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
C#

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.
C#

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