LinqToSql Precompiling queries benefit?

Question

I was looking through the sample LINQ queries provided with LINQPad taken from the C# 4.0 in a Nutshell book, and ran across something I have never used in LINQ to SQL... Compiled Queries.

Here is the exact exmaple:

// LINQ to SQL lets you precompile queries so that you pay the cost of translating
// the query from LINQ into SQL only once. In LINQPad the typed DataContext is
// called TypeDataContext, so we proceed as follows:

var cc = CompiledQuery.Compile ((TypedDataContext dc, decimal minPrice) =>    
    from c in Customers
    where c.Purchases.Any (p => p.Price > minPrice)
    select c
);

cc (this, 100).Dump ("Customers who spend more than $100");
cc (this, 1000).Dump ("Customers who spend more than $1000");

What does precompiling a LINQ to SQL query like this actually buy me? Would I get a performance boost from a query slightly more complex than this one? Is this even used in actual practice?

Answer 1

In a nutshell, precompiled querires buy you a performance gain when you need to run a single query multiple times.

Here's some information on LINQ To SQL performance.

I’ve read in several places that compiling your LINQ will help, but I have never heard anyone say how drastic the speed improvement can be. For example, in one of my favorite books (LINQ in Action) by Fabrice Marguerie and others, he quotes on page 296 a blog post by Rico Mariani titled DLINQ (Linq to SQL Performance (Part 1) as saying using a compiled query offers nearly twice the performanced of a non-compiled query, and goes on to say that it brings the performance to within 93% of using a raw data reader. Well, suffice it to say I never ran the test myself. I could have lived with twice, but not 37 times.

So, from this, it seems that you should always compile your LINQ to SQL queries. Well, that’s not quite true. What I’m recommending is that if you have a reason to execute the same query over and over you should strongly consider compiling. If for example, you are just making a LINQ to SQL call once, there is no benefit because you have to compile it anyway. Call it ten times? Well, you will have to decide for yourself.

Answer 2

The way I use compiled queries is in a static way: I statically declare the compiled query, so the query tree structure has to be parsed only once, and you basically have a prepared statement that just needs some extra parameters. This is mainly used on websites, so the query has to be compiled only once, ever. The performance gain depends of course on the complexity of your query.

Answer 3

We use this at our company and for queries that are run often need not be compiled for each run. You don't have to get overly complex with linq to sql before this makes a difference, but that will depend on the traffic and load on the servers.

Answer 4

From this article from Rico Mariani's Performance Tidbits

Q4:

What are the downsides to precompiled queries?

A:

There is no penalty to precompiling (see Quiz #13). The only way you might lose performance is if you precompile a zillion queries and then hardly use them at all -- you'd be wasting a lot of memory for no good reason.

But measure :)