When does compile queries of LINQ to SQL improve performance

Question

I was referring to an article which focuses on Speeding up LINQ to SQL Queries. One of the techniques it mentions is "Use Compiled Queries" and explains how to use it.

I wanted to see performance improvement of compiled queries and hence i tried the same example provided by the author. I used Northwind Db as datacontext. I tried normal execution and compiledquery execution and checked them on LINQ PAD.

First I tried executing the query without using CompileQuery. It took 2.065 seconds.

var oo =   from o in Orders
   where o.OrderDetails.Any (p => p.UnitPrice > 100)
   select o;

oo.Dump ("Order items with unit price more than $100");

var oo1 = from o in Orders
   where o.OrderDetails.Any (p => p.UnitPrice > 10)
   select o;

oo1.Dump ("Order items with unit price more than $10"); 

Secondly, the queries with using CompileQuery. It took 2.100 seconds.

var oo = CompiledQuery.Compile ((TypedDataContext dc, decimal unitPrice) =>    
   from o in Orders
   where o.OrderDetails.Any (p => p.UnitPrice > unitPrice)
   select o
);

oo (this, 100).Dump ("Order items with unit price more than $100");
oo (this, 10).Dump ("Order items with unit price more than $10");

Re-executing them several times showed that the time taken by both of the approaches are almost similar.

Here we see only two query executions for each method. I tried making 10 queries for each of them. But both of them completed around 7 seconds.

Does pre-compiling the queries really improve the performance? Or am I getting it wrong it terms of usage ?

Thank you for your time and consideration.

Edit: After reading the accepted answer, readers may also want to go through this article which nicely explains how compiled queries improve performance.

Answer 1

Bear in mind that there are two main pieces of a LINQ query that can be particularly expensive:

1. Compiling the LINQ expressions into an SQL Statement.
2. Running the SQL Statement and retrieving the results

In your case, you have a relatively simple query, and either a very slow database connection, some very large data sets, or tables that are not indexed in an optimal way to run this particular query. Or maybe a combination of all three.

So compared to the amount of time it is taking to produce the SQL for your query (maybe 10-50 milliseconds), the second step is taking so much time (~1000 ms) that you can hardly notice the difference.

You would see significant improvements if the following conditions are all true:

1. your LINQ query is complex,
2. you have a fast connection to your database,
3. the SQL query itself runs quickly on that database, and
4. the result set is small enough that it gets transferred back from the database relatively quickly.

In practice, I've had queries that can take upwards of 500ms to compile, but only a few milliseconds to actually run. These are usually the cases where I focus on precompiling queries.

One good way to know ahead of time what kind of performance gains you can expect from precompiled queries is to time the second instance of your query using a Stopwatch object, and then run the generated SQL directly using LINQPad's Analyze SQL feature. If the SQL query returns quickly but the LINQ query takes a long time, that's a good candidate for precompiling.