How do I initialize my Entity Framework queries to speed them up?

Question

I have a login page that executes a very simple EF query to determine if a user is valid. On the first run this query takes about 6 seconds to run. On subsequent runs it takes much less than a second.

I've looked at an article that talked about using Application Auto-Start and my question is this: Is there a way to trigger this query to cause whatever caching needs to happen without actually calling the query, or is it necessary for me to just call the query with a dummy set of arguments?

EDIT: When I say six seconds I'm referring the time it takes to get the query. Code looks something like this (note in this case contactID is a nullable int and set to null):

return from contact in _context.Contacts 
             where contact.District == Environment.District &&
             contact.ContactId == (contactID ?? contact.ContactId)
             select contact;

This is a SqlServer 2008 and I've run a profiler to check the SQL and the duration it returns is 41ms for the query that ultimately gets executed. The 6 or 7 second delay happens before the query even reaches SQL though. I'm trying to setup glimpse now to see if it can give me more details on other things that may be going on at the same time.

Answer 1

This really sounds like what is called a "cold query". The main performance botteneck for cold queries is "View Generation" which is performed once per AppDomain of your application. Typically the effect is that your first query - and it doesn't matter which one - is slow and subsequent queries are fast.

It must not necessarily be a query that could be slow. If the first operation you are doing with EF in your application is an Insert that would be slow. Or even an Attach that doesn't touch the database at all would be slow as well. (That's a good simple test case by the way: Add a context.Users.Attach(new User()) into application start and watch in the debugger how long it takes to pass that line.)

In all cases the time is consumed by building an internal data structure in memory - the local query "views" (they have nothing to do with database table views) - that takes place once per AppDomain.

View Generation is described here in more detail and here where you also can find resources how to "pre-generate" those views as part of your build process and before deployment. (Note: You have to update these pre-generated every time your change your model and redeploy your application.)

The alternative way is to trigger loading your web application start periodically (by some process for example that hits the site). In application start you would run any dummy query or the Attach thing above or call the EF initialisation manually:

using (var context = new MyContext())
{
    context.Database.Initialize(false);
}

Edit

I forgot the last solution. Just ignore the 6 or 7 seconds. If your site gets famous and has reasonable traffic such a cold query will become unlikely to occur because the IIS worker process will rarely shut down the AppDomain. The occasional user who hits the site in the night when such a shut down has happened right before is probably too tired to even notice the delay.