Wooley's LINQ Wonderings

Insights and observations regarding LINQ

LINQ to SQL Compiled Queries

As LINQ nears release, people are starting to consider the performance implications that the extra overhead brings. Currently there are two threads on this: thread1, thread2. For those that are intested in the performance implications, I highly recommend checking out the outstanding series of posts by Rico Mariani.

Ultimately if you want to get the best performance, you need to use the Compile function of the CompiledQuery class. Let's consider the following query (Note, turn this into C# by adding a semi-colon at the end if necessary):

From p In context.Products() _
            Where (p.Price >= minimumPrice) _
            Select p)

In this query, we are searching for the products that are "Expensive" in that their price exceeds a price value that we set. If we regularly consume this, we can eliminate the overhead of building the query pipeline. See the Matt Warren talk about this pipeline on the deep dive video at on Charlie Calvert's blog for more information regarding the overhead that is necessary to evaluate a query.

To compile the query, we can leverage the CompiledQuery.Compile method. This method takes an argument list as input and a result type. It returns a delegate as a variable that we will be able to consume. Thus in this case, we can pass the DataContext instance and the minimumPrice variable in. We will return a IQueryable(of Product) object. thus we can start the definition of our compiled query as follows:

VB: 
     CompiledQuery.Compile( _
         Function(context As MyDataContext, minimumPrice As Decimal) _
            From p In context.Products() _
            Where (p.Price >= minimumPrice) _
                    Select p)

C#:
     CompiledQuery.Compile(
            (MyDataContext context, decimal minimumPrice) =>
                    from p in context.Products
                    where p.Price >= minimumPrice
                    select p);

In this case we are defining our query as a Lambda function which the Compile can convert to the corresponding expression tree representation. If you are unfamiliar with Lambdas in VB, check out Timothy Ng's recent MSDN Magazine article.

With the delegate functions declared, all that is left is to actually assign them to a variable that we can consume later. To do this, we define a static/shared function which returns the Func anonymous delegate type. By defining it as a static/shared function, the compilation will only occur once per AppDomain and will remain cached through the rest of the application's lifetime. Notice, we are defining the signature of the query, not the results. We are free to change the parameter without needing to re-compile the query's structure. Here's the completed function calls in VB and C#:

Public Shared ExpensiveProducts As Func(Of MyDataContext, Decimal, IQueryable(Of Product)) = _
       CompiledQuery.Compile(Function(context As MyDataContext, minimumPrice As Decimal) _
            From p In context.Products() _
            Where (p.Price >= minimumPrice) _
            Select p)

   public static Func<MyDataContext, decimal, IQueryable<Product>>
         ExpensiveProducts =
           
 CompiledQuery.Compile((MyDataContext context, decimal minimumPrice) =>
                    from p in context.Products
                    where p.Price >= minimumPrice
                    select p);

The syntax for using the compiled query takes a bit of thinking to get your head around. We are essentially creating a function which returns a function rather than returning a value. If you're familiar with functional programming such as OCaml and F#, this concept should be easy to grok. For the rest of us, piece out the individual method components as laid out above and you should be able to understand it after a while ;-)

To consume our new function, we simply instantiate a DataContext instance and pass it along with the minimumPrice we want to set as our minimum value.

  Dim context As New MyContext(My.Settings.MyConnectionString)
  Dim Items = ExpensiveProducts(context, minimumPrice)

Technorati tags: , ,,

Crossposted from http://devauthority.com/blogs/jwooley/default.aspx
Published Wednesday, September 5, 2007 1:20 AM by jwooley
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

JD Conley said:

I just ran into a case in the wild where using compiled queries was really beneficial: http://jdconley.com/blog/archive/2007/11/28/linq-to-sql-surprise-performance-hit.aspx. This post helped a lot.

November 28, 2007 9:55 PM
 

Bruno said:

Do you know if its possible to use a List<T>.Contains() comparision in a complied query (in order to use the SQL 'IN' clause) ?

I tried to implement this in a complied query and it always raises an exception telling me that this operation cannot be done...

August 27, 2008 1:38 PM
 

Anton said:

Use arrays instead of lists and it will work ;-)

October 10, 2008 4:24 AM
 

Ricky said:

This post says "By defining it as a static/shared function, the compilation will only occur once per AppDomain and will remain cached through the rest of the application's lifetime." There's a similar sentence in the book near the bottom of p. 295.  I think this is misleading at best.  The sentence seems to imply that it is the "static" keyword that ensures that the compilation will only occur once.  That is not the case, is it?  The static keyword means that the compilation will be performed by the static constructor the first time the class type is referenced.  Leaving off the "static" keyword means the compiliation will be performed by the object constructor (and will be scoped to the object).  But in BOTH cases, compilation will occur only once.

Also, neither the book nor the post (nor the .Net 3.5 SDK) ever explain exactly what CompiledQuery.Compile() actually does and when it does it. Does it create an expression tree from the lambda expression? Or does it translate an expression tree into SQL? Or does it do both? And WHEN does it do its work: When the user clicks Build in Visual Studio? When a constructor is called at runtime? When the field ExpensiveProducts is reference the first time?

November 28, 2008 4:09 PM
 

LINQ-To-SQL Improving Performance | Coding Adventure said:

July 25, 2010 10:21 PM
 

LINQ-To-SQL Improving Performance | New Dev Central said:

September 4, 2010 2:01 AM
 

Experts Comment said:

LINQ to SQL CompiledQuery is good option, but things that needs to be taken into consideration while using the same is that first time when it is called it takes more time than a routine query would take. Refer to below link...

http://www.a2zmenu.com/LINQ/LINQ%20to%20SQL%20CompiledQuery.aspx

September 11, 2010 8:53 AM
 

Mike Gleason jr Couturier said:

Nice post. Okay you can compile your queries, but if you are interested in what's actually happening under the hood when you're doing that, you can click on my name to see my analysis.

Thanks !

March 20, 2011 12:53 PM

Leave a Comment

(required) 
(optional)
(required) 
Submit