A fine site

How to improve your LINQ query performance by 5 X times?

Leave a comment

Introduction and goal

LINQ has been criticized by many early adopters for its performance issues. Well, if you are just going to drag and drop using a DBML code generator, I am sure you will land up in a mess. Try doing this, make a simple LINQ to SQL project using DBML and see your SQL profiler. I am sure you will never like to touch a DBML code generator again.

In this article, we will first look into how LINQ queries are executed, and then we will touch base on how compiled LINQ queries can help us improve our application performance at least 5 times. My numbers can be 10% up and down as I had come to that figure using my environmental situations.

Still new to LINQ? Below are some real quick starters

This article requires some pre-requisites, in case you are new to LINQ I would suggest you to go through the below links.

Deep dive into how LINQ queries work

Before we get into how we can improve LINQ query performance, let’s first try to understand the various steps involved in a LINQ query execution. All LINQ queries are first converted to SQL statements. This conversion also involves checking of LINQ query syntaxes and translating the queries to SQL.

Below is a simple LINQ query which selects data from a customer table. This LINQ query is then transformed into the necessary SQL statements by the LINQ engine.

The checking of syntaxes and generating the SQL query accordingly is a bit of a tedious job. This task is performed every time we fire a LINQ query. So if we can cache the LINQ query plan, we can execute much faster.

LINQ has provided something called as compiled LINQ queries. In compiled LINQ queries, the plan is cached in a static class. As we all know, a static class is a global cache. So LINQ uses the query plan from the static class object rather than building and preparing the query plan from scratch.

Figure: LINQ query caching

In all, there are four steps which need to be performed right from the time LINQ queries are built till they are fired. By using compiled LINQ queries, the four steps are reduced to two.

Figure: Query plan bypasses many steps

Steps involved in writing compiled LINQ queries

The first thing to do is to import the Data.Linq namespace.

 Collapse | Copy Code
Import namespace using System.Data.Linq;

The syntax to write compiled queries is a bit cryptic and many developers do not like the way the syntax is. So let us break the syntax into small pieces and then we will try to see how the complete syntax looks like. To execute a compiled function, we need to write a function to pointer. This function should be static so that the LINQ engine can use the query plan stored in the static class objects. Below is how we define the function. It starts with public static stating that this function is static. Then we use the Func keyword to define the input parameters and output parameters. Below is how the parameter sequence needs to be defined:

  • The first parameter should be a data context. So we have defined the data type as DataContext.
  • Followed by one or many input parameters. Currently we have only one, i.e., customer code, so we have defined the second parameter data type as string.
  • Once we are done with all input parameters, we need to define the data type of the output. Currently we have defined the output data type as IQueryable.

We have named this delegate function getCustomers.

 Collapse | Copy Code
public static Func<DataContext, string, IQueryable<clsCustomerEntity>> getCustomers

We need to call the method Compiled of the static class CompiledQuery with the DataContext object and necessarily define input parameters followed by the LINQ query. For the below snippet, we have not specified the LINQ query to minimize complications.

 Collapse | Copy Code
CompiledQuery.Compile((DataContext db, string strCustCode)=> Your LINQ Query );

Uniting the above two code snippets, below is how the complete code snippet looks like:

 Collapse | Copy Code
public static Func<DataContext, string, IQueryable<clsCustomerEntity>>
getCustomers= CompiledQuery.Compile((DataContext db, string strCustCode)=> Your LINQ Query );

We then need to wrap this static function in a static class. We have taken the above defined function and wrapped that function in a static class clsCompiledQuery.

 Collapse | Copy Code
public static class clsCompiledQuery
    public static Func<DataContext, string, IQueryable<clsCustomerEntity>>
    getCustomers = CompiledQuery.Compile((DataContext db, string strCustCode)
        => from objCustomer in db.GetTable<clsCustomerEntity>()
        where objCustomer.CustomerCode == strCustCode
        select objCustomer);

Consuming the compiled query is pretty simple; we just call the static function. Currently, this function returns the data type IEnumerable. We have to define an IEnumerable customer entity which will be flourished through thegetCustomers delegate function. We can loop through the customer entity using the clsCustomerEntity class.

 Collapse | Copy Code
IQueryable<clsCustomerEntity> objCustomers = 
    clsCompiledQuery.getCustomers(objContext, txtCustomerCode.Text);
foreach (clsCustomerEntity objCustomer in objCustomers)
    Response.Write(objCustomer.CustomerName + "<br>");

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s