How can I "warm up" a LinqToSql precompiled query?

Question

In LinqToSql, precompiled queries are great - but it still requires me to take the compilation performance hit the first time the query is used.

I would like to 'warm up' these precompiled queries in the background when the application starts up. Obviously I can do that by calling them with some default parameters - however this results in a needless hit to the database.

Is there any way to 'warm up' the precompiled query without calling out to the database?

I have taken a look at the CompliedQuery source code but it seems many of the classes needed are sealed and/or internal...

It depends on what is causing the "hit". Have you tried pre-generating the views? (note, *NOT* MVC views) http://msdn.microsoft.com/en-us/data/dn469601.aspx — Erik Funkenbusch, Jun 20 '14 at 18:25
the "hit" is linq to sql translating the expression tree to SQL. In other words, it's the very purpose of using CompiledQuery.Compile() :) — Shaun Rowan, Jun 20 '14 at 18:43
@ShaunRowan - are you certain of that? And I don't just mean you think you know.. do you have evidence from a profiler or something that shows this is where the hit is? If you look at the following article, it shows where performance hits are, and view generation is one of them. http://msdn.microsoft.com/en-us/data/hh949853.aspx query translation is, but not compiling. — Erik Funkenbusch, Jun 20 '14 at 19:15
I am postitive. The hit is in the expression tree visitors which is creating the SQL. It is very easy to recreate - create virtually any compiled query of medium-high complexity and measure the performance. It will take longer the first time regardless of whether you have warmed up your context (in this case has nothing to do with view generation which is really an Entity Framework concept and has nothing to do with linq to sql) — Shaun Rowan, Jun 20 '14 at 19:34

Shaun Rowan · Accepted Answer · 2014-06-22T01:58:14.397

Ok, so looking at source code for the CompiledQuery which gets returned from CompiledQuery.Compile() we can verify that the query is only actually compiled once you invoke the method for the first time:

    ICompiledQuery compiled; 
    private object ExecuteQuery(DataContext context, object[] args) {
        if (context == null) { 
            throw Error.ArgumentNull("context");
        } 
        if (this.compiled == null) { 
            lock (this) {
                if (this.compiled == null) { 
                    // This is where the query is copmiled
                    this.compiled = context.Provider.Compile(this.query);
                    this.mappingSource = context.Mapping.MappingSource;
                }
            } 
        }
        // this is where the query is executed against the data store
        return this.compiled.Execute(context.Provider, args).ReturnValue;
    }

So there is no way to force the compilation without actually executing it.

However, I did find a hacky workaround for it which gives me what I need. There is a way we can execute the query thus causing the query to be precompiled without allowing the call to the database:

// compiled query
static Func<NorthwindDataContext, int, IQueryable<Order>> GetOrderById =
    CompiledQuery.Compile((NorthwindDataContext db, int orderId) => LINQ GOES HERE );


static void Warmup() 
{
    var context = new NorthwindDataContext("ConnectionString");
    // dispose the connection to force the attempted database call to fail
    context.Connecction.Dispose(); 
    try  
    {
         GetOrderById(context, 1);
    }
    catch (Exception ex)
    {
         // we expect to find InvalidOperationException with the message
         // "The ConnectionString property has not been initialized."
         // but the underlying query will now be compiled
    }
}

static void GetData() 
{
     // as long as we called warmup first, this will not take the compilation performance hit
     var context = new NorthwindDataContext("ConnectionString");
     var result = GetOrderById(context, 1);
}

How can I "warm up" a LinqToSql precompiled query?

1 Answers1