LINQ Exchange

One of the drawbacks of LINQ to SQL is that the SQL statement is built dynamically so it needs to be parsed and compiled each time you run it. The System.Data.LINQ namespace provides a solution in a class named CompiledQuery. This class is responsible for caching the compiled version of a LINQ to SQL query. CompiledQuery 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, which must be IQueryable<T>.

The CompiledQuery class allows us to pre-compile a query. This pre-compilation provides a marked increase in execution speed.To pre-compile a query we must define a public static field of type Func<T,S,R>

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; }
}

 

Example Using a LINQ to SQL Compiled Query

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 catch to using pre-compiled queries is that you cannot 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.

Below is 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.

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 forced the queries to execute by calling ToList() on each query. I also have written a small query at the beginning of the program to make LINQ manager open a connection to SQL Server. A connection must be open before the CompiledQuery executes, or an error is raised.

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
            // by calling a stored procedure
            dc.sproc_DoNothing_Procedure().ToList();

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

            // test Compiled Query
            for (int i = 0; i < 1000; i++)
                FilteredResult(dc, criteria).ToList();

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

            BeginTime = DateTime.Now;

            // Test non-compiled querey
            for (int i = 0; i < 1000; i++)
            {
                (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).ToList();
            }

            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();
        }
    }

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

 

How to Speed Up LINQ to Sql With Compiled Queries, 5.0 out of 5 based on 1 rating

No related posts.

Related posts brought to you by Yet Another Related Posts Plugin.