Speed up Linq to Sql with compiled queries


There is no doubt that tehre are some drawbacks to Linq to Sql. One of them is that the Sql statement is built dynamically so it is needed to be parsed and compiled each time you run it. Fortunately .Net 3.5 has a solution for this problem. System.Data.Linq namespace includes a class named CompiledQuery which is responsible for caching the compiled version of a Linq to Sql query. This class has a static method called Compile which takes a Func<T,S,R> delegate. In this signature, T is the type of a DataContext (i.e. HRMDataContext) , S is the type of a predicate to filter the query and R is the type of returned result. Needless to say that it must be IQueryable<T>.

In this article we will see how to pre-compile a query, its limitations and how it really improves the speed of a Linq query.

To pre-compile a query we must define a public static field of type Func<T,S,R> . What we assign to this field is the result of CompiledQuery.Compile method:

public static Func<testDataContext , SearchCriteria, IQueryable<Person>> FilteredResult …

In the above line, testDataContex is the type of a DataContext inside the project, SearchCriteria is type of a class or struct that is designed for passing search criteria to .Compile method. For example, suppose that in testDataContext, we have a Table named Person. We have also defined a class (or struct) named SearchCriteria as bellow:

public class SearchCriteria
{
public int id { set; get; }
public string FirstName { set; get; }
public string LastName { set; get; }
}

Now to get these definitions to work with a precompiled query we can write such a statement:

public static Func<testDataContext , SearchCriteria, IQueryable<Person>> FilteredResult =
System.Data.Linq.CompiledQuery.Compile(
(testDataContext dc , SearchCriteria criteria ) =>
from p in dc.Persons
where (p.id == criteria.id || criteria.id == -1)
&& (p.FirstName == criteria.FirstName || criteria.FirstName == string.Empty)
&& (p.LastName == criteria.LastName || criteria.LastName == string.Empty)
select p
);

That’s it. At this point, FilteredResult contains a pre-compiled query and can be used this way:

testDataContext dc = new testDataContext();
SearchCriteria criteria = new SearchCriteria();
criteria.id = -1;
criteria.FirstName = “Bill”;
criteria.LastName = “Gates”;
List<Person> p = FilteredResult(dc, criteria).ToList();

The above code creates instances of testDataContext (dc) and SearchCriteria (criteria) and passes them to FilteredResult as arguments. The result of FilteredResult is IQueryable<Person> we have called .ToList() extension method to get a List<Person> series.

One upsetting point about pre-compiled queries is that you can not use a stored-procedure to make a compiled query. In the above Linq to Sql code, if you write “from C in usp_GetPerson() …” you will get an error indicating that stored procedures are not allowed to be used.

Now let’s see how much precompilation can be helpful. I have written a small Console application that runs two version (one is compiled and one is not) of a query over a database for 1000 times. The time needed to run each query is as follows:

Compiled query takes 0 minutes, 1 seconds and 62 milliseconds.

Regular query takes 0 minutes, 13 seconds and 328 milliseconds.

As it is clear, the compiled query is greatly faster than a regular query. Notice that in a Linq model, nothing will really happen unless we iterate over the result of the query. Therefore, I have written a foreach statement to iterate over the result of queries. I also have written a small query at the beginning of the program to make Linq manager open a connection to Sql Server. If we do not do this, the compiled query will surprisingly takes longer!

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;

namespace CompiledQuery
{
class Program
{
public static Func<testDataContext , SearchCriteria, IQueryable<Person>> FilteredResult =
System.Data.Linq.CompiledQuery.Compile(
(testDataContext dc , SearchCriteria criteria ) =>
from p in dc.Persons
where (p.id == criteria.id || criteria.id == -1)
&& (p.FirstName == criteria.FirstName || criteria.FirstName == string.Empty)
&& (p.LastName == criteria.LastName || criteria.LastName == string.Empty)
select p
);

static void Main(string[] args)
{
testDataContext dc = new testDataContext();
SearchCriteria criteria = new SearchCriteria();
IQueryable<Person> Q = null;

// The following code makes Linq manager to open a connection to Sql Server
var init = from p in dc.Persons select p;
foreach (Person person in init) ;

criteria.id = -1;
criteria.FirstName = “Bill”;
criteria.LastName = “Gates”;
DateTime BeginTime = DateTime.Now;

for (int i = 0; i < 1000; i++)
{
Q = FilteredResult(dc, criteria);
foreach (Person person in Q) ;
}

DateTime EndTime = DateTime.Now;
TimeSpan Diff1 = EndTime – BeginTime;

BeginTime = DateTime.Now;

for (int i = 0; i < 1000; i++)
{
Q = from p in dc.Persons
where (p.id == criteria.id || criteria.id == -1)
&& (p.FirstName == criteria.FirstName || criteria.FirstName == string.Empty)
&& (p.LastName == criteria.LastName || criteria.LastName == string.Empty)
select p;
foreach (Person person in Q) ;
}

EndTime = DateTime.Now;
TimeSpan Diff2 = EndTime – BeginTime;

Console.WriteLine(“Compiled query takes : {0}:{1}:{2}”, Diff1.Minutes, Diff1.Seconds, Diff1.Milliseconds);
Console.WriteLine(“Regular query takes {0}:{1}:{2}”, Diff2.Minutes, Diff2.Seconds, Diff2.Milliseconds);

Console.ReadKey();
}
}
}

You can download the source of of a full sample project from here.

About these ads

5 thoughts on “Speed up Linq to Sql with compiled queries

  1. Rikki says:

    This post contains the following statement:

    “To pre-compile a query we must define a public static field of type Func .”

    This repeats a common error that pre-compiled queries have to be declared “static”. This is not true. Declaring FilteredResult “static” just means that it will be created (and the query compiled by CompiledQueries.Compile) by the class’s static constructor the first time the class is referenced and it will live until the AppDomain dies. If you leave out the “static” then FilteredResult is created (and the query compiled by CompiledQueries.Compile) by the class’s object constructor when an object is created, and it will live until the object is disposed.

    Whether or not you declare your pre-compiled queries “static” or not depends on when you want them created, how long you want them to live, and how you plan to call them.

    Hi,
    The aim of pre-compiling a query is to avoid repetitive compilation. If you use a non-static method, since the object is created in each round trip, the query is compiled again steadily. Therefore, only a static method makes sense because as you mentioned, it lasts as long as the app. domain is alive.
    Thanks for the comment anyway.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s