DLinq (Linq to SQL) Performance (Part 1)
[ By popular demand, here are links for all 5 parts in the series Part 1, Part 2, Part 3, Part 4, Part 5
-Rico ]
I love Linq. Really. That might scare you because it has all these wacky new constructs and as a performance guy you'd think that I'd be all about getting rid of abstractions and just getting to the metal. But don't be scared, I haven't lost my mind. Linq is great because, even though it adds some levels of complexity, it simulataneously increases the chunkiness of the work that the framework receives in such a way that it creates fantastic opportunities to deliver performance. Just like SQL can do a great job optimizing database queries because they are chunky enough.
And speaking of databases, DLinq is really where the opportunities for amazing coolness are present.
I first started looking at the performance of DLinq (it's officially called Linq to SQL but I still call it DLinq)shortly after the May 2006 CTP -- the very same one many of you are still using. There were some great opportunities at that time and I'm happy to report that we've capitalized on a lot of what we found then, in fact I'll be writing about that in the next few postings. But for today I want to talk about how things looked back in May of 2006. How they might still look to you at this very moment if you're using the May 2006 CTP.
I wanted to look at the basics of DLinq performance in a very simple case to get an idea what the raw overhead was. So I set up a harsh test environment:
- northwinds database, local
- many queries have already been run so the database is hot, no disk activity
- the body of the dlinq query is minimal so all that code is hot
- no enties need be stored, so CLR memory is also hot
Why would I do this? Because it's the "nightmare" scenario for DLinq. There is no business logic. There is no database latency. There is no inherent CLR overhead associated with the processing. The cost is DLinq and nothing but DLinq. Normally there is some business processing so the DLinq code is only a portion, maybe even a small portion of the total processing time. Normally you have to connect to a database over the network and maybe even read the data off disk, again that reduces the portion of time you spend waiting for DLinq. But not in my test case... my test case is beating on DLinq so I can see how it stands up.
I won't show you the whole program (you could create it in a few seconds with the CTP) but the key parts are like this:
Here's the query
var q = from o in nw.Orders
select new OrderDetail {
OrderID = o.OrderID,
CustomerID = o.CustomerID,
EmployeeID = o.EmployeeID,
ShippedDate = o.ShippedDate
}
Then we run this in a loop
foreach (var detail in q)
{
sum += detail.OrderID;
count++;
}
Note the highly useful (sarcasm) business logic -- adding the OrderId. I did that so I could print the total at the end and make sure as I change the test that I was really reading them. Same for the count. Any real application would do something -- anything -- with those order detail lines, my application is basically throwing them away.
OK great so I have a little test harness scientifically designed to maximize the DLinq overhead. What do I compare it against?
Well I wrote this other program that gets the same data and does the same (trivial) operation the old fashioned way so that I could compare.
The body of that one looks like this:
SqlCommand cmd = con.CreateCommand();
string cmdText = "select OrderID, CustomerID, EmployeeID, ShippedDate from Orders";
cmd.CommandText = cmdText;SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
OrderDetail o = new OrderDetail();o.OrderID = dr.GetInt32(0);
o.CustomerID = dr.GetString(1);
if (!dr.IsDBNull(2)) o.EmployeeID = dr.GetInt32(2); else o.EmployeeID = null;
if (!dr.IsDBNull(3)) o.ShippedDate = dr.GetDateTime(3); else o.ShippedDate = null;sum += o.OrderID;
count++;
}
It's a lot more code but that's how you do it using just SqlDataReader.
So now I can measure how many queries/sec I can do with the DLinq code and compare it to the SQLDataReader equivalent.
Any guesses? DLinq can't be faster because of course it uses SqlDataReader itself to do the job so the best you could get is a tie.
No peeking now.
Think about it. How much slower do you think DLinq was in May of 2006 in this "worst case" scenario.
Got your number?
Last chance now.
Final answer?
OK here's what I got when I did the experiment, back in July of 2006.
| Build/Test | Time for500 Queries | Queries/sec |
| May 2006 CTP | 8.027s | 62.29 |
| Raw Cost (SQLDataReader) | 1.094s | 457.04 |
You can pretty much eyeball it from those times. 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.
**Sekiya Sato (see below) pointed out an error in my original benchmark in which I had one of my ISDBNull() checks backwards. That error made the "nolinq" version actually run 3.6% faster than it should have. So the number I reported, 13.62% should have actually been 14.09% -- let me restate that result for clarity, in May 2006, DLinq was running at 14.09% of the underlying provider speed in this (harsh) test case on my hardware and not 13.62% as previously reported.
Comments
Anonymous
June 22, 2007
Hello Rico, Is it possible that (D)LINQ performance Beta 1 is better?Anonymous
June 22, 2007
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.Anonymous
June 23, 2007
Rico Mariani, our performance expert amongst other things, has posted a blog detailing worst case peformanceAnonymous
June 23, 2007
Rico, I'm getting worse results with VS 2008 Beta 1 than you did with the May 2006 CTP. See http://oakleafblog.blogspot.com/2007/06/rico-mariani-starts-linq-to-sql.html. Looking forward to Beta 2. --rjAnonymous
June 25, 2007
Cliffhangers on blog posts? Now I've seen everything.Anonymous
June 25, 2007
Ｌｉｎｑの性能1/8Anonymous
June 25, 2007
Linqの性能1/8 からはじまるパフォーマンスチューニングAnonymous
June 26, 2007
Hello Rico,
if (dr.IsDBNull(2)) o.EmployeeID = dr.GetInt32(2); else o.EmployeeID = null;
It is a mistake,isn't it? I supposed that correct is
if (!dr.IsDBNull(2))
I'm sorry,if wrong.
Anonymous
June 26, 2007
You're right that is a mistake. I fixed it. Now I have to double check that it didn't creep into my original benchmark.Anonymous
July 04, 2007
Some quick links about LINQ: Articles about extension methods by the Visual Basic team Third-party LINQAnonymous
July 04, 2007
Some quick links about LINQ: Articles about extension methods by the Visual Basic team Third-party LINQAnonymous
July 05, 2007
One of the things I get asked quite often is "How does LINQ to SQL affect performance compared to writingAnonymous
July 05, 2007
One of the things I get asked quite often is "How does LINQ to SQL affect performance compared toAnonymous
July 05, 2007
The comment has been removedAnonymous
July 05, 2007
The comment has been removedAnonymous
July 06, 2007
linq to sql 的动态条件查询方法Anonymous
July 09, 2007
Rico Mariani did a very good job analyzing performance implications of LINQ to SQL queries. He is currentlyAnonymous
July 16, 2007
Also been catching up on Rico Mariani's notes on improvements to LINQ to SQL performance between the...Anonymous
August 12, 2007
There are several good new blogs from members of the community team. Nevertheless, the most importantAnonymous
August 24, 2007
I've been meaning to dig into LINQ performance for some time (actually since it came up during one ofAnonymous
August 24, 2007
I've been meaning to dig into LINQ performance for some time (actually since it came up during one ofAnonymous
August 26, 2007
Risorse su Linq to SQLAnonymous
September 04, 2007
Linq to Sql performanceAnonymous
November 01, 2007
Some of the best blogs on LINQ to SQL I found are available for great learning, Scott Guthrie The FamousAnonymous
December 15, 2007
V poslední době jsem měl dvě přednášky - nejprve o Silverlightu vývoji SilverlightAnonymous
December 15, 2007
V poslední době jsem měl dvě přednášky - nejprve o Silverlightu vývoji Silverlight aplikací v PHP pomocíAnonymous
January 11, 2008
I've written a few articles about Linq now and you know I was a big fan of compiled queries in Linq butAnonymous
January 11, 2008
I've written a few articles about Linq now and you know I was a big fan of compiled queries in LinqAnonymous
January 31, 2008
re: Linq はすごい？ その７Anonymous
January 31, 2008
WARNING & DISCLAIMER: This is a long post, split over a series, as it discusses some old, well-entrenchedAnonymous
February 20, 2008
Considering I've put a few posts up about LINQ To SQL, I realised I've never shared some of theAnonymous
March 07, 2008
(This is part of an on-going series of articles, started here ) We're getting closer to the goalAnonymous
April 02, 2008
I had a blast on the Geek Speak today. If you missed it, they will have it available on demand from theirAnonymous
April 14, 2008
Update: I made a mistake in the first Linq to Sql query. It's not that slow as I previously postedAnonymous
June 06, 2008
下面是一些讨论LINQtoSQL性能一些文章。http://blogs.msdn.com/ricom/archive/2007/06/22/dlinq-linq-to-sql-performanc...Anonymous
July 16, 2008
The comment has been removedAnonymous
August 25, 2008
I did a series of postings on Linq Compiled Queries last year, I recently got some questions on those