LINQ to SQL - compiled queries (with working example for beta 2)

I've been meaning to dig into LINQ performance for some time (actually since it came up during one of my interviews for this job), so I was interested to read a sequence of posts from some of the C# team, tracing the performance of LINQ to SQL from the May 2006 CTP (it was called DLINQ back then) up to early July 2007 (around beta 2 timeframe).

Back in May 2006, for the test in question LINQ to SQL was performing at 14.09% (approx ;-)) of the underlying SQL provider - you'd expect it to be slower than the provider, but that was quite a bit slower. Since then internal changes have got this figure up to 53.56% for the same code. But it's when you use LINQ compiled queries that things start to get interesting - I won't steal Rico's thunder by listing the results here, you'll have to read the articles for that (see links below), but compiled queries look like being very useful for queries that are likely to be run many times within an application. They look a little bit like this:

// precompile a query to retrieve a product based on product ID 

public static Func<AdventureWorksDataContext, int, tblProduct>
GetProductById = CompiledQuery.Compile((AdventureWorksDataContext db, int productID) =>
db.tblProducts.Single(p => p.ProductID == productID));

// precompile a query to retrieve a product based on partial product name

public static Func<AdventureWorksDataContext, string, IQueryable<tblProduct>>
GetProductsByPartialName = CompiledQuery.Compile((AdventureWorksDataContext db, string partialName) =>
from p in db.tblProducts where p.ProductName.Contains(partialName) select p); 

 // Use compiled queries

AdventureWorksDataContext adv = new AdventureWorksDataContext();
tblProduct product = GetProductById(adv, 1);
Console.Out.WriteLine("Product ID {0}, Name {1}", product.ProductID, product.ProductName);

var products = GetProductsByPartialName(adv, "ball");
foreach (tblProduct p in products)
{
Console.Out.WriteLine("Product ID {0}, Name {1}",
p.ProductID, p.ProductName);
}  

So pretty easy to do really (the above code works in VS 2008 beta 2 by the way, you can download the sample project which is attached to this post). Am really liking the intellisense support in VS 2008 by the way, really handy for LINQ.

 Here are the links to the performance articles I referred to earlier:

LINQ to SQL Performance Part 1

LINQ to SQL Performance Part 2

LINQ to SQL Performance Part 3

LINQ to SQL Performance Part 4

LINQ to SQL Performance Part 5

 

Cross posted from ronan's blog