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!
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