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.


Need Your Help

Can someone help me optimize this “request prime numbers” PHP Function

php performance function optimization

i`m having trouble optimizing the performance of this PHP function, witch is supposed to return the first $n number of primes requested. The request is made through a form, and when i request the f...

How to show user's udt definition in postgresql?

postgresql user-defined-types

I know the udt definition relies on pg_catalog.pg_type, but is there another table where I can get its columns/elements?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.