Click here to Skip to main content
15,867,453 members
Articles / Web Development / ASP.NET

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

Rate me:
Please Sign up or sign in to vote.
4.65/5 (62 votes)
16 Jul 2009CPOL7 min read 490.5K   3.6K   190   58
Tips to improve your LINQ query performance.

Table of contents

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.

Image 1

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.

Image 2

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.

Image 3

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.

Image 4

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.

C#
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.

C#
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.

C#
CompiledQuery.Compile((DataContext db, string strCustCode)=> Your LINQ Query );

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

C#
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.

C#
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 the getCustomers delegate function. We can loop through the customer entity using the clsCustomerEntity class.

C#
IQueryable<clsCustomerEntity> objCustomers = 
    clsCompiledQuery.getCustomers(objContext, txtCustomerCode.Text);
foreach (clsCustomerEntity objCustomer in objCustomers)
{
    Response.Write(objCustomer.CustomerName + "<br>");
}

Performance comparison

Out of curiosity, we thought to do some kind of comparison to see how much the performance difference is. We took a simple customer table with 3000 records in it and we ran a simple query on the customer code. We have attached the sample source with the article. Below is a simple screenshot:

Image 5

What we have done in this project is we have executed a LINQ SQL without query compilation and with query compilation. We have recorded the time using the System.Diagnostic.StopWatch class. Here’s how the performance recording has taken place. We start the stop watch, run the LINQ SQL without compile, and then we stop the watch and record the timings. The same way we have recorded the performance LINQ query with compilation.

Image 6

We create the data context object and start the stop watch.

C#
System.Diagnostics.Stopwatch objStopWatch = new System.Diagnostics.Stopwatch(); 
DataContext objContext = new DataContext(strConnectionString);
objStopWatch.Start();

We run the LINQ query without compilation. After execution, stop the watch and record the time differences.

C#
var MyQuery = from objCustomer in objContext.GetTable<clsCustomerEntity>()
where objCustomer.CustomerCode == txtCustomerCode.Text
select objCustomer;
foreach (clsCustomerEntity objCustomer in MyQuery)
{
    Response.Write(objCustomer.CustomerName + "<br>");
}
objStopWatch.Stop();
Response.Write("The time taken to execute query without compilation is : " + 
objStopWatch.ElapsedMilliseconds.ToString() + " MillionSeconds<br>");
objStopWatch.Reset();

Now we start the stop watch again, run the LINQ query with compilation, and record the time taken.

C#
objStopWatch.Start();
IQueryable<clsCustomerEntity> objCustomers = 
    clsCompiledQuery.getCustomers(objContext, txtCustomerCode.Text);
foreach (clsCustomerEntity objCustomer in objCustomers)
{
    Response.Write(objCustomer.CustomerName + "<br>");
}
objStopWatch.Stop();
Response.Write("The time taken to execute query with compilation is : " + 
   objStopWatch.ElapsedMilliseconds.ToString() + " MillionSeconds");

Analyzing the results

When we measure performance, we need to see the time of execution the first time as well as subsequent times. At least 8 recordings are needed so that any kind of .NET runtime performance is averaged out. There are two important points we can conclude from the experiment:

  • We need to excuse the first reading as there can be a lot of .NET Dramework object initialization. It can lead to a lot of wrong conclusions as there is a lot of noise associated with the first run.
  • The subsequent readings have the real meat difference. The average difference between them is 5 times. In other words, a LINQ query executed using no compilation is 5 ms slower than compiled LINQ queries.
  No Compilation Milliseconds Query Compilation
First time 4 124
Second time 9 2
Third time 7 2
Fourth time 7 1
Fifth time 6 2
Sixth time 7 2
Seventh time 6 2
Eight time 6 2

Below is a graphical representation and you can see how compiled queries have better performance than non-compiled ones.

Image 7

Hardware and software configuration used for test conduction

  • The web application and database application where on different boxes.
  • Web application was running on Windows XP using a simple personal web server provided by VS 2008 (sorry for that guys, but did not have other options at that moment). Web application PC hardware configuration was 2 GB RAM, P4, 80 GB hard disk.
  • Database was SQL Server 2005 on a Windows 2003 Server with 2 GB RAM, P4, 80 GB hard disk.

For further reading do watch the below interview preparation videos and step by step video series.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect https://www.questpond.com
India India

Comments and Discussions

 
GeneralMy vote of 2 Pin
tbayart20-Jul-17 7:51
professionaltbayart20-Jul-17 7:51 
QuestionOutput type in Func when query returns Anonymous type Pin
chint.994-Apr-16 2:33
chint.994-Apr-16 2:33 
QuestionNot working for Entity frame work Pin
Balaji 3520-Sep-15 21:40
professionalBalaji 3520-Sep-15 21:40 
QuestionWhat if we don't store them in static class? Pin
Member 1179138730-Jul-15 22:45
Member 1179138730-Jul-15 22:45 
QuestionEF6-VS2013 DataContext Pin
NikitaS117-Jun-15 20:40
NikitaS117-Jun-15 20:40 
QuestionWhat if DataBaseContext re initialize Pin
Member 114374479-Apr-15 0:20
Member 114374479-Apr-15 0:20 
General5 Pin
Omar Gameel Salem26-Feb-14 22:24
professionalOmar Gameel Salem26-Feb-14 22:24 
GeneralMy vote of 4 Pin
Guillaume Ranslant29-Nov-11 2:12
Guillaume Ranslant29-Nov-11 2:12 
QuestionGeneric method for compiled Linq ? Pin
YawerIqbal3-Jul-11 23:36
YawerIqbal3-Jul-11 23:36 
hi Shivprasad,

You have infect provided nice guidline on imporving performance using Linq to SQL.

Here is a question please. In the way you have mentioned one will need to write a seprate funtion for each Linq to DB call where input parameters are different. Can there be a generic mehtoed that can be used for all Linq calls and also returns complied Linq ?

thanks
QuestionWhy CompiledQuery fast? Pin
devvvy17-Mar-11 22:39
devvvy17-Mar-11 22:39 
GeneralVery good and neat article! Pin
Virat Kothari27-Jan-11 17:20
Virat Kothari27-Jan-11 17:20 
GeneralMy vote of 5 Pin
SohelElite7-Jan-11 23:25
SohelElite7-Jan-11 23:25 
GeneralMy vote of 5 Pin
Runa Jack6-Oct-10 18:20
Runa Jack6-Oct-10 18:20 
Generalawesome Pin
Pranay Rana25-May-10 23:00
professionalPranay Rana25-May-10 23:00 
QuestionWhat happened if i used Stored procedures with Linq !? Pin
the black angel28-Feb-10 22:47
the black angel28-Feb-10 22:47 
AnswerRe: What happened if i used Stored procedures with Linq !? Pin
Shivprasad koirala1-Mar-10 4:32
Shivprasad koirala1-Mar-10 4:32 
GeneralRe: What happened if i used Stored procedures with Linq !? Pin
neoandrew27-Apr-10 5:15
neoandrew27-Apr-10 5:15 
GeneralDrop LINQ! Pin
are_all_nicks_taken_or_what21-Sep-09 5:21
are_all_nicks_taken_or_what21-Sep-09 5:21 
GeneralRe: Drop LINQ! Pin
emission1-Nov-09 9:17
emission1-Nov-09 9:17 
GeneralRe: Drop LINQ! Pin
Guillaume Ranslant29-Nov-11 2:17
Guillaume Ranslant29-Nov-11 2:17 
GeneralRe: Drop LINQ! Pin
johannesnestler8-Mar-12 3:34
johannesnestler8-Mar-12 3:34 
GeneralRe: Drop LINQ! Pin
HaBiX17-May-12 20:04
HaBiX17-May-12 20:04 
GeneralRe: Drop LINQ! Pin
Andy Missico25-May-12 9:41
Andy Missico25-May-12 9:41 
GeneralLinq project is dropped by Microsoft, this article has no relevance ignore this article, dont give vote to this article Pin
Mogamboo_Khush_Hua19-Aug-09 19:39
Mogamboo_Khush_Hua19-Aug-09 19:39 
GeneralRe: Linq project is dropped by Microsoft Pin
Shivprasad koirala19-Aug-09 22:06
Shivprasad koirala19-Aug-09 22:06 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.