• Download Source_File_and_Sample_Application - 473.12 KB

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

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

• http://msdn2.microsoft.com/en-us/netframework/aa904594.aspx

• http://msdn2.microsoft.com/en-us/vcsharp/aa336745.aspx

• Ado.net does not need any introduction!

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 * 10⁶ .

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.

· 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 vas 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 fro 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]

• Lets 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);

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.

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

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

Not 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[^] Sign In·View Thread·PermaLink I 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 Sign In·View Thread·PermaLink 5.00/5 (1 vote) Comments 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) my blog Sign In·View Thread·PermaLink Did 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 Sign In·View Thread·PermaLink 2.00/5 (1 vote) Fix 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 Sign In·View Thread·PermaLink 5.00/5 (2 votes)

Last Visit: Friday, November 21, 2008 12:11 PM     Last Update:Friday, November 21, 2008 12:11 PM