Rico Mariani Starts LINQ to SQL Performance Analysis Series
Rico Mariani is a "Performance Preacher" in Microsoft's Developer Division and a co-author of the "Improving .NET Application Performance and Scalability" patterns & practices white paper. His Channel9 video, "Rico Mariani: Writing better, faster code," was the featured "Behind the Code" show segment earlier this year. He also starred in "Rico Mariani - Making .NET Perform (Happy Birthday Video #2)" produced in February 2005.
DLinq (Linq to SQL) Performance (Part 1) is Rico's first post about LINQ to SQL performance on his Performance Tips blog. His tests pit a simple LINQ to SQL query against an SqlDataReader with the Northwind sample database's Orders table as the data source. Rico's results for 500 executions of a simple query that iterated the orders table and created an instance of an Order object with OrderID, CustomerID, EmployeeID, and ShippedDate properties were as follows:
Build/Test | Time for 500 Queries | Queries/Sec |
| May 2006 CTP LINQ to SQL | 8.027s | 62.29 |
| Raw Cost (SQL Data Reader) | 1.094s | 457.04 |
To see if I could duplicate Rico's results with the VS 2008 Beta 1 bits running under Vista Ultimate, I built a simple test harness that emulated his test conditions (to the extent that I could determine them from the code provided in Rico's post.)
Note: You can copy and paste the code for my LINQtoSQLPerf.sln test harness from my VB.NET Code for the LINQ to SQL Performance Test Harness Web page to a VB 2008 WinForm project. The O/R Designer contains only the Order object with OrderID, CustomerID, EmployeeID, and ShippedDate properties, so loading of related entities isn't an issue.
Rico must have a considerably faster machine than my two-years-old, single core 2.26-GHz Pentium 4 Dell 400SC server with 1 GB RAM and 80-GB 7,200 RPM UltraATA-100 drive (IBM IC35L090AVV207-0, 2MB buffer, 8.8 ms. seek time). Here are my results:
Build/Test | Time for 500 Queries | Queries/Sec |
| VS 2008 Beta 1 LINQ to SQL | 14.698s | 34.01 |
| Raw Cost (SQL Data Reader) | 1.465s | 341.30 |
Changes, such as running a networked instance of SQL Server Express or moving from Debug to Release configuration made less than a 0.5 second change to the LINQ to SQL query time or 0.2 second change to the SQL Data Reader time. Removing unneeded columns from the Order object in the O/R Designer didn't change the LINQ to SQL time, either; CPU usage was pinned at 100% for the entire ~14.5 seconds; about 500 MB physical memory was free during the tests.
Rico says:
In May 2006 DLinq is running at about 1/8 the speed of the underlying provider (13.62%).* We can do better than that. And we did... Stay tuned for the details and some modern era DLinq results.
*Remember no real application would ever see a result as poor as 13.62% because of course they would be doing "actual work" as well as the DLinq operations resulting in more comparable performance.
My test show LINQ to SQL to be an order of magnitude slower than the underlying SqlDataReader. I'll be interested to see how much better Rico did with "modern era" LINQ to SQL bits. Rico says in a comment:
Some things made it into Beta 1 but the bulk of what I'm going to post in the next few days didn't happen until after. You'll first see it in Beta 2.
That's what they all say. Stay tuned.
Updated 6/29/2007: Corrected and added fixed disk specs.
Atom 1.0 site feed
5 comments:
I hope you enjoy reading my series then. I'm very pleased with how it turned out :)
-Rico
With bated breath and crossed fingers we wait for a good and per formant solution.
Hi!
Good to see more information on LINQ to SQL performance. Can't wait to read more from Rico on the subject (keep up the good work Rico :-)).
A few word on compiled queries.
I've been able to optimize my queries tremendously by switching to compiled queries. The difference was like night and day (see benchmarks below), and made me realize that the expression compilation is a quite costly operation.
Most database applications use repetitive queries that take dynamic arguments - compiled queries are perfect for such requirements. A few lines of code make a big difference in performance.
Converting regular LINQ expressions to compiled queries quickly becomes a trivial task, and I hope that most LINQ to SQL developers realize, that this is a pattern they should look into (it's a prime pattern candidate the LINQ to SQL team to focus on when writing docs and samples).
Here are my own findings (as of beta 1).
I'm measuring performance improvements in LINQ to SQL using a small application much like yours. I've got a single table with approximately 175 rows and measure different queries against the data.
Because SqlDataReader is the native mechanism for hydrating entities (and because SqlDataReaders are used for high performance reading), they serve as the benchmark reference.
Note: These benchmarks are based on beta 1 and (modified) code already disclosed on the LINQ to SQL project forum.
I'm performing 175 Primary Key queries against the SQL table using the following methods:
1. Compiled Queries
2. Regular Expressions
3. SqlCommands / SqlDataReaders
4. SqlConnections pre open / closed
From beta 1 I was able to make the following benchmarks with no re-use of DataContext (i.e. created for each iteration - simulates most n-tier designs):
1. 570 ms (Compiled Queries)
2. 2850 ms (Regular Expressions)
3. 75 ms (SqlCommands / SqlDataReaders)
I then made some benchmarks re-using the DataContext (simulates some situations in n-tier designs where several queries can be batched for performance and less strain on the SqlConnection pool):
1. 175 ms (Compiled Queries)
2. 520 ms (Regular Expressions)
3. 75 ms (SqlCommands / SqlDataReaders)
Using a pre opened SqlConnection (if, for some reason your implementation allows you to optimize requests from the pool) came with a performance improvement of approximately 5 ms for all queries.
I just includede this part to ensure that there were no connection pooling issues with beta 1.
It's pretty clear (atleast from my benchmarks) that nothing beats the SqlDataReader. Then again, we all knew this. But what came as a surprise was the low performance of regular PK LINQ queries against a table (according to the team this relates to object materialization and lack of cached entity signatures in the DataContext).
Unfortuantely, some of the DataContext implementation in beta 1 is broken (e.g. the .Clone() method) and I remember having read that very little (if any) caching made it to the current release.
Regardless of the performance of the final release of LINQ to SQL, there's no excuse to skip compiled queries. I'm quite sure we'll see lots of blog posts on how to manage compiled queries going forward.
Looks like I ran into a potential bug in LINQ to SQL that involved compiled queries. Better report to the team ..
I already tried for the performance between LINQ and DatAdapter,surprisingly LINQ is more slower.
See for my results at
http://forums.microsoft.com/MSDN/AddPost.aspx?PostID=2085517&SiteID=1
Siva Prasad
My tests brings me to a really different results... I see that LINQ is about 30% slower than raw ADO.NET. You may check my results here and the follow up here.
Post a Comment