Omar AL Zabir blog on ASP.NET Ajax and .NET 3.5

Working hard to enrich millions of peoples' lives

Sponsors

News

I was
Co-Founder and CTO of Pageflakes, acquired by LiveUniverse - founded by MySpace founder.

I am
Chief Architect, SaaS Platform, British Telecom

I will be
Chief Architect, Mi...

Follow omaralzabir on Twitter

My Public Page
www.pageflakes.com/omar

View Omar AL Zabir's profile on LinkedIn

Read my blog on:

Omar AL Zabir

www.oazabir.com



Views:

Open source projects

Solving common problems with Compiled Queries in Linq to Sql for high demand ASP.NET websites

If you are using Linq to SQL, instead of writing regular Linq Queries, you should be using Compiled Queries. if you are building an ASP.NET web application that’s going to get thousands of hits per hour, the execution overhead of Linq queries is going to consume too much CPU and make your site slow. There’s a runtime cost associated with each and every Linq Query you write. The queries are parsed and converted to a nice SQL Statement on *every* hit. It’s not done at compile time because there’s no way to figure out what you might be sending as the parameters in the queries during runtime. So, if you have common Linq to Sql statements like the following one throughout your growing web application, you are soon going to have scalability nightmares:

var query = from widget in dc.Widgets
where widget.ID == id && widget.PageID == pageId
select widget;

var widget = query.SingleOrDefault();

There’s a nice blog post by JD Conley that shows how evil Linq to Sql queries are:

image

You see how many times SqlVisitor.Visit is called to convert a Linq Query to its SQL representation? The runtime cost to convert a Linq query to its SQL Command representation is just way too high.

Rico Mariani has a very informative performance comparison of regular Linq queries vs Compiled Linq queries performance:

image

Compiled Query wins on every case.

So, now you know about the benefits of compiled queries. If you are building ASP.NET web application that is going to get high traffic and you have a lot of Linq to Sql queries throughout your project, you have to go for compiled queries. Compiled Queries are built for this specific scenario.

In this article, I will show you some steps to convert regular Linq to Sql queries to their Compiled representation and how to avoid the dreaded exception “Compiled queries across DataContexts with different LoadOptions not supported.”

Here are some step by step instruction on converting a Linq to Sql query to its compiled form:

First we need to find out all the external decision factors in a query. It mostly means parameters in the WHERE clause. Say, we are trying to get a user from aspnet_users table using Username and Application ID:

Query to get a user from aspnet_users table

Here, we have two external decision factor – one is the Username and another is the Application ID. So, first think this way, if you were to wrap this query in a function that will just return this query as it is, what would you do? You would create a function that takes the DataContext (dc named here), then two parameters named userName and applicationID, right?

So, be it. We create one function that returns just this query:

Converting a LInq Query to a function

Next step is to replace this function with a Func<> representation that returns the query. This is the hard part. If you haven’t dealt with Func<> and Lambda expression before, then I suggest you read this and this and then continue.

So, here’s the delegate representation of the above function:

Creating a delegate out of Linq Query 

Couple of things to note here. I have declared the delegate as static readonly because a compiled query is declared only once and reused by all threads. If you don’t declare Compiled Queries as static, then you don’t get the performance gain because compiling queries everytime when needed is even worse than regular Linq queries.

Then there’s the complex Func<DropthingsDataContext, string, Guid, IQueryable<aspnet_User>> thing. Basically the generic Func<> is declared to have three parameters from the GetQuery function and a return type of IQueryable<aspnet_User>. Here the parameter types are specified so that the delegate is created strongly typed. Func<> allows up to 4 parameters and 1 return type.

Next comes the real business, compiling the query. Now that we have the query in delegate form, we can pass this to CompiledQuery.Compile function which compiles the delegate and returns a handle to us. Instead of directly assigning the lambda expression to the func, we will pass the expression through the CompiledQuery.Compile function.

Converting a Linq Query to Compiled Query

Here’s where head starts to spin. This is so hard to read and maintain. Bear with me. I just wrapped the lambda expression on the right side inside the CompiledQuery.Compile function. Basically that’s the only change. Also, when you call CompiledQuery.Compile<>, the generic types must match and be in exactly the same order as the Func<> declaration.

Fortunately, calling a compiled query is as simple as calling a function:

Running Compiled Query

There you have it, a lot faster Linq Query execution. The hard work of converting all your queries into Compiled Query pays off when you see the performance difference.

Now, there are some challenges to Compiled Queries. Most common one is, what do you do when you have more than 4 parameters to supply to a Compiled Query? You can’t declare a Func<> with more than 4 types. Solution is to use a struct to encapsulate all the parameters. Here’s an example:

Using struct in compiled query as parameter

Calling the query is quite simple:

Calling compiled query with struct parameter

Now to the dreaded challenge of using LoadOptions with Compiled Query. You will notice that the following code results in an exception:

Using DataLoadOptions with Compiled Query

 

The above DataLoadOption runs perfectly when you use regular Linq Queries. But it does not work with compiled queries. When you run this code and the query hits the second time, it produces an exception:

Compiled queries across DataContexts with different LoadOptions not supported

A compiled query remembers the DataLoadOption once its called. It does not allow executing the same compiled query with a different DataLoadOption again. Although you are creating the same DataLoadOption with the same LoadWith<> calls, it still produces exception because it remembers the exact instance that was used when the compiled query was called for the first time. Since next call creates a new instance of DataLoadOptions, it does not match and the exception is thrown. You can read details about the problem in this forum post.

The solution is to use a static DataLoadOption. You cannot create a local DataLoadOption instance and use in compiled queries. It needs to be static. Here’s how you can do it:

image

 

Basically the idea is to construct a static instance of DataLoadOptions using a static function. As writing function for every single DataLoadOptions combination is painful, I created a static delegate here and executed it right on the declaration line. This is in interesting way to declare a variable that requires more than one statement to prepare it.

Using this option is very simple:

image

Now you can use DataLoadOptions with compiled queries.

kick it on DotNetKicks.com
Posted: Oct 28 2008, 02:24 AM by oazabir | with 3 comment(s)
Filed under: , , ,

Comments

James Clarke said:

Great post,  came up against this problem and followed your bit about the static DataLoadOptions but I found that if you try this on an ASPX code-behind for example you have to be sure to specify the load options variables as static instance variables.  Just calling the func from an event handler will still get the same problem on second time loading.

Many thanks,

James Clarke

# November 11, 2008 8:57 AM

Oleg said:

Thanks, great post.

So what exactly happens when you work with compiled query e.g. doing something like this:

Compiled_AllNewOrders(...).Where(o => o.Something == true).Skip(10).Take(10)

Do you retain any performance benefits of the compiled part of the final query?

# November 22, 2008 6:10 PM

Rick said:

This post contains the following statement:

"If you don’t declare Compiled Queries as static, then you don’t get the performance gain because compiling queries everytime when needed is even worse than regular Linq queries."

I believe this is a common mistake in discussions of compiled queries.  Whether or not you declare the delegate "static" or not simply determines what ctor creates it (and when) and how long it lives. IT HAS NO EFFECT ON WHETHER THE QUERY IS GOING TO BE COMPILED EVERYTIME OR NOT. If the delegate is "static" then the class's static ctor creates it the first time the class name is referenced and it lives until the AppDomain dies. If it is not "static" then the class's object ctor creates it when an object is created and it lives until the object is disposed.

What determines whether the query is compiled every time or not is the presence of absence of CompiledQueries.Compile(): If the lambda expression is NOT wrapped in CompiledQueries.Compile(), then it will compile every time. If it IS wrapped in CompiledQueries.Compile(), then it will compiled just once. This is true regardless of whether the delegate variable is "static" or not.

Also, having "readonly" in your examples just distracts from the point of the post.  This keyword simply means that a programmer cannot assign a different function to the delegate variable somewhere in the code after it is first created by a ctor. But this has nothing to do with whether or not the query is going to be compiled more than once.

# November 28, 2008 8:13 PM

Rik said:

What if you add your own extension method that enables a wherein clause on iqueryable, (as I have done).

i.e. call would look like...

var query = dc.aspnet_Users.AsQueryable();

query = query.WhereIn(u=>u.WhateverID, new List<int> {10,11,12,13,14});

How could this be made into a compiled query? I imagine a struct of unknown size could be a problem, or inefficient if try to cater for more parameters than might be used.

Regards,

Rik

# November 29, 2008 9:04 AM

Dave said:

Hi,

Rick, either i missunderstand what you wrote or you are wrong:

Everytime you call CompiledQuery.Compile(...) the given Expression is compiled.

Even if you pass the same Expression Instance to the Compile Method it will compile the expression again and again.

If you call it 2 times, then the passed Expression will be compiled 2 times.

Said this, if you want to see a performance improvement by using CompiledQuery, you should call it only once for the same Query and then reuse the returned Delegate multiple times.

However, if the Delegate returned by the CompiledQuery.Compile() Method will be stored into a static Variable or an instance Variable does not matter, as long as you reuse the same instance of the Delegate.

Regards,

Dave

# December 6, 2008 4:46 PM

mlife said:

"Compiled queries across DataContexts with different LoadOptions not supported"

Excellent Tip Omar!

Thank you

# September 2, 2009 3:00 PM

Adriano said:

what can I do when I use my methods into compiled query like Busca_Configuracao_Teste()?

private static Func<ParametroEntity, contrato, Configuracao_Historico> _Qry_Configuracao_Historico = CompiledQuery.Compile((ParametroEntity _db, cContrato _contrato) =>(from c in _db.Relaciona_Configuracao_Historico.Busca_Configuracao_Teste(_contrato.Id_Grupo, _contrato.Id_Cliente, _contrato.Id_Fase, _contrato.Id_Filial, _db.Cliente_Fase_Filial).Cast<Relaciona_Configuracao_Historico>()

                  select c.Configuracao_Historico).FirstOrDefault());

ERROR: LINQ to Entities does not recognize the method...

# February 10, 2010 6:35 AM

Demis Bellot said:

Hmmm, nice post except doing all that work has made the code a lot more unreadable and requires a lot more development effort. IMHO the query caching should be happening transparently behind the scenes by using the LINQ provider.

I'm sure given enough time the EF crew will be able to provide the most efficient solution.

# March 11, 2010 4:28 AM

jmagicie said:

Here's an experience:

Facebook app doing 22 million uniques a month.  No compiled queries.  Quad Core Xeon 2.8 something web server sitting pretty at 2% cpu usage during PEAK times.  

# March 19, 2010 11:37 PM

oazabir said:

Must be heavily cached data. How may Transaction/sec and Batch query/sec on SQL Server?

# March 20, 2010 4:37 PM