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 "http://linqinaction.net/blogs/jwooley/archive/2007/09/04/linq-to-sql-compiled-queries.aspx">
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 "http://www.jdconley.com/blog/archive/2007/11/28/linq-to-sql-surprise-performance-hit.aspx">
a nice blog post by JD Conley
that shows how evil Linq to Sql
queries are:

"/wp-content/images/76F5F519677BAB7771582A9372CD282D.png">
"border-top-width:0px;display:inline;border-left-width:0px;border-bottom-width:0px;border-right-width:0px;"
alt="image" src=
"/wp-content/images/CDBB4FE6AC93A7974F6EACD8E3DC99FC.png"
border="0" height="389" width="654">

You see how many times "text-decoration:underline;">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.

"http://blogs.msdn.com/ricom/archive/2008/01/14/performance-quiz-13-linq-to-sql-compiled-query-cost-solution.aspx">
Rico Mariani has a very informative performance comparison
of
regular Linq queries vs Compiled Linq queries performance:

"/wp-content/images/A65EB37315438EB9AD27F417E8DA6F75.png">
"border-top-width:0px;display:inline;border-left-width:0px;border-bottom-width:0px;border-right-width:0px;"
alt="image" src=
"/wp-content/images/603AFAC446C6B8C6E02B07E343407F17.png"
border="0" height="252" width="531">

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 "text-decoration:underline;">aspnet_users table using
Username and Application ID:

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 "text-decoration:underline;">DataContext (dc named here),
then two parameters named "text-decoration:underline;">userName and "text-decoration:underline;">applicationID, right?

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

"/wp-content/images/F6A8A5AF3D072C711FD326E1C0F3E16C.png">
"border-top-width:0px;display:inline;border-left-width:0px;border-bottom-width:0px;border-right-width:0px;"
alt="Converting a LInq Query to a function" src=
"/wp-content/images/1A5E4F49EC831376C2CF33A7C9B430A1.png"
border="0" height="103" width="693">

Next step is to replace this function with a "text-decoration:underline;">Func<> representation
that returns the query. This is the hard part. If you haven’t
dealt with "text-decoration:underline;">Func<> and Lambda
expression before, then I suggest you read "Lambda Expression" href=
"http://blogs.msdn.com/ericwhite/pages/Lambda-Expressions.aspx">this

and "http://blah.winsmarts.com/2006/05/19/demystifying-c-30--part-4-lambda-expressions.aspx">
this
and then continue.

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

"/wp-content/images/7D226D1DCA75D882E8A6C9B791B611D0.png">
"border-top-width:0px;display:inline;border-left-width:0px;border-bottom-width:0px;border-right-width:0px;"
alt="Creating a delegate out of Linq Query" src=
"/wp-content/images/498AED307E185F9D52DEA893EEA07E41.png"
border="0" height="114" width="573">

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 "text-decoration:underline;">Func Guid, IQueryable> thing. Basically the
generic "text-decoration:underline;">Func<> is declared to
have three parameters from the "text-decoration:underline;">GetQuery function and a return
type of "text-decoration:underline;">IQueryable.
Here the parameter types are specified so that the delegate is
created strongly typed. "text-decoration:underline;">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 "text-decoration:underline;">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 "text-decoration:underline;">CompiledQuery.Compile
function.

"/wp-content/images/DDB65134CB351655BC850330EC80DC52.png">
"border-top-width:0px;display:inline;border-left-width:0px;border-bottom-width:0px;border-right-width:0px;"
alt="Converting a Linq Query to Compiled Query" src=
"/wp-content/images/C5F6B65BCB13DB337D99641F0FA6CDE3.png"
border="0" height="133" width="677">

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 "text-decoration:underline;">CompiledQuery.Compile function.
Basically that’s the only change. Also, when you call
"text-decoration:underline;">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:

"/wp-content/images/0B94EF63F2EAF94191A210A853D021C9.png">
"border-top-width:0px;display:inline;border-left-width:0px;border-bottom-width:0px;border-right-width:0px;"
alt="Running Compiled Query" src=
"/wp-content/images/DFD5DE856841ABC3F3A667D9A8DA9E17.png"
border="0" height="89" width="716">

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 "text-decoration:underline;">Func<> with more than 4
types. Solution is to use a "text-decoration:underline;">struct to encapsulate all the
parameters. Here’s an example:

"/wp-content/images/DFA586708EFE05F70AE4FAA0ED0F7BEC.png">
"border-top-width:0px;display:inline;border-left-width:0px;border-bottom-width:0px;border-right-width:0px;"
alt="Using struct in compiled query as parameter" src=
"/wp-content/images/DDA289F7B31865CB375ACF2F05A71771.png"
border="0" height="245" width="736">

Calling the query is quite simple:

"/wp-content/images/887F207A15AAEF45598347B47609282A.png">
"border-top-width:0px;display:inline;border-left-width:0px;border-bottom-width:0px;border-right-width:0px;"
alt="Calling compiled query with struct parameter" src=
"/wp-content/images/C799FF8806885DD709D3C708D9123EE4.png"
border="0" height="55" width="674">

Now to the dreaded challenge of using "text-decoration:underline;">LoadOptions with Compiled
Query. You will notice that the following code results in an
exception:

"/wp-content/images/9A4AAAFB3C0DB1BEDA5BBE9ECCBEA704.png">
"border-top-width:0px;display:inline;border-left-width:0px;border-bottom-width:0px;border-right-width:0px;"
alt="Using DataLoadOptions with Compiled Query" src=
"/wp-content/images/0A8509779D1F45D6C8868C502E497863.png"
border="0" height="133" width="647">

The above "text-decoration:underline;">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 "text-decoration:underline;">DataLoadOption once its called.
It does not allow executing the same compiled query with a
different "text-decoration:underline;">DataLoadOption again. Although
you are creating the same "text-decoration:underline;">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 "text-decoration:underline;">DataLoadOptions, it does not
match and the exception is thrown. You can read details about the
problem in "Compiled Queries cannot take same DataLoadOption" href=
"http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2558764&SiteID=1">
this forum post
.

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

"/wp-content/images/F339878E4950D573BB183FA159E08D21.png">
"border-top-width:0px;display:inline;border-left-width:0px;border-bottom-width:0px;border-right-width:0px;"
alt="image" src=
"/wp-content/images/8E32FE6AF9D0506FE4F2EAFA83B0C6A0.png"
border="0" height="146" width="589">

Basically the idea is to construct a "text-decoration:underline;">static instance of "text-decoration:underline;">DataLoadOptions using a static
function. As writing function for every single "text-decoration:underline;">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:

"/wp-content/images/D899B8F169C605196E0D921DA5EDED09.png">
"border-top-width:0px;display:inline;border-left-width:0px;border-bottom-width:0px;border-right-width:0px;"
alt="image" src=
"/wp-content/images/CA4BC3777E6F9C5A5C5266995D2A84A1.png"
border="0" height="73" width="670">

Now you can use "text-decoration:underline;">DataLoadOptions with compiled
queries.

"http://www.dotnetkicks.com/kick/?url=http%3a%2f%2fmsmvps.com%2fblogs%2fomar%2farchive%2f2008%2f10%2f27%2fsolving-common-problems-with-compiled-queries-in-linq-to-sql-for-high-demand-asp-net-websites.aspx">
"http://www.dotnetkicks.com/Services/Images/KickItImageGenerator.ashx?url=http%3a%2f%2fmsmvps.com%2fblogs%2fomar%2farchive%2f2008%2f10%2f27%2fsolving-common-problems-with-compiled-queries-in-linq-to-sql-for-high-demand-asp-net-websites.aspx"
alt="kick it on DotNetKicks.com" border="0">

20 Comments

  1. I've applied this on LINQ to Entites, but Omar I have a question! Shall I use this will frequently used queries?! what if I decided to be smart and complie each Query? what would be the concequences?

  2. Isn't easier and faster to create a store procedure and call the SP through LINQ?

  3. So much code for such little reward.

    It seems a lot easier in many respects to go back to basics, as ive had my fingers burnt a few times with things like this in live situations.

    LINQ to SQL requires a huge learning investment just to get it performing at an acceptable performance level.

  4. You must make all DataLoadOptions static even if don't use compiled queries. It is a deep dive secret of L2S I've found hard way.

    L2S cache last 10 entity materializers and will reuse them but only if DataContext has has the same (or empty) instance of DataLoadOptions assigned. otherwise it will compile it EACH time spending cpu and memory.

    Look at System.Data.Linq.SqlClient.ObjectReaderCompiler.Compile(SqlExpression, Type) : IObjectReaderFactory

    and System.Data.Linq.SqlClient.ObjectReaderCompiler.maxReaderCacheSize : Int32

  5. I was planning on changing my dal to use linq to sql to make my life creating queries in the bll abit easier and less painful but the performance hit has put me off completely. I appreciate the workaround but it's more work than going back to basics. I hope this will be resolved in .net 4.

  6. Nice post.

    I would welcome any performance improvement in linq to sql. However, there is some stories floating around saying linq to sql is dead.

    BTW, is it possible to use pure code instead of posting the screen shots?

  7. Thanks for the info on how to use CompiledQueries with LoadWith! I've been trying to figure that out for a while now and had just about concluded it couldn't be done.

    Now I've just got to update my app to use static instances . . .

  8. I too have been searching for a way to use the DataLoadOptions w/ compiled queries, so thanks for the post!

    It's only natural that you'd want to use DataLoadOptions w/ a compiled query, but it took me way to long to find this answer.

    Is there a way that doesn't require an additional line of code to use it each time you want to use the compiled query?

    Couldn't the static DataLoadOptions somehow be part of the initialization of the compiled query?

  9. Hi daniel,

    “in fact if they are they become less efficient that dynamic queries. “

    Compiled Queries are definitely more efficient than dynamic queries in terms of execution. They are compiled, faster and does not consume memory like dynamic queries does on every execution.

    I believe you wanted to say compiled queries become less reusable than dynamic queries.

    Compiled queries are like fixed queries. You have to build the whole query and just call it. I do not know a way to join a compiled query with another compiled query or dynamic query. In fact I am curious how you join a dynamic query with another one.

  10. Omar, great article. I had hoped this would solve some of the speed issues that we have been facing, however was disapointed to find that the compiled queries couldn't be reused further, in fact if they are they become less efficient that dynamic queries.

    Eg: I tried to join another table onto a compiled query, but this blew out the query. However it looks like I can refine the query without losing performance so long as I'm not changing the structure by adding more tables etc.

    Are there any way to join onto compiled queries without losing the performance gain?

  11. Hi Omar, thanks for that.

    I guess because the Compiled Queries return an IQueryable I was expecting to be able to reuse it like you do a dynamic query:

    IQueryable query = …(ExecuteCompiledQuery)

    and then be able to go:

    int totalCount = query.Count();

    List results = query.Skip(10).Take(10).ToList();

    But having looked into it, I can see that the query is actually executed immediately on the SQL box when you call the Compiled Query.

    So I guess I would need a seperate compiled query for the count and then another compiled query which has “skip” and “take” paramters to actually get the search result range I'm after… The problem with this is that I have to write and maintain duplicate code.

  12. Hi, I tried to use this but it failed.

    Im using an object of type Expression > in may where clause. like this:

    internal static readonly

    Func>,IQueryable >

    Compiled_GetParameters = CompiledQuery.Compile>, IQueryable >(

    (db,predicate) => db.MswToParameter.Where(predicate).Select(m => m.Parameter).Distinct());

    When called I get an NotSupportedException: Unsupported overload used for query operator 'Where'.

    The query works as a dynamic linq query.

    What have I missed? Where shall I begin to search for the error?

  13. Hi, very good explaination.

    But I missed one thing. The comparison to stored procedures in SQL Server. They can be executed by LINQ.

    Finally I would say this is just an opinion when it's not possible to add a stored procedure.

  14. Hi,

    I don't get at all how you are “passing” the delegate to the compiled query. The delegate is called GetUserFromUserName, right? There is no reference to this function in the Compiled_GetUserFromUserName. When I mimic this code I get an error stating “cannot convert method group SingleOrDefault to non-delegate type System.Linq.IQueryable”. What am I missing?

  15. Thank you for submitting this cool story – Trackback from PimpThisBlog.com

  16. Hi,

    Very clear walk through!

    Using LINQ to SQL, I need to change the database schema at runtime. Table names remain the same as in design time, just the schema will be different; i.e. design time configuration is like this [Table(Name="schema1.table")], while at runtime I need to use “table” from schema2, as in [Table(Name="schema2.table")].

    If I use dynamic mapping as here: http://msdn.microsoft.com/en-us/bb546173.aspx, would I still benefit from the performance of the compiled queries?

    To be more concise I plan to instantiate the DataContext multiple times and pass a different metadata each time I instantiate it. Would the query be recompiled each time the data context is instantiated with different metadata? I guess re-instantiating the context should make no difference to the compiled query, but as I haven’t done myself any tests, but maybe someone knows already the answer?

    Thanks in advance!

  17. Hi!
    How can i compile query like this (different number of params):

    IQueryable q = …
    if (orderId != null)
    q = q.Where(q => q.OrderID == orderId);
    if (userId != null)
    q = q.Where(q => q.UserId == userId);

    see the problem, if orderId == null then I don’t need that where part. Is the only solution writing code like this:

    q = q.Where(q => (q.UserId == userId || userId == null) && (q.OrderId == orderId || orderId == null));

    but that is less efficient because of not needed comparisson if userId or orderId is null. Or that isn’t much? Pls enlighten me:)

Leave a Reply