High Performance LINQ To SQL - Compiled Queries - O/R Mappers

High Performance LINQ To SQL - Compiled Queries - O/R Mappers - Ecommerce Websites

by David Hayden, Florida ASP.NET Developer, Filed: LINQ To SQL

It should be of no surprise that an O/R Mapper will add overhead to performance as compared to the performance one would receive from a DataReader in ADO.NET. LINQ To SQL is no different. It adds a lot of services on top of ADO.NET, such as:

Connection Management
Query Translation and Execution
Object Identity
Change Tracking

This, of course, all comes at a little bit of a performance overhead. For most applications and user stories the slower performance is irrelevant. The value of the O/R Mapping services greatly outweigh any degradation in performance.

Sometimes, however, there may be a couple of database activities that are very expensive and we may want to achieve as optimal performance as possible even at the expense of maintainability. LINQ To SQL has a solution for these times - Compiled Queries.

LINQ To SQL Compiled Queries

Let's pretend we have an e-commerce website and getting Products By Category is an expensive operation. We have optimized the database as much as we can using proper indexing and covering indexes and it just isn't enough. We are using LINQ To SQL and we decide it is time for Compiled Queries. LINQ To SQL Compiled Queries allow the translation of the query to happen once at compile time and the query to be consumed many times. The lack of translation at each call will greatly improve performance for our e-commerce store.

Using Northwind, I create a partial class of the NorthwindDataContext and add my compiled query:

public partial class NorthwindDataContext
{
    public static Func<NorthwindDataContext, int, IQueryable<Product>>
        ProductsByCategoryID = CompiledQuery.Compile
            ((NorthwindDataContext context, int categoryID) =>
            context.Products.Where(p => p.CategoryID == categoryID));

    public IQueryable<Product> GetProductsByCategoryIDFast(int
                                                        categoryID)
    {
        return ProductsByCategoryID(this, categoryID);
    }
}

Everything above is compiled except for the category I am searching on. Note we are using Expression Trees, which are new to C# 3.0. This will greatly improve the performance of getting the products by category in my e-commerce store.

Now in my e-commerce website I will use the higher performance method of accessing the products in my product catalog by category:

NorthwindDataContext db = new NorthwindDataContext();

List<Product> products = db.GetProductsByCategoryIDFast(1).ToList();

Here I am getting all the products in my product catalog in the CategoryID = 1. The improvement in performance is much better and very close to the performance of the speed of the DataReader in ADO.NET. This should make my e-commerce website perform much quicker than when not using a LINQ To SQL Compiled Query.

For more information on the performance one achieves by using LINQ To SQL Compiled Queries, I recommend reading Rico Mariana's blog:

http://blogs.msdn.com/ricom/archive/2007/06/22/dlinq-linq-to-sql-performance-part-1.aspx

There are actually several parts to his LINQ To SQL performance testing that you can read on his blog.

Conclusion

LINQ To SQL offers the option of Compiled Queries when you need to achieve DataReader like performances in ADO.NET. This comes at a bit of a price of maintenance, because you will need to create compiled queries for those operations you feel are expensive.