A few months ago, Diane Wilson reported her performance findings when comparing EF to LINQ to SQL to DataReaders. Something looked wrong to me because her numbers were vastly different than what I had been seeing in the many perf tests that I did. We talked a bit and I pointed her to the critical perf blog posts that Brian Dawson wrote on the ADO.NET Team blog last year. These posts explain where the performance costs are with queries and how you can improve on them.

1. Exploring the Performance of the ADO.NET Entity Framework - Part 1

2. Exploring the Performance of the ADO.NET Entity Framework – Part 2

3. ADO.NET Entity Framework Performance Comparison

We also talked about NoTracking queries and their impact on performance when queries are executed multiple times against the same context. Diane modified her methodology but EF still looked pretty sad. One of her key factors was that she was testing for the web and using Pre-Compiled queries. The problem she was encountering was that she was not benefiting from the pre-compiled queries across sessions.

I have been thinking about this recently as I prepare some conference presentations about EF in web apps for next month's Developer Summit in Stockholm and was playing with compiled queries.

Diane was suffering from the fact that the pre-compiled query instance was not owned by the application process, so I shoved it into a static variable.

public class CustomerProvider {private EFWorkshopEntities _commonContext;
static Func<EFWorkshopEntities, IQueryable<Customer>> compQueryFunc;
 //declare static function

Then, in the class constructor, I compile the query, but only if the Func is null, meaning that it hasn't been compiled yet. This is the whole point of pre-compiling. Compiling a query (translating it into a store query) is a *very* expensive part of the query process.

public CustomerProvider()
{
  _commonContext = new EFWorkshopEntities(); 
  if (compQueryFunc == null)
    compQueryFunc =CompiledQuery.Compile<EFWorkshopEntities, 
                   IQueryable<Customer>>
                   (ctx => from cust in ctx.Customers 
                           where cust.Orders.Any() 
                           select cust);
} 

Finally, I have a function that invokes the query and returns the data to the caller.

public List<Customer> GetCustomer()
{
  var custs = compQueryFunc.Invoke(_commonContext);
  return custs.ToList();
}

Now for the testing.

I had a web page instantiate this class and call GetCustomer, the refresh the page, which mean GetCustomer was called again.

In the debugger I saw the compile method hit the first time, but not the second. Just what I was hoping.

Then I ended the session and started it again.

I watched in teh debugger and the compile method was not getting hit at all. The static Func was still active and populated and functioning properly since I was getting my customers back on my page.

So leveraging the pre-compiled query across web or service sessions while the application process continues to run, is the way to benefit from the pre-compilation.

I would imagine the same pattern is necessary for LINQ to SQL, but I would have to go look. L2S might handle the Func differently and leave it in the application process memory.

A few extra tricks

I showed this solution to Bruno Guardia Robles who is a performance guy on the ADO.NET team and he passed back to me a few hints.

One is to consider putting a thread lock around the Compile method, just in case multiple people hit it at the exact same time. Smart, since EF is not thread safe.

Another, which came from Jeff Derstadt on the EF team was very interesting.

It turns out that you can compile the query without the actual instance of the context. In other words, if I had only declared _commonContext, but not instantiated it prior to calling Compile, the compile still works! (I thought that was pretty cool.) What this means is that you can, if you want, put the compile method into a static constructor if you want and not bother instantiating the context until the first time it is truly needed, e.g., in my GetCustomer method.

So hopefully, Diane will retest her queries and have more confidence EF's performance in her web applications after this. :-)