Bagikan melalui

DLinq (Linq to SQL) Performance (Part 1)

  • Artikel

[ 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 peformance

  • Anonymous
    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. --rj

  • Anonymous
    June 25, 2007
    Cliffhangers on blog posts? Now I've seen everything.

  • Anonymous
    June 25, 2007
    Ｌｉｎｑの性能1/8

  • Anonymous
    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 LINQ

  • Anonymous
    July 04, 2007
    Some quick links about LINQ: Articles about extension methods by the Visual Basic team Third-party LINQ

  • Anonymous
    July 05, 2007
    One of the things I get asked quite often is "How does LINQ to SQL affect performance compared to writing

  • Anonymous
    July 05, 2007
    One of the things I get asked quite often is "How does LINQ to SQL affect performance compared to

  • Anonymous
    July 05, 2007
    The comment has been removed

  • Anonymous
    July 05, 2007
    The comment has been removed

  • Anonymous
    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 currently

  • Anonymous
    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 important

  • Anonymous
    August 24, 2007
    I've been meaning to dig into LINQ performance for some time (actually since it came up during one of

  • Anonymous
    August 24, 2007
    I've been meaning to dig into LINQ performance for some time (actually since it came up during one of

  • Anonymous
    August 26, 2007
    Risorse su Linq to SQL

  • Anonymous
    September 04, 2007
    Linq to Sql performance

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

  • Anonymous
    December 15, 2007
    V poslední době jsem měl dvě přednášky - nejprve o Silverlightu vývoji Silverlight

  • Anonymous
    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 but

  • 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

  • Anonymous
    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-entrenched

  • Anonymous
    February 20, 2008
    Considering I've put a few posts up about LINQ To SQL, I realised I've never shared some of the

  • Anonymous
    March 07, 2008
    (This is part of an on-going series of articles, started here ) We're getting closer to the goal

  • Anonymous
    April 02, 2008
    I had a blast on the Geek Speak today. If you missed it, they will have it available on demand from their

  • Anonymous
    April 14, 2008
    Update: I made a mistake in the first Linq to Sql query. It's not that slow as I previously posted

  • Anonymous
    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 removed

  • Anonymous
    August 25, 2008
    I did a series of postings on Linq Compiled Queries last year, I recently got some questions on those