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

Updated: at 11:17 PM

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

 

If you have a problem like this involving ASP.NET, LINQ, EF, etc., consulting services are available. We love these kind of problems. Contact Peter Kellner and his associates here.

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:

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!