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: 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:

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

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!