780 Requests Per Second Verses 110, You Really Need to Compile your LINQ to SQL (LINQ2SQL) Queries

*Note 10/22/2011 (2+ years later):  Microsoft is fixing this problem in EntityFramework 4.5!  See my post here about it: http://peterkellner.net/2011/10/22/microsoft-to-add-auto-compiled-linq-queries-to-entity-framework-v-next/

 

So, I’ve been on kind of a rant lately about how slow LINQ2SQL is if you don’t compile your queries before executing them.  To be fair, if you are doing Windows Forms Programming, WPF or Silverlight it really does not have much impact.  The reason is that a very complex LINQ query may take 50 milliseconds (1/20th of a second).  No big deal if you just have a dozen or so of them to do.  The story changes though if you are using LINQ2SQL in a web environment that has limited CPU resources.  That is, unless you have unlimited money, if it takes more than one web server to handle your load, your throwing away money by using uncompiled LINQ2SQL.

So, to put some more substance behind my claims, I’ve written a small test application using Visual Studio 2008 that compares the performance of using LINQ2SQL compiled verses non-compiled on a trivial web page.

Here are some assumptions:

  • My Notebook Computer Simulates a real world web server (Lenovo W500, Vista64, IIS7)
  • All Web Pages do 7 LINQ2SQL executions (Heading speak at  VSLive in Vegas so 7 seemed right)
  • All Web Requests Do Only LINQ2SQL, no images, css, JavaScript or anything else

OK, maybe my assumptions are not so good, but I believe overall I’m making a good point so I’m going to keep going rather than just say “take my word for it, trust me”.  So, I’m attaching the web project here that let’s you recreate all this so you can do your own tests.  I think you’ll need Visual Studio Team Suite to do these tests.  If you don’t have that, you can at least look at the code.  The guts are in LINQUtils.cs.  I’m going to show the relevant code below.

But before that, here is the Visual Studio 2008 Project Code if you want to try it yourself:

Download: Project Zip File

First, The Results

notcompiled notcompiled1
compiled image

The code is very straight forward.  Below is the code for the LINQ2SQL Not Compiled Test:

  1: public string ProcessLinqQueryNotCompiled()
  2: {
  3:     if (meta == null)
  4:     {
  5:         meta = new DataClassesDataContext();
  6:     }
  7: 
  8:     Stopwatch stopWatch = new Stopwatch();
  9:     stopWatch.Start();
 10: 
 11:     int retCnt = 0;
 12:     for (int i = 0; i < iterations; i++)
 13:     {
 14:         const int companyTypeIdToFind = 1;
 15:         var q3 = from dataCo in meta.Companies
 16:                  where (from dataCt in meta.CompanyCompanyTypes
 17:                         where dataCt.CompanyTypeId == companyTypeIdToFind
 18:                         select dataCt.CompanyId).Contains(dataCo.Id)
 19:                  select dataCo;
 20: 
 21:         var newList = q3.ToList();
 22:         retCnt = newList.Count;
 23:     }
 24:     stopWatch.Stop();
 25:     double milliSecondsPerIteration = Convert.ToDouble(stopWatch.ElapsedMilliseconds)/Convert.ToDouble(iterations);
 26:     return string.Format("Return Cnt: {0} LINQ Executions: {1}      Milliseconds Per Iteration: {2}", retCnt, iterations,
 27:                          String.Format("{0:0.00}", milliSecondsPerIteration));
 28: }

And, the Compiled LINQ2SQL Code:

  1: public string ProcessLinqQueryCompiled()
  2: {
  3:     var stopWatch = new Stopwatch();
  4:     stopWatch.Start();
  5: 
  6:     int retCnt = 0;
  7:     for (int i = 0; i < iterations; i++)
  8:     {
  9:         const int companyTypeIdToFind = 1;
 10: 
 11:         if (meta == null)
 12:         {
 13:             meta = new DataClassesDataContext();
 14:         }
 15: 
 16:         if (_compiledQuery == null)
 17:         {
 18:             _compiledQuery =
 19:                 CompiledQuery.Compile((DataClassesDataContext metax, int myQuery) =>
 20:                                       (from dataCo in metax.Companies
 21:                                        where (from dataCt in metax.CompanyCompanyTypes
 22:                                               where dataCt.CompanyTypeId == companyTypeIdToFind
 23:                                               select dataCt.CompanyId).Contains(dataCo.Id)
 24:                                        select dataCo)
 25:                     );
 26:         }
 27: 
 28:         
 29:         var query =
 30:             (IOrderedQueryable<Company>)
 31:             _compiledQuery(meta, 1);
 32:         var newList = query.ToList();
 33:         retCnt = newList.Count;
 34:     }
 35:     stopWatch.Stop();
 36:     double milliSecondsPerIteration = Convert.ToDouble(stopWatch.ElapsedMilliseconds) / Convert.ToDouble(iterations);
 37:     return string.Format("Return Cnt: {0} LINQ Executions: {1}      Milliseconds Per Iteration: {2}", retCnt, iterations,
 38:                          String.Format("{0:0.00}", milliSecondsPerIteration));
 39: 
 40: }

Bottom line, You need to compile your LINQ2SQL!

About Peter Kellner

Peter is a software professional specializing in mobile and web technologies. He has also been a Microsoft MVP for the past 7 years. To read more about Peter Kellner and his experience click here. For information about how Peter Kellner might be able to help you with your project click here.

Follow me:


Comments

  1. Hi there, I found your website by the use of Google whilst searching for a related subject, your web site got here up, it looks good. I’ve bookmarked it in my google bookmarks.

  2. Belen Trussler says:

    The new Zune browser is surprisingly good, but not as good as the iPod’s. It works well, but isn’t as fast as Safari, and has a clunkier interface. If you occasionally plan on using the web browser that’s not an issue, but if you’re planning to browse the web alot from your PMP then the iPod’s larger screen and better browser may be important.

  3. We’ve never really measured it eactly, but we’ve noticed the difference between uncompiled and compiled L2S. We’d like it if MS made it bit ‘friendlier’ to use…

  4. no downsides but limitations in terms of the kind of things you can pass in. It’s just hugely inconvenient to compile your queries, and not a very friendly syntax.

  5. I’ve used LINQ2Objects but I wasn’t aware of being able to specify that it should be compiled. Is there a downside to compiling?

Trackbacks

  1. […] 780 Requests Per Second Verses 110, You Really Need to Compile your LINQ to SQL (LINQ2SQL) Queries 09.06.2009 | Posted in Computer World   So, I’ve been on kind of a rant lately about how slow LINQ2SQL is if you don’t compile your queries before executing them.  To be fair, if you are doing Windows Forms Programming, WPF or… This site is a resource for asp.net web programming. It has examples by Peter Kellner of techniques for high performance programming More here: 780 Requests Per Second Verses 110, You Really Need to Compile your LINQ to SQL (LINQ2SQL) Queries […]

Follow

Get every new post delivered to your Inbox

Join other followers: