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.
——————————————————————————————————————————————
In the earlier posts on LINQ to SQL performance optimization, I discussed about the LINQ Best Practices and LINQ DataLoadOptions. This discussion will remain incomplete if I don’t included CompiledQuery.Compile.
While developing the application we often come across scenario where we are required to execute a similar nature of query more than couple of times.
When we execute a query using LINQ there are number of steps involved before final SQL query is generated, and each time we call the method to fetch the data whole process starting from query generation to its execution is carried out. In this life cycle the overhead that is easily visible is that we are every time generating the query.
LINQ’s CompiledQuery.Compile is used to avoid this extra overhead. If you have a application executing structurally similar query, CompiledQuery.Compile is one thing that can help you increase the performance of your application.
CompiledQuery.Compile compiles the query once and execute it for the rest of the time with different set of parameters.
// Method in which whole process of query generation and execution take place everytime
private static void CompiledQueryMethod(string orderID)
{
SampleDataContextDataContext db = new SampleDataContextDataContext();
var result = from c in db.Customers
join o in db.Orders on c.CustomerID equals o.CustomerID
where o.OrderID == orderID
select new { c };
}
In this case whole process of query generation to execution is carried out each time CompiledQueryMethod is called.
Now lets do the same using CompiledQuery.Compile way.
// This will compile the query and return a delegate that can be used to execute for diffferent
// set of parameters
public static Func<SampleDataContextDataContext, int, IQueryable<Customer>>
GenerateCompiledQuery = CompiledQuery.Compile((SampleDataContextDataContext db, int orderID) =>
from c in db.Customers
join o in db.Orders on c.CustomerID equals o.CustomerID
where o.OrderID == orderID
select c);
The above method returns a delegate that can be cached and used several number of time just by changing the input parameter.
private static void CompiledQueryMethod(int orderID)
{
SampleDataContextDataContext db = new SampleDataContextDataContext();
var result = GenerateCompiledQuery(db,orderID);
}
