relax! it’s just 0s and 1s

so in my last post, I wrote about the Linq compiled queries syntax, before diving into its use. this post is about the usage of compiled queries.

every time, a Linq Query is executed (accessed), the expressions tree/Linq query is translated into its equivalent SQL/data source query and the results are fetched. for static data and data, changing on parameter values, the repeated translation is redundant.

Assume we have the following methods:

GetAllProductsAuthorizedToBeSoldInIndia()
{
 // Linq query to join Products and Country table on key for 
 // India and return the expected products.
}

now if we access this method N times, the LINQ query will have N SQL translations, for the same result that it will fetch. clearly this is a performance bottleneck.

also consider a parameterized method:

GetAllProductsAuthorizedToBeSoldInACountry(int countryId)
{
 // Linq query to join Products and Country table on 
 // CountryId and return the expected products.
}

Except for the parameter value, the generated SQL query has to be re-generated every time.

To solve this, we use Linq Compiled Queries. They facilitate one time translation of the Linq query, and re-use of the translated query across multiple calls.

public class MyCompiledQueries
{
  private static 
  Func<MyDatabaseContext, IQueryable<Product>> 
  ProductsAuthorizedToBeSoldInACountry 
  = CompiledQuery.Compile(
  (MyDatabaseContext db, int countryId) => 
  db.Products(p=>p.CountryId=countryId)); 
 
  public static List<Product>  
  GetAllProductsAuthorizedToBeSoldInACountry
  (MyDatabaseContext db, int countryId)
  {
    return MyCompiledQueries. ProductsAuthorizedToBeSoldInACountry(
    db, countryId).ToList<Product>();
  }
}

the queries are translated once, and the parameter values are substituted at runtime.

1. The CompileQuery.Compile method returns us a delegate, which can be invoked repeatedly.
2. The appropriate parameters need to be passed to the delegate during the invocation. (data context + query parameters)
3. Because we need to reuse the delegate to actually have any performance benefit, static variables/method wrappers are preferred.
4. It is a good practice to put all of your Compiled Queries into a separate class, at one place, and possibly have wrapper methods which make more business sense than entity names.
5. on my box, performance increased by 220%, when I changed my queries to Compiled Queries. I was working on a test domain data application using Linq. this was just slightly less than the data reader performance, we get.

the syntax for compiled queries makes it a little tough to maintain, but the benefits are sweet n totally worth the effort.