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: 

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!

---



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

1. 780 Requests Per Second Verses 110, You Really Need to Compile your LINQ to SQL (LINQ2SQL) Queries | Real Rumors Says:
   June 8th, 2009 at 10:30 pm

   [...] 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 [...]

2. Shane Says:
   June 9th, 2009 at 11:06 am

   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?

3. peter kellner Says:
   June 9th, 2009 at 12:09 pm

   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.

4. Daily Links for Wednesday, June 10th, 2009 Says:
   June 10th, 2009 at 5:47 pm

   [...] 780 Requests Per Second Verses 110, You Really Need to Compile your LINQ to SQL (LINQ2SQL) Queries |… [...]

5. This week on C9: XNA updates, Win 7 Training, VS 2010, build your own arcade | Tech-monkey.info Blogs Says:
   June 12th, 2009 at 2:08 pm

   [...] – Howard Dierking - Extreme MSDN Magazine ASP.NET Makeover screencast series – Peter Kellner - Compile LINQ to SQL to improve query performance (110 uncompiled vs 780 requests compiled), via Alvin Ashcraft [...]

6. Jens Says:
   June 14th, 2009 at 11:24 pm

   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…

Leave a Reply

Name (required)

Mail (will not be published) (required)

Website