ADO.NET Entity Framework 4.0: Making it fast through Compiled Query

  • Article

If we are using similar query frequently, we can increase it’s performance by Compiling it through CompiledQuery. It’s always recommended to use CompiledQuery if you happen to see the query is getting executed many times.

Let’s take an example, in Northwind database if you are getting Customer based on City. You may follow the below approach. Also a very important point to observe is to see how it runs faster in subsequent calls even though the parameter values differ.

Compiled Query

  1. private static readonly Func<NorthwindEntities, string, IQueryable<Customer>> myCompiledQuery =
  2.     CompiledQuery.Compile<NorthwindEntities, string, IQueryable<Customer>>(
  3.     (ctx, city) => from c in ctx.Customers
  4.                     where c.City == city
  5.                     select c);

Now, instead of writing raw LINQ use this Compiled Query

Calling it

  1. private static void CallCompiledQuery(string myCity)
  2. {
  3.     using (NorthwindEntities ctx = new NorthwindEntities())
  4.     {
  5.         var q = myCompiledQuery(ctx, myCity);
  6.  
  7.         foreach (var k in q)
  8.         {
  9.             Console.WriteLine(k.CompanyName);
  10.         }
  11.     }
  12. }

We also wanted to capture the time.

  1. static void CallIt(string sCity)
  2. {
  3.     Stopwatch sw = new Stopwatch();
  4.     sw.Start();
  5.  
  6.     CallCompiledQuery(sCity);
  7.  
  8.     Console.WriteLine("+++++++++++++++++++++");
  9.     Console.WriteLine("Elapsed Time in Milliseconds : {0}", sw.ElapsedMilliseconds);
  10.     sw.Stop();
  11. }

Just call twice to capture the time,

  1. static void Main(string[] args)
  2. {
  3.     Console.WriteLine();
  4.     Console.WriteLine("++++++++++++++++");
  5.     Console.WriteLine("Call No. 1");
  6.     Console.WriteLine("++++++++++++++++");
  7.     CallIt("London");
  8.     Thread.Sleep(2000);
  9.     Console.WriteLine();
  10.  
  11.     //
  12.     Console.WriteLine();
  13.     Console.WriteLine("++++++++++++++++");
  14.     Console.WriteLine("Call No. 2");
  15.     Console.WriteLine("++++++++++++++++");
  16.     CallIt("México D.F.");
  17.     Thread.Sleep(2000);
  18.     Console.WriteLine();
  19. }

In my machine the first call took 322 milliseconds and the second one took 4 milliseconds. This may differ time to time and machine to machine. But this is faster indeed.

Namoskar!!!