Performance improvement for Entity Framework in ASP.NET using CompiledQuery

When we write any LINQ query on Entity framework and run this query; Converting this LINQ query to SQL statements takes long time this includes checking LINQ syntax and then translating it to the SQL statement.

Let us consider we have an web  application in ASP.NET and  we have used Entity Framework  to access the database and we have following method in it.

This method returns all customers for a given city. Let us consider that this method is called from the page having combo box to select city and having grid to display all customers of selected city. Each time user changes the selection of the city this method is called to get all customers of the selected city. So When each time method is called LINQ query is parsed and translated to SQL again and again even though only difference between the two query execution is only the parameter "city". So there is an extra performance overhead of parsing and translating to SQL 

To improve the performance .NET 3.5 introduces new class called as CompiledQuery which provide facility to compile and cache the LINQ query to reuse. CompiledQuery class provides method Compile.

When user calls compile method; it compiles query and creates delegate and return it. We can use this delegate to execute the query again may be using different parameter.

So we can modify the above example as follows.

Here we have declared one delegate getCstomer, We check that if it is null; if it is null it means this is first time and we call CompiledQuery.Compile method. This method parse the LINQ query and cache it.

Next time as getCustomer is not null it will directly invoke the query without parsing and translating overhead.

Note that static Func in above figure ; we have defined this delegate is static so that it will be available to us in several post back of the page irrespective of session. So once query is parsed and translated and saved in delegate we can use it further without parsing and translating overhead. Also note that lock statement around compile method as compile method is not thread safe; For lock tmpObject is used. It is initialized in static constructor.

How much we have optimized?

Normal code

With the normal code it takes around 13 ms to 20 ms each time city London is selected.

Code with CompiledQuery

With new updated code with CompiledQuery it takes 20 ms for the first time and then it takes 2 ms to 3 ms when city London is selected.

Source code

You can find the source code here.

Next Enhancement

In EF5 provides performance improvement of automatic compilationof LINQ to Entities queries using  CompiledQuery