Nikolaos Kantzelis ASP.Net Blog

This blog will focus on ASP.NET Framework

Sponsors

About Me

Great Blogs

Enhancing performance in Entity Framework applications by precompiling LINQ to Entities queries

This is going to be the tenth post of a series of posts regarding ASP.Net and the Entity Framework and how we can use Entity Framework to access our datastore. You can find the first one here, the second one here , the third one here , the fourth one here, the fifth one here ,the sixth one here ,the seventh one here ,the eighth one here and the ninth one here.

I have a post regarding ASP.Net and EntityDataSource. You can read it here.I have 3 more posts on Profiling Entity Framework applications. You can have a look at them here, here and here.

In this post I will be looking into how to improve performance in EF applications. I will be looking into how to precompile LINQ to Entities queries and see the performance gains from that.

We have to know our application very well and see what queries are executed repeatedly. Those are candidates for precompilation. We will use the public sealed class CompiledQuery.

For more information click here.

I assume that you have access to a version of SQL Server.If you do not, you can download and install the free SQL Server Express edition from here. 

In this post (step 9) you will find a T-SQL script that will create the database objects of the CompanyEmployees database.Before that you execute the T-SQL script you must create in the Query window the CompanyEmployees database yourself. You can download the companiesemployeesinsertt.zip to insert data into the tables.

1) Launch Visual Studio 2010 (express edition will work fine). Create an empty ASP.Net web site from the available templates and choose a suitable name for it. Choose C# as the development language.

2) Add a new item to your website,a web form.Leave the default name. Add a button and a textbox on the the form.Leave the default names.

3) Add a new project to your solution, a class library project.Remove the class1.cs file from the project. 

4) Add a new ADO.Net Entity Data model to the class library project. Choose a suitable name for it, e.g CompanyEmployees.edmx.

5) Then the Wizard pops up. Choose "Generate from Database" option and finish the steps of the wizard by selecting only the tables (Companies,Employees) to be included in the model. Now you have your model ready with all the entities and the generated code.

6) My application is very simple. I will query the Employees entity asking for the Employee first name and last name by passing the country as a parameter.I will enter the country in the textbox and then click the button to filter the employees by that parameter. The parameter will be passed at runtime.

We will use the Compile() method of the CompiledQuery class. Inside the class declaration of the Default.aspx page, type

public partial class _Default : System.Web.UI.Page
{

private static Func<CompanyEmployeesEntitiesstringIQueryable<Employee>> 
theCompiledQuery =
CompiledQuery.Compile<CompanyEmployeesEntitiesstring
IQueryable<Employee>>((context, country) => from emp in context.Employees
                              where emp.Country == country
                              select emp);

 

As you see here I create a delegate.The Compile() method take 2 arguments. I pass as the first argument the instance of the ObjectContext object.Then I pass as a second argument a "string" because I filter on a string.

Then I have a Lambda expression which encapsulates a Linq to Entities query.The query just returns entities of type (Employee) according to the parameter it takes.

 7) Now we need to instantiate the ObjectContext object. In the Button1_Click() event handling routine type,

 


        try
        {
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();


            using (var context = new CompanyEmployeesEntities())
            {

                string mycountry = TextBox1.Text;

                var query = theCompiledQuery(context, mycountry);

                foreach (var item in query)
                {
                Response.Write(item.EmpFirstName + " " + item.EmpLastName);
                Response.Write("<br/>");
                }

 
Response.Write("<br/>");
            Response.Write("<hr/>");
Response.Write("Time in milliseconds : " +   stopwatch.ElapsedMilliseconds);               
                stopwatch.Stop();
            }

        }
        catch (Exception ex)
        {

            Response.Write(ex.InnerException.Message);
        }

We need somehow to find the time ellapsed between consecutive requests.

Obviously the first request will not have any performance gains. Some time will be spent to compile the query.

I use the Stopwatch class.Add the following using statement in the Default.aspx.cs

 
using System.Diagnostics;

Then I just create an instance of that class and I start the time.I instantiate the context object and i get the parameter (country) from the textbox. Then I pass them to the theCompiledQuery delegate.

var query = theCompiledQuery(context, mycountry);

Then I iterate through the results and display them on the screen. Finally I find the elapsed time, and stop the watch.

8) Run your application. In my case , I enter in the textbox as a parameter-country="Norway" and I hit the button. This is what I get back

Julie Moreno
Mark Bolton


Time in milliseconds : 4452

 

9) Type another value in the textbox e.g Sweden . This is what I get.

Sonya Ryan
Luke Torres
Erin Schmidt
Nakia Zavala
Cassie Dougherty


Time in milliseconds : 24

As you can see there is a significant performance gain even though I have a different value in the paremeter.

10)  Type another value in the textbox e.g Bulgaria . This is what I get.

Frances Caldwell
Dana Heath
Ericka Rivers
Quentin Harmon
Sandra Ware
Bethany Kent


Time in milliseconds : 13

As you can see there is a significant performance gain even though I have a different value in the paremeter.

Email me if you need the source code.

Hope it helps!!!

Comments

Alan said:

this is an excellent post!!! It really helped me to understand this

# March 21, 2011 2:42 PM

Mike L said:

Very nicely done.  I can definitely see the perf improvements by comparing against the same uncompiled query.

# April 19, 2011 1:57 PM

kurumsal site tasarimi said:

Very good I would recommend to anyone sharing Thanks for sharing.

# July 3, 2011 12:32 PM

Frank said:

this I very informative, thanks

# November 17, 2011 11:24 AM

Zimmer said:

Hello to all, it's in fact a nice for me to go to see this website, it includes precious Information.

# March 6, 2013 11:45 PM

kurumsal site tasarimi said:

Good post admin, thank you

# May 16, 2013 4:17 AM