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

How to refresh the active Slide in a slide show?

vba powerpoint powerpoint-vba

Based on my last question I got the right code to change the image of an shape.

Styling <select> menus using only CSS/CSS3

html css css3 custom-controls

I wish to know why can't we style a select menu(not a dropdown menu) using CSS alone like we can style buttons or checkboxes. I have come across many articles that have styled them but they used