DLinq (Linq to SQL) Performance (Part 4)


Well it’s high time I gave you some numbers for the new stuff.


In the original benchmark the Linq version was running at 13.62% of the original time.  And while I’m discussing that result, Sekiya Sato pointed out an error in my original benchmark (see the comments of the above posting) 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.


I have in my hands a nice fresh build, which is similar to what you’re going to get when you adopt Beta 2.  The results below include my original test plus an some quick insert and update tests I added — I’ll describe those in the next installment.  What we want to talk about right now is the select cases.  The regular select is as orignially described.  The syntax for the compiled select (and this really builds) is this:



var fq =CompiledQuery.Compile(
    (Northwinds nw) =>
        from o in nw.Orders
        select new OrderDetail
        {
                OrderID = o.OrderID,
                CustomerID = o.CustomerID,
                EmployeeID = o.EmployeeID,
                ShippedDate = o.ShippedDate
        }
);


Note that with the nice type inferencing you never have to see the generic types in your code but it’s still strongly typed.  To use this query you simply


 



foreach (var detail in fq(nw))
{
    sum += detail.OrderID;
    count++;
}


Now let’s have a look at the numbers:



























































 

no linq

       with linq

 

      select


  update


    insert


              original
select


   compiled
 select


  update


    compiled
    update


   insert

run 1 915.25 4.87 4.29 497.81 858.07 20.65 21.05 20.25
run 2 916.25 5.02 4.76 491.59 864.60 20.34 20.62 12.02
run 3 942.86 4.87 4.66 496.57 859.11 21.03 20.47 16.08
average 924.79 4.92 4.57 495.32 860.59 20.67 20.71 16.12

The units for all of the above are test iterations per second so bigger is better. 





































         dlinq      nolinq          ratio
select 495.32 924.79 53.56%
compiled select 860.59 924.79 93.06%
update 20.67 4.92 420.19%   (DLinq is faster)
compiled update 20.71 4.92 421.00%   (DLinq is faster)
insert 16.12 4.57 352.66%   (DLinq is faster)

 


Wow that’s pretty good.  If you do nothing to your code, just raw internal improvements go from 14.09% of the underlying provider to 53.56% — that’s a 3.8x improvement.  But look at what you can do with compiled queries: if you compile the select statement I got 93.06% of the underlying providers raw speed — that’s 6.6x faster than what I got back in May of 2006.  This is a truly great result because, as I’ve mentioned before, this is a harsh test. With the normal overheads associated with actual business logic and data transfer this result basically means that you may not even be able to measure any throughput degradation at all if you use compiled DLinq queries in your program.


I think I’ll let Matt talk about the details of how we did this because he did the work but I can give you the high level points if you haven’t already guessed them from the previous postings



  • create custom methods that bind the data perfectly using lightweight code generation

  • create reusable SQL with parameters to avoid generating the SQL query again

  • provide read-only contexts to avoid any unnecessary entity management (this not needed anyway in my case because I new up an OrderDetail object with only part of the data)

When I modelled this on paper last summer it looked like we could get about 95% of the underlying provider speed plus or minus measurement error and we seem to have landed at 93%. 


Now you may ask, why is DLinq doing better at updates than my code that writes directly to the underlying provider?  I’ll talk about this a bit more next time but the short answer is this:  the code I wrote to do the updates looks like pretty typical SQL sent to the database in batches.  However I didn’t go to the trouble of creating prepared statements for update and insert cases, DLinq gives you this automatically.  So despite my more complicated logic the savings DLinq got from superior SQL trumped my techinque.


And lastly, as always, this doesn’t necessarily translate to any specific numbers for your application but it sure bodes well.  I’m very pleased indeed.

Comments (24)

  1. Rico has finally posted some numbers that show you the performance he’s seeing with Beta 2 bits.

  2. c says:

    When wouldn’t I want to compile the query?  If it’s so much faster, why isn’t it the default?  Why would I have to write that magic code to make it perform well?

  3. ricom says:

    Compiling the query makes it durable. There is no need for this, nor is there any desire, unless you intend to run that same query many times.

    SQL provides regular select statements, prepared select statements, and stored procedures for a reason.  Linq now has analogs.

  4. Anders Borum says:

    Hi Rico,

    Have to admit that I’ve been waiting for your post to arrive in the previous days. The wait was well worth it :-)

    Even 53% is a great number for non-compiled queries. Can’t wait to beta 2 ..

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

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

  7. Rico Mariani has been posting about LINQ to SQL perfomance and has finally posted the performance numbers

  8. Rico Mariani has been posting about LINQ to SQL perfomance and has finally posted the performance numbers

  9. neuhawk says:

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

  10. Marco Russo says:

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

  11. kangnoz says:

    LINQ to SQL performance

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

  13. Ryan Ternier says:

    Rico,

    THere’s been some talk about having a DataBase as an entire layer in a solution. Using LINQ/DLINQ to  build all the queries etc.

    It seems that it can be done, but one thing that strikes me is the performance of this:

    You call one StoredProcedure with an Update, that stored procedure then calls 4 other stored procedures, and on this update you will get 8 result sets back.

    Using DLINQ I would assume you’d have to do multiple DLINQ statements to do the same functionality.  Is that correct?

    Thanks :)

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

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

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

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

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

  19. jankyBlog says:

    Risorse su Linq to SQL

  20. Wriju's BLOG says:

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

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

  22. Wriju's BLOG says:

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

  23. 吴明浩 says:

    Heythere,backagain.InmyfirstpostaboutLINQItriedtoprovideabrief(okay,bitdetailed)in…