Performance Comparisons LINQ to SQL / ADO / C#

• Download source files and sample application - 144.55 KB

Contents

• Introduction
• What you Need to Run the Samples
• Before you Begin Read the Following
• Code Explained
• Inserting a Row — ADO vs LINQ Using a Stored Procedure
• Inserting a Row — ADO vs LINQ without Using a Stored Procedure
• ADO vs. LINQ Reading from a Table
• Reading XML Files ADO vs. LINQ
• Accessing Objects (Arrays in this case) [Traditional vs. c#3.0 vs. LINQ]
• Outcome
• Inserting a Row — ADO vs LINQ Using a Stored Procedure
• Inserting a Row — ADO vs. LINQ without Using a Stored Procedure
• Reading from a Table (ADO vs. LINQ). 5
• Reading XML Files ADO vs. LINQ
• Accessing Objects (Arrays in this case) [Traditional vs. c#3.0 vs. LINQ]

Introduction

Most of you, like myself, have been watching the release of VS 2008 from its beta versions. I have already explored some features of Silverlight; as seen here.

From the perspective of this article Microsoft added generics, query operators and LINQ support. I always wondered why LINQ was added when the world was comfortable with ADO programming with its support extended to objects and XML too (and similarly, the use of generics/aggregations etc. in C # 3.0). This question created the purpose of this article. This article seeks to compare existing approaches with the new ones released with C# 3.0 and LINQ. As the scope of comparison is quite large, I will restrict our discussion to:

• Performance of reading data from SQL Server 2005 using ADO.NET and LINQ using a Stored procedure
• Performance of reading data from SQL Server 2005 using ADO.NET and LINQ by directly executing SQL statements.
• Performance of reading and manipulating XML files using existing C# approaches vs. LINQ approach
• Performance of accessing list of objects ( arrays in this article ) using traditional programming, LINQ and C# 3.0
• Performance of filling a dataset using ADO and LINQ and then performing a filter operation and then summing up something after filtering.

Before we jump into this article, I want to point out that there are many ways to accomplish a task programmatically. An effort has been made to write the best code possible, though, there are extra lines introduced so to make the comparison for writing LINQ and ADO functions more fair. As the code is shareware you are free to improve and build on it. The basic unit of measurement is counting the ticks which have been used in this article ( System.StopWatch). Each piece of performance code has been run 500 times so as to get the best performance values. Wherever possible I have added code to dispose/garbage collect objects. All the code explained in this article is available for download. The code is commented wherever required. Finally, all the performance values are added to an Excel file Graphs.xls and visually shown by Excel graphs.

Logic for running the function 500 times is given below

1. create a dataset
2. Start the stopwatch()
3. run the test function ( under the ActualFunction #region)
4. add the ticks to a data row
5. stop the watch
6. output the dataset to a XML and finally to the Graph.xls

The configuration used for getting the current performance data is given below

OS Name	Microsoft(R) Windows(R) Server 2003, Ent Edition
Version	5.2.3790 Service Pack 1 Build 3790
Total Physical Memory	2,038.04 MB
Available Physical Memory	872.52 MB
Total Virtual Memory	2.85 GB
Page File Space	1.00 GB

Background

What you Need to Run the Samples

• VS 2008
• SQL Server 2005 with sample database installed ( AdventureWorks )
• Run the InsertSP.sql script on the Adventureworks database, this will create a simple stored procedure to inserting values to the Sales.Customer table.
• After the run of the two sample console applications it will produce the following XML file for all the 500 passes for each comparison, data from these XML files is to be manually imported to Graphs.xls file.

Before you Begin Read the Following

If you are not familiar with LINQ, new features of C# 3.0 please read the following URLs

• The LINQ Project
• Visual C# 3.0x
• ADO.NET does not need any introduction!

Using the Code

Inserting a row — ADO vs. LINQ Using a Stored Procedure

Function used for ADO is ADOInserting() in RetrieveUsingADO.sln. The LINQ counterpart of the same is LINQInserting() in RetriveUsingLINQ.sln.

Inserting a Row ADO vs LINQ without Using a Stored Procedure

Function used for ADO is ADOInsertingDirect() in RetrieveUsingADO.sln. The corresponding LINQ function is LINQInsertingDirect() in RetriveUsingLINQ.sln.

ADO vs. LINQ Reading from a Table

Function used for ADO is ADOReading() in RetrieveUsingADO.sln. The corresponding LINQ function is LINQReading() in RetriveUsingLINQ.sln. Just to make the function do a little more task I have added code to add all the values of the first column after reading.

Reading XML Files ADO vs. LINQ

Function used for ADO is xmlReading() in RetrieveUsingADO.sln. The corresponding LINQ function is LinQXmlRead() in RetriveUsingLinQ.sln. These functions first read an XML file, then apply a filter in them (pass values > 250 in this case) and then add all the values of the first column.

Accessing Objects (Arrays in this case) [Traditional vs. c#3.0 vs. LINQ]

Function used for LINQ part is LinQObjects1(), for the traditional C# part is csharpObjects1() and for the C# 3.0 part is LINQObjects2() in RetriveUsingLINQ.sln.

All the functions first create an array of integers, and then crate a second array of only even numbers with their squares. The last step in the functions is to add all the values in the resultant subset.

Filling Dataset using LINQ and ADO and then Performing Filter Operations

Function used for LINQ part is LINQQueryDataset(), and that for the ADO part is ADODataSetQuery(). Please note that such database operation are resource intensive as the minimum counter value is 15 * 10⁶.

Points of Interest

I have referenced the median value rather then the mean to help reduce the effect of outliers in the graph because in a Windows OS there are always more processes running and spikes in the graph do not necessarily mean a fault in the code.

Inserting a Row — ADO vs. LINQ Using a Stored Procedure

• The median value of ADO insert is far more than that of LINQ. LINQ wins in this situation.

Inserting a Row — ADO vs. LINQ without Using a Stored Procedure

• The median value of ADO is more than that of LINQ which suggests that LINQ is a winner in this situation

Reading from a Table (ADO vs. LINQ)

• There is a large difference between median values of LINQ and ADO as far as reading from table is concerned. ADO wins here, but is because of ADO.NET maturity in the market and its tight connection with SQL server or because LINQ (in my opinion ) created an overhead by creating the < IEnumerable> interface and an object if each item was draw in the LINQtoSQl dbml designer. For further improvement you should try with loosely typed datasets.

Reading XML Files ADO vs. LINQ

• By comparing the mean values for C# and LINQ, we have a close performance competition. There is only a small difference of 12304 ticks and by definition 1 second has about 1 billion ticks. LINQ marginally wins here.

Accessing Objects (Arrays in this Case) [Traditional vs. c#3.0 vs. LINQ]

• Let's start with LINQ Obj2. Here the whole requirement of adding squares of only even numbers is narrowed down to one statement and that given below. The most interesting fact is that this function actually does not take any time to run! So C# 3.0 syntax wins here

double sum = nums.Aggregate(delegate(double Cursum, double curNum) 
{ 
    if (curNum % 2 == 0) 
    { 
        return (Cursum + (curNum * curNum)); 
    } else 
    { 
        return (Cursum + 0); 
    } 
});

• Then comes LINQObj1 which runs a LINQ styled query on the array as given below. Please note that instead of the newFunction(temp) we could also have written temp%2 == 0, I just wanted to demonstrate using functions in the condition clause of the LINQ query.

var getSquaresLessthen500 = from temp in nums where temp == newFunction(temp) select temp*temp;

• Last in the performance under this category is the traditional C# style syntax as given in csharpObjects1().

Filling Dataset using LINQ and ADO and then Performing Filter Operations

And there is a big difference between mean values. I think that in the LINQ implementation the line where we create an object of DataRow and then add to the table is the place where performance is hitting. ADO implementation wins here.

table.LoadDataRow(new Object[] {
tempRec.CustomerID, tempRec.TerritoryID, tempRec.AccountNumber, tempRec.CustomerType,
tempRec.rowguid, tempRec.ModifiedDate}, true);

So my conclusion is that LINQ is not the overall winner (as expected). While insert operations are better in LINQ, the read operation is superior in ADO. XML operations are by and large the same (not too much of a difference) and object access is basically depending on the type used (even still, aggregate was a good example). LINQ to datasets is pretty costly; I suggest using the ADO version and only use LINQ to objects if we already have dataset available and want to query.

For further improvement we should try and do bulky insert operations and also reads of different kind (like integer read, string read, block data read and substring reads). Again it is hard to draw conclusions based on the limited number of scenarios I have covered. There is a scope of improvement in each scenario, but since I have timed all the operations, this will give us better insights when we design or architect next time.

Appendix

Mean and Median Values

ADO Insert Mean 102445.4	LINQInster Mean 40941.11 Median 39247
ADOInserDirect Mean 101625.8 Median> 96991	LINQInsertDirect Mean 42324.78 Median 37634
ADORead Mean 7162452 Median 7097164	LINQRead Mean 14000818 Median 13825471
csharpXMLReading Mean 108391.1 Median 104622	LINQXmlRead Mean 98842.27 Median 92318
csharpObjects1 Mean 461.6407 Median 402	LINQObjects1 Mean 2965.475 Median 2340
	LINQObjects2 Mean 0 Median 0
ADO DataSet Mean 19159168 Median 19241882	LINQ DataSet Mean 24760066 Median 24597140

You must Sign In to use this message board.

FAQ  Noise Tolerance Very HighHighMediumLowVery Low  Search Messages    Layout Expand All MessagesThread ViewNo Javascript (slow)No Javascript Preview    Per page 102550

Msgs 1 to 7 of 7 (Total in Forum: 7) (Refresh) FirstPrevNext

Not quite sure you are right on this... Necromantici 18:33 11 Jun '08   Hi there! Its remarcable that you wanted to measure Linq but after reviewing your code I thing that you dont understand the why's involved behind Linq. For example, you are not using real or almost close to real examples. One could be creating an instance of Customer then assigning some values to it and then persinsting it. Or another one could be that you are using Linq to SQL as if it were a mere data library, as if it where ADO.NET. Why shoud I execute a stored procedure that inserts some data when the majority of the real-life applications uses or Datasets or Business Objects or some design pattern for data persistence. Besides, coding data access like you do is unsafe. And by the way, you are making a lot of conversions, you are not using the best methods to get data with Linq To SQL for example retrieving data using AsEnumerable where you could have used IEnumerable without any internal conversions or spoky calls and you are using var as any other .NET type. I have the impression that you havent develop any business application, because only that could explain why you dont understand the existence of ORM, data persistence patterns and data access frameworks and wouldn't need to do this only to discover that Linq To SQL is a bit unperformant compared with ADO.NET and that only if your test's are correct. Check this links. http://davidhayden.com/blog/dave/archive/2006/12/06/ImplicitlyTypedLocalVariables.aspx[^] Be sure to check the next link because it explains in simple words the tradeoffs involved in these topics. http://davidhayden.com/blog/dave/archive/2008/02/19/HighPerformanceLINQToSQLCompiledQueriesORMappersEcommerceWebsites.aspx[^] Finally, check the blog of Rico Mariani http://blogs.msdn.com/ricom[^] He is an expert in optimizations not only in .NET but in C++ and other platforms/lenguages. And by the way, here is a series of posts where he talks about DLinq (Linq To Sql). http://blogs.msdn.com/ricom/archive/2007/06/22/dlinq-linq-to-sql-performance-part-1.aspx[^] Sign In·View Thread·PermaLink 5.00/5 (2 votes) Darn Straight binaryDigit@@ 19:02 27 May '08   Isn't LINQ a redundant SQL parser (reversed, actually) simply to complete this OO hype? Here we are again, learning from Java and JavaScript. Sign In·View Thread·PermaLink 3.67/5 (2 votes) Re: Darn Straight jwooley 7:26 28 May '08   LINQ has nothing to do with SQL. LINQ to SQL deals with SQL. LINQ is a much more broad set of libraries and interfaces to make working with data (regardless of the source) more natural and with a common API. Whether the data is coming from objects, XML, database, or other providers (LINQ to Amazon web services and LINQ to Sharepoint are a couple examples) you can code against the structures using the same LINQ syntax. In many ways LINQ is learning more from Foxpro than Javascript. Sign In·View Thread·PermaLink Apples to oranges on the Read operation paul.vencill 14:50 27 May '08   As you surmised, using the strongly typed results on LINQ is going to incur some overhead, how much is in question. Perhaps a more accurate test would've been to have the datareader actually populate strongly typed data layer objects, as that's closer to what LINQ is actually doing. Sign In·View Thread·PermaLink 5.00/5 (1 vote) Re: Apples to oranges on the Read operation Dewey 15:23 27 May '08   I agree, having the datareader populate a class would be a closer comparison. However, I'm still not sure how Linq beats ADO at anything, it seems counter intuitive. Sign In·View Thread·PermaLink 3.00/5 (1 vote) Re: Apples to oranges on the Read operation Robert_G 20:40 27 May '08   DataSets are usually *way* slower than real classes + datareader. They indirect every value that is set to the internal storage, by that adding significant overhead. Just think of the underlying complexity that is involved just to write or read a value of a datarow. The Problem of LINQ is mainly that it has to use Reflection to describe your query in the expression tree. Accessing Members through Reflection costs time. Microsofts LINQ to MSSQL tuff also accesses properties through reflection which adds even more overhead. I use my own codegens for LINQ via Expressions trees, and they generate methods to read/write properties back&forth as well, by that eliminating a lot of Reflection usage. This performs much better than the stuff they have hardcoded for MSSQL. Sign In·View Thread·PermaLink Re: Apples to oranges on the Read operation jwooley 7:23 28 May '08   Rico Mariani did a series of tests comparing the read operations starting with http://blogs.msdn.com/ricom/archive/2007/06/22/dlinq-linq-to-sql-performance-part-1.aspx[^] In this series he found that a big tweak that you can make is to use compiled queries rather than the ad-hock ones. That eliminates much of the reflection code that is otherwise necessary. In addition, Rick Strahl has done some testing regarding datasets with LINQ and found that ASP binding is significantly faster when binding to a dataset than to objects, so there may be some trade-off in that regard. Sign In·View Thread·PermaLink 2.00/5 (1 vote)

Last Visit: Wednesday, November 19, 2008 10:35 PM     Last Update:Wednesday, November 19, 2008 10:35 PM