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 for
500 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 (41)

  1. israelaece says:

    Hello Rico,

    Is it possible that (D)LINQ performance Beta 1 is better?

  2. ricom says:

    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.

  3. Rico Mariani, our performance expert amongst other things, has posted a blog detailing worst case peformance

  4. rogerj says:

    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.

    –rj

  5. Ariel says:

    Cliffhangers on blog posts?

    Now I’ve seen everything.

  6. Linqの性能1/8 からはじまるパフォーマンスチューニング

  7. sekiya sato says:

    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.

  8. ricom says:

    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.

  9. Some quick links about LINQ: Articles about extension methods by the Visual Basic team Third-party LINQ

  10. Some quick links about LINQ: Articles about extension methods by the Visual Basic team Third-party LINQ

  11. One of the things I get asked quite often is "How does LINQ to SQL affect performance compared to writing

  12. One of the things I get asked quite often is "How does LINQ to SQL affect performance compared to

  13. Well it’s high time I gave you some numbers for the new stuff. In the original benchmark I tested the

  14. Well it's high time I gave you some numbers for the new stuff. In the original benchmark the Linq

  15. neuhawk says:

    linq to sql 的动态条件查询方法

  16. Marco Russo says:

    Rico Mariani did a very good job analyzing performance implications of LINQ to SQL queries. He is currently

  17. Also been catching up on Rico Mariani’s notes on improvements to LINQ to SQL performance between the…

  18. There are several good new blogs from members of the community team. Nevertheless, the most important

  19. I’ve been meaning to dig into LINQ performance for some time (actually since it came up during one of

  20. I’ve been meaning to dig into LINQ performance for some time (actually since it came up during one of

  21. jankyBlog says:

    Risorse su Linq to SQL

  22. Linq to Sql performance

  23. Wriju's BLOG says:

    Some of the best blogs on LINQ to SQL I found are available for great learning, Scott Guthrie The Famous

  24. V poslední době jsem měl dvě přednášky – nejprve o Silverlightu vývoji Silverlight

  25. V poslední době jsem měl dvě přednášky – nejprve o Silverlightu vývoji Silverlight aplikací v PHP pomocí

  26. I’ve written a few articles about Linq now and you know I was a big fan of compiled queries in Linq but

  27. I've written a few articles about Linq now and you know I was a big fan of compiled queries in Linq

  28. re: Linq はすごい? その7

  29. WARNING & DISCLAIMER: This is a long post, split over a series, as it discusses some old, well-entrenched

  30. Considering I've put a few posts up about LINQ To SQL, I realised I've never shared some of the

  31. (This is part of an on-going series of articles, started here ) We're getting closer to the goal

  32. I had a blast on the Geek Speak today. If you missed it, they will have it available on demand from their

  33. .Net World says:

    Update: I made a mistake in the first Linq to Sql query. It's not that slow as I previously posted

  34. Wriju's BLOG says:

    ADO.NET is our contemporary data access component and now we have written many applications. Now there

  35. I did a series of postings on Linq Compiled Queries last year, I recently got some questions on those