Why are my compiled linq queries slow the first time?

We've recently started using complied queries in order to improve performance on our Linq to SQL setup. There are several queries that always take multiple seconds to run the first time take under a second in subsequent runs. This appears to be because the compilation doesn't actually take place until the call is made the first time.

Is there an easy way to force this compilation to happen during the compilation of the program or at the very least during startup?

EDIT: So from the comments I'm seeing it looks like linq queries are definitely not compiled until the call is made. Righ now I'm writing my queries like this:

static readonly Func<DataContext, int, IQueryable<Item>> getByPLUComp =
    CompiledQuery.Compile<DataContext, int, IQueryable<Item>>((db, PLU) =>
            from i in db.Items
            where i.IntPLU == PLU && i.Terminated == null
            select i);

I have a bunch of these static readonly Funcs floating around. Is there an easy way I can have my program run around and initialize them on startup so the cost is incurred there rather than during regular use?

EDIT 2: One last try before I give up on this question. To fix this problem I've just added calls to the compiled queries during the initialization of my program. For example:

public void Initialize()
{
    DataContext db = new DataContext();
    getByPLUComp(db, 0);
}

Is there a more elegant way to force compilation without just running the query and throwing away the results?

Answers


The database will be caching 2 things when the SQL is executed:

  1. execution plan
  2. data

The execution plan will remain in the cache for subsequent calls for that same query until it "expires". However, it's the data cache that generally makes the biggest difference in performance as the data is kept in memory so subsequent calls don't need to access the disk to get it.


The server need to be the query plan on the first execuation, it then uses the cached plan for subsequent executions.


Sql Cashes data the first time wich will improve the other runs. . . You could make script that runs though the calls so that they will be cashed. You could ad this to the installation process. . . . That may work.

You could make a table that updates the information needed into it. That way when you need the information its readly available. (if there are alot of processing on bringing the data together)


jalf pointed out in the comments that this has nothing to do with SQL and everything to do with the fact that compiled queries are compiled at runtime not during compile time.

http://blogs.msdn.com/ricom/archive/2007/06/22/dlinq-linq-to-sql-performance-part-1.aspx


Need Your Help

evaluate cell in 2d array using spring expression language

java spring jsp jstl el

In a spring mvc application, I have a jsp which needs to iterate through two collections who both share the same index, i. The blocks List contains time values. The filledblocks array is a 2d array

Regarding xpath parsing of xml with default namespace using Saxon

java xml xpath namespaces saxon

I wanted some info related to xpath parsing of default namespace using saxon. I am using the Saxon-HE-9.5.1-3.jar to use xpath 2 features in my code. After including the saxon library in the class ...