Introduction

After publishing Linq to SQL Performance Considerations, many readers replied and asked me to extend the benchmarking examples to include Linq to SQL compiled queries.

This article takes a look at what it takes to convert Linq queries to compiled queries and what performance gains can be obtained.

Here is a link to the previous article: Part 1.

I have also decided to run each of the data access scenarios, discussed in Part 1, of this article, through the Visual Studio Performance profiler using the Instrumentation profiling method.

The Instrumentation profiling methods goes beyond the Sampling profiling method which collects information only when the program actively uses the CPU.

The Instrumentation profiling method adds probes to the beginning and end of each function and is used to measure elapsed time.

Probes are also used to determine how much CPU time each function is using and how expensive external functional are. (Find Application Bottlenecks with Visual Studio Profiler, 2010.)

Please refer to the previous article as I plan on listing only the changes to the code that were originally listed.

Part 1 of this article compared benchmarking Linq to SQL against various data access methods which included ADO.NET accessing a SQL Server Stored Procedure, Linq to SQL accessing the same Stored Procedure and Linq to SQL accessing a SQL Server user defined function.

Compiled Queries

In order to create Linq to SQL compiled queries, there are several design patterns that can be used as a guide in the conversion process. This article will use a design pattern that creates static methods that call a delegate.

There are some really good articles on these design patterns and I will post a reference to these later in the reference section.

Let us look at one of the simple query expressions that were referenced in Part 1 of this article:

var shift1 =
	from sft1 in sq.Shifts
	where sft1.ShiftName == "Shift1"
	select new { sft1.StartTime };

This query expression was used to extract the start time of the first shift.

In order to convert this query into a compiled query, the query has to be converted into a method. In order to do so, we must parameterize several key pieces of data. (Solving Common Problems with Compiled Queries in Linq to SQL for High Demand ASP.NET web sites, 2008.)

IQueryable<string> GetShift1(SQLDataDataContext sq, string strShift)
{
    return from sft1 in sq.Shifts
        where sft1.ShiftName == strShift
        select sft1.StartTime;
}

In the above code, a generic method has been created that receives a data context and a string argument for the desired shift.

Next, the method must be converted to include a delegate and a Lambda expression. The Func<> delegate and Lambda expressions are two new constructs introduced in C# 3.0 to aid in the construction of Linq to SQL expressions.

public static readonly Func<sqldatadatacontext,string,IQueryable<string>>
    GetShift1 = (sq, strShift) =>
        from sft1 in sq.Shifts
        where sft1.ShiftName == strShift
        select sft1.StartTime;

Notice the above delegate is declared as static readonly. You want the compiled query to be declared once and re-used across all threads. If you do not include the static modifier, the compiled query will be re-compiled every time you reference it and you will lose any performance gains.

Now the final transformation can take place. The method can now be converted into a compiled query by adding the following syntax:

public static readonly Func<SQLDataDataContext, string, IQueryable<string>>
GetShift1 = CompiledQuery.Compile((SQLDataDataContext sq, 
	string strShift) => from sft1 in sq.Shifts
        where sft1.ShiftName == strShift
        select sft1.StartTime);

By adding the CompiledQuery.Compile() method to the code, the delegate is compiled upon execution, a single time, and no further compiling occurs during subsequent execution.

There are a few more issues that need to be addressed when creating compiled queries. These issues will be addressed when we discuss the other Linq to SQL queries that were defined in Part 1 of this article.

var shift2 =
     from sft2 in sq.Shifts
     where sft2.ShiftName == "Shift2"
     select new {sft2.StartTime, sft2.Hours };

In the above Linq to SQL query, the select new {sft2.StartTime, sft2.Hours} statement implies an anonymous type. No type name is provided. Compiled queries cannot contain anonymous types. Generics requires a type name to be specified.

There is a way around this:

public static readonly Func<SQLDataDataContext, string, IQueryable<Shift>>
     GetShift2 = CompiledQuery.Compile((SQLDataDataContext sq, string  strShift 
      => from sft2 in sq.Shifts
         where sft2.ShiftName == strShift
         select sft2 );

Notice the return type is IQueryable<shift>. One way to address the problem is to specify a type. In this case, the compiled query is referencing the Shift class, which was created by the Designer.

var icount =
   from insp in sq.Inspections
   where insp.TestTimeStamp > dStartTime && insp.TestTimeStamp < dEndTime
   && insp.Model == "EP"
   group insp by insp.Model into grp
   select new { Count = grp.Count() };

Notice that this query also contains an anonymous type. The below compiled query gets around this by referencing a known type of IQueryable<Int32>.

public static readonly Func<SQLDataDataContext, 
	string, DateTime, DateTime, IQueryable<Int32>>
    GetModelCnt = CompiledQuery.Compile((SQLDataDataContext sq, 
	string strModel, DateTime dStartTime, DateTime dEndTime) =>
        from insp in sq.Inspections
        where insp.TestTimeStamp > dStartTime && insp.TestTimeStamp < dEndTime
        && insp.Model == strModel
        group insp by insp.Model into grp
        select grp.Count());

We removed the code that references the anonymous type and used projection to query the group by Count() method.

var unordered =
    from insp in sq.Inspections
    where insp.TestTimeStamp > dStartTime && insp.TestTimeStamp < dEndTime
    && insp.Model == "EP" && insp.TestResults != "P"
    group insp by new { insp.TestResults, insp.FailStep } into grp

    select new
    {                
        FailedCount = (grp.Key.TestResults == "F" ? grp.Count() : 0),
        CancelCount = (grp.Key.TestResults == "C" ? grp.Count() : 0),
        grp.Key.TestResults,
        grp.Key.FailStep,
        PercentFailed = Convert.ToDecimal(1.0 * grp.Count() / tcount * 100)
    };

There are two issues in converting the above query to a compiled query. As above, this query contains an anonymous type. The second issue pertains to the number of types that must be passed to the method.

Apparently a compiled query is limited in the number of types that can be passed as arguments. (Solving Common Problems with Compiled Queries in Linq to SQL for High Demand ASP.NET web sites, 2008.)

The way around this is to use a struct or a class to package up the arguments and pass the object to the compiled query.

public struct testargs
{
    public int tcount;
    public string strModel;
    public string strTest;
    public DateTime dStartTime;
    public DateTime dEndTime;
}

public static readonly Func<SQLDataDataContext, testargs, 
	IQueryable<CalcFailedTestResult>>
    GetInspData = CompiledQuery.Compile((SQLDataDataContext sq, testargs targs) =>
        from insp in sq.Inspections
        where insp.TestTimeStamp > targs.dStartTime && 
	insp.TestTimeStamp < targs.dEndTime 
        && insp.Model == targs.strModel && insp.TestResults != targs.strTest
        group insp by new { insp.TestResults, insp.FailStep } into grp

        select new CalcFailedTestResult
        {
            FailedCount = (grp.Key.TestResults == "F" ? grp.Count() : 0),
            CancelCount = (grp.Key.TestResults == "C" ? grp.Count() : 0),
            TestResults = grp.Key.TestResults,
            FailStep = grp.Key.FailStep,
            PercentFailed = Convert.ToDecimal(1.0 * grp.Count() / targs.tcount * 100)
        });

The anonymous type issue was handled by providing a named type that was built by the designer. The struct was built and populated before calling the compiled query.

var fStepc =
    from selection in unorderedc
    orderby selection.FailedCount descending, selection.CancelCount descending

    select selection;

The last query is querying an IQueryable object that was created by the previous compile query. Since the query operation is being performed on an object and not a data context, we do not have to compile this query.

Calling Compiled Queries

Once the compiled queries are built, how do you invoke them? You invoke the compiled queries by simple method calls:

Here is the code rewritten to call the compiled queries.

Program p = new Program();
SQLDataDataContext sq = new SQLDataDataContext
	(p.GetConnectionString("Production_Monitoring"));
sq.ObjectTrackingEnabled = false;

// get Shift1 start time
var shift1c = GetShift1(sq, "Shift");

foreach (var sft in shift1c)
{
       s1StartTime = sft;
}

// Get Shift2 Hours
var shift2c = GetShift2(sq, "Shift2");

foreach (var sft in shift2c)
{
    s2StartTime = sft.StartTime;
    iHours = Convert.ToInt32(sft.Hours);
}

DateTime dStartTimec = Convert.ToDateTime(sDate + " " + s1StartTime);
DateTime dEndStartTimec = Convert.ToDateTime(sDate + " " + s2StartTime);
DateTime dEndTimec = dEndStartTimec.AddHours(iHours);

var icountc = GetModelCnt(sq, "EP", dStartTimec, dEndTimec);

foreach (var i in icountc)
{
    tcount = i;    
}

testargs targs = new testargs();
targs.strModel = "EP";
targs.strTest = "P";
targs.dStartTime = dStartTimec;
targs.dEndTime = dEndTimec;
targs.tcount = tcount;

var unorderedc = GetInspData(sq, targs);

var fStepc =
    from selection in unorderedc
    orderby selection.FailedCount descending, selection.CancelCount descending

    select selection;


stopwatch.Stop();
Console.WriteLine("Linq precompile with compiling time - 
	" + stopwatch.ElapsedMilliseconds);
stopwatch.Reset();

Benchmarks

As in Part 1 of this article, the data access scenarios are running in a Console application that was developed in C# using Visual Studio 2008. The data table being accessed, at the time of the benchmark had around 202,000 rows of data and the final query returned 14 rows of data. The version of SQL Server was 2008.

Benchmark time is in milliseconds, the application was executed 5 times to gain a better sample size.

Scenario	Time	Time	Time	Time	Time
Linq	239	203	162	161	172
DataLayer	90	90	90	90	90
SP	102	103	104	109	102
FN	91	92	92	92	92
Linq Compiled – 1^st Pass	84	84	85	85	85
Linq Compiled 2^nd Pass	49	49	49	48	49

Very amazing results, the compiled second pass query benchmark against the data layer query is 45.5 % faster. The second pass compiled query benchmark against the first pass query benchmark is 41.6 % percent faster. The second pass compiled query against the (average time) of the native Linq query is 73. 82 % faster.

Performance Profiling

All scenarios were run through the Visual Studio Performance profiler separately with the exception of the compiled queries. These queries were grouped together and run one right after the other to give a comparison of performance between the first pass query execution time and the second pass query execution time.

Because the compiled query only needs to be compiled once, the second pass query execution benchmark time will continue for further query executions.

The performance summaries by themselves do not provide much insight. But some very interesting results will appear when comparing the Summaries against the various data access scenarios.

When making comparisons, pay attention to the Number of calls and the Time for each of the sections within the Performance Summary.

Native Linq Query

Notice the decreased number of calls; this is attributed to the application offloading much of the querying to the database. The execution time also displays that the stored procedure is executing more efficiently than the native Linq query.

Data Layer Query

Notice that the number of calls is the same as the data layer query. But there is a slight increase in execution time. Linq still has some overhead over the data layer calling the stored procedure.

Linq Stored Procedure

The results are very similar between the Linq stored procedure query and the Linq user defined function query. Benchmark times are around 10 milliseconds apart.

Linq User-Defined Function

Linq Compiled

Remember this summary includes both the first pass compiled query and second pass compiled query performance comparisons. In other words, there are two queries running in this summary against the other summaries, which were only running one query.

As indicated above, the compiled second pass query is 41.6 & faster than the compiled first pass query. The Performance Summary indicates that the compiled queries are indeed taking up less number of calls and are executing more efficiently.

Conclusion

The above article went into some basic detail on what is involved in converting Linq to SQL queries to compiled queries. The article did cover one of the more common design patterns in this conversion and tried to bring to light some of the areas where modifications were needed.

In the benchmarking section, it also became very apparent of the benefits of utilizing compiled queries.

Reference

History

29^th April, 2010: Initial post

Linq to SQL Performance Considerations - Part 2