Click here to Skip to main content
Email Password   helpLost your password?

Introduction

Most you like me have been watching the release of VS 2008 from its beta versions. From the perspective of this article Microsoft added generics, query operators and LinQ support. I was always wandering why LinQ was added when the world has been comfortable with ADO programming with its support extended to objects and xml too; similarly use of generics/aggregations etc in C # 3.0 etc. With this came the purpose of this article. That is to compare existing approaches with the new ones release 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 filer 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 , there are extra lines introduced so that to be fare in writing function for LinQ and ADO. As the code will be a shareware you are free to improve and build on it. The basic unit of measurement is counting ticks which have been used in this article ( System.StopWatch). Each piece of performance code has been run 500 times so as to get best performance values. Where ever possible I have added code to dispose/ garbage collect objects. All the code explained in this article is available in 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

Processor x86 Family Intel ~2992 Mhz

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

Before you begin read the following

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

Using the code

1. 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.

2. 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.

3. 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.

4. 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 and xml file, then apply a filter in them (pass values > 250 in this case) and then add all the values of the first column.

5. 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.

6. 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 * 106 .

Points of Interest

I have referenced the median value rather then the mean to help reduce the effect of out-liars in the graph as in a Windows operation system there are always more processes running and spikes in the graph does not necessarily means a fault of 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.

Image1.JPG

· Inserting a row …ADO vs. LinQ without using a stored procedure.

Image2.JPG

· Reading from a table (ADO vs. LinQ).

· Reading xml files ADO vs. LinQ.

Image4.JPG

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

Image5.JPG

· 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);

Image6.JPG

Conclusion

So my conclusion is that LinQ is not the overall winner (as expected!). While insert operations as better in LinQ; the read operation are great in ADO. Xml operations are by and large same (not too much of a difference) and object access is basically depending on the type of use, 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 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.

Mean and Median Values

ADO Insert

LinQInsert

Mean

102445.4

Mean

40941.11

Median

97645

Median

39247

ADOInsertDirect

LinQInsertDirect

Mean

101625.8

Mean

42324.78

Median

96991

Median

37634

ADORead

LinQRead

Mean

7162452

Mean

14000818

Median

7097164

Median

13825471

csharpXMLReading

LinQXmlRead

Mean

108391.1

Mean

98842.27

Median

104622

Median

92318

csharpObjects1

LinQObjects1

Mean

461.6407

Mean

2965.475

Median

402

Median

2340

LinQObjects2

Mean

0

Median

0

ADO DataSet

LinQ DataSet

Mean

19159168

Mean

24760066

Median

19241882

Median

24597140

Download source code and sample application here

Download Source_File_and_Sample_Application.zip - 473.12 KB

You must Sign In to use this message board.
 
 
Per page   
 FirstPrevNext
GeneralNot quite sure that your article is right
Necromantici
18:31 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[^]
GeneralI can tell from the Introduction
Adam Tibi
0:48 3 Jun '08  
Hi,

You said in your introduction:

"From the perspective of this article Microsoft added generics, query operators and LinQ support. I was always wandering why LinQ was added when the world has been comfortable with ADO programming with its support extended to objects and xml too; similarly use of generics/aggregations etc in C # 3.0 etc"

- Generics are added in .NET 2.0 (not C# 3.0)
- You probably meant "LINQ to SQL"
- The world wasn't comfortable with plain ADO.NET this is why components such as Microsoft Enterprise Library (database components) and OR-Mappers such as Subsonic, LLBLGEN, NHibernate, Wilson, etc... existed.
- It is LINQ or maybe linq but not LinQ !!!

I won't bother reading the rest of the article as the introduction doesn't look trustworthy to me. You got my one.
Regards

Make it simple, as simple as possible, but not simpler.
AdamTibi.NET

GeneralComments on the code
Steven Campbell
9:34 28 May '08  
In ADO.NET:
* You include the time to open and close the unpooled db connection. This is very slow. Linq is using pooled connections, so it is not a fair comparison. You should use the same connection string for both methods.
* When reading data from a reader, you should use GetDouble() instead of Convert.ToDouble to get the value (conversions are very slow)


GeneralDid you use compiled LINQ queries?
leppie
4:31 26 May '08  
Or just plain ones?

xacc.ide - now with TabsToSpaces support
IronScheme - 1.0 alpha 3 out now

GeneralFix the Formatting
John Simmons / outlaw programmer
2:46 26 May '08  
Near the top of the article,you have some text all squished up.


"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"...the staggering layers of obscenity in your statement make it a work of art on so many levels." - Jason Jystad, 10/26/2001



Last Updated 26 May 2008 | Advertise | Privacy | Terms of Use | Copyright © CodeProject, 1999-2010