Tonight at Philly.Net we are having our annual “15 Minutes of Fame” meeting.  That is where we have 10 of our regular presenters do a short 15 minute presentation on something they think is cool.  We’ve got a great line up planned for tonight and I am excited to not just present my topic but also learn a few things.  These mini presentations give the audience just enough to get started, plant a seed, wet your whistle, etc. 


It’s not always so easy to come up with an idea for such a short presentation.  I thought LINQ Compiled Queries was a good one because, while it is a big feature of LINQ, it is relatively easy to implement.  I also think people avoid it because it sounds complicated.  In fact, it is not. 


I’ll demonstrate with some code taken from this VS2008 solution.


Here is a regular method that takes a few parameters, executes a “SELECT” on the Northwind Customers table, and returns a list of Orders.  You’ll also note that there are parameters to support paging, a common scenario with LINQ.


public static List<Order> GetOrdersNotCompiled(string custName, string shipCountry, int skip, int take)
{
//even though the query is compiled, you can still pass in parameters.
using (NorthwindDataContext db = new NorthwindDataContext())
{
List<Order> orders = (from order in db.Orders
where order.Customer.CompanyName == custName
&& order.ShipCountry == shipCountry
select order).Skip(skip).Take(take).ToList();

return orders;
}
}


So how to convert this to a Compiled Query?


A Compiled Queries get stored as a Static Func<>.  A Func acts as a “pointer” to a method.  We make it static so it can be reused across the application.  That is how we get the performance gain.  Once the .Net framework figures out how to take the LINQ and turn it into a SQL statement, it gets saved for reuse in the Static variable.


A Func<> has a limited number of parameters that can be passed to it.  I like the technique of standardizing what gets passed in, passing the datacontext in the first parameter and a “helper class” containing all other parameters as the second.  And of course, last is the out parameter.


Here is our helper class:

private class GetOrdersInputs
{
public string CustomerName { get; set; }
public string ShipCountry { get; set; }
public int Take { get; set; }
public int Skip { get; set; }
}

Now let’s look at the Func<> declaration:

private static Func<NorthwindDataContext, GetOrdersInputs, IQueryable<Order>> GetOrdersQuery =
CompiledQuery.Compile((NorthwindDataContext db, GetOrdersInputs inputs) => LINQ GOES HERE );

You can see that the Compile method takes a lambda expression.  I thought it was easier to follow without the complete LINQ.  Here is the complete version:

private static Func<NorthwindDataContext, GetOrdersInputs, IQueryable<Order>> GetOrdersQuery =
CompiledQuery.Compile((NorthwindDataContext db, GetOrdersInputs inputs) =>
(from order in db.Orders
where order.Customer.CompanyName == inputs.CustomerName
&& order.ShipCountry == inputs.ShipCountry
select order).Skip(inputs.Skip).Take(inputs.Take));

Don’t worry if the Func<> seems confusing.  You’ll find it very easy to use over and over again, just replace the variables and the LINQ statement anytime you need it.


Now you just call GetOrdersQuery as follows:


public static List<Order> GetOrdersCompiled(string custName, string shipCountry, int skip, int take)
{


    // load up the helper class with parameters.
    GetOrdersInputs inputs = new GetOrdersInputs { CustomerName = custName, ShipCountry = shipCountry, Skip = skip, Take = take };

    // even though the query is compiled, you can still pass in parameters.
   
using (NorthwindDataContext db = new NorthwindDataContext())
    {


        // Execute the compiled query.
        List<Order> orders = GetOrdersQuery(db, inputs).ToList();
        return orders;
    }
}


Results:  The included demo is a little Console App that runs each query 27 times.  I’m using a free tool – CLR Profiler from Microsoft to analyze my application.


The compiled query executes about 257 thousand method calls (that is .net converting the LINQ into SQL).  But the non compiled version executes about 2.6 million calls because it does the work over and over again!


CLR Compiled


 


The Compiled Query will perform much better and it really wasn’t much work to implement.  So give it a try!


Again, here is the source code:  CompiledQuery.zip


Technorati Tags: ,

4 thoughts on “15 Minutes on LINQ Compiled Queries

  1. Thank you very much for sharing the information.

    The warm up of the Entity Framework takes forever. How could the code help to avoid 30 seconds of preloading the initialization asynchronously? Synchronously it freezes the UI. I also cached and used pre-compiled views.

    Reply
  2. Thanks Andy,
    I found a class with the same name in the ADO.NET entity framework.
    I feel this compiled query approach is cumbersome. Image your application has 100 linq queries, you have to create 100 helper classes, and another 100 static functions. I wish Microsoft provides an easily way, maybe some syntax sugar, or some more magics in DataContent, to make programmer life easier.

    Reply
  3. Q,
    I have not yet used the Entity Framework. However, my understanding is that it has the exact same functionality to do Compiled Queries exists. I’m not sure if it uses the same namespaces or not.

    -Andy

    Reply
  4. Andy,I will not be able to attend your presentation today, so please allow me to ask a couple of questions here. Is this compiled query a LINQ to SQL feature, since the class CompiledQuery is in System.Data.Linq.dll? Do you know if there is a counterpart in ADO.Net entity framework? Thanks.

    Reply

Leave a reply

required

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>