6

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.

Mykroft
  • 13,077
  • 13
  • 44
  • 72
  • I think it might be worthwhile to do some profiling and attempt to find out where the delay is actually occurring. – Robert Harvey May 06 '13 at 18:57
  • the overhead of EF is pretty light and probably isn't the problem. Maybe install something like Glimpse (NuGet package) and see what's really to blame. You may have other events occuring that are really at fault (bootstrapped add-ins?). – Brad Christie May 06 '13 at 19:00
  • @RobertHarvey Do you mean SQL profiling or .NET profiling? – Mykroft May 06 '13 at 19:01
  • What kind of a database is it? if it's SQLExpress, there may be overhead mounting/attaching the dbf to the instance which is causing delay. – Brad Christie May 06 '13 at 19:03
  • @BradChristie: Six seconds of overhead? – Robert Harvey May 06 '13 at 19:05
  • Related: http://stackoverflow.com/questions/11996164. See also [this Google Search](https://www.google.com/search?q=long+delays+entity+framework). .NET profiling was used [here](http://stackoverflow.com/questions/10757019). – Robert Harvey May 06 '13 at 19:07
  • See edits. I think I've addressed most of the questions here. I'll try and put more info in once I have glimpse figured out and setup. – Mykroft May 06 '13 at 19:21
  • @RobertHarvey: I have no idea of the specs on his/her box; and I've seen stranger things happen. Depends on how throttled hdd is, what processes are running, etc. Either way, too many variables (without more information) to make a determination at this point. – Brad Christie May 06 '13 at 19:32
  • Duplicate? http://stackoverflow.com/questions/13250679/how-to-warm-up-entity-framework-when-does-it-get-cold – Gert Arnold May 06 '13 at 20:34

1 Answers1

3

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.

Slauma
  • 175,098
  • 59
  • 401
  • 420
  • 1
    This is enterprise coding so "getting famous" doesn't work as a solution :). But I think the Initialize call combined with the application auto-start stuff may solve my problem. I'll be testing that out today. Thanks. – Mykroft May 07 '13 at 13:34
  • "too tired to even notice the delay." - unless their in a different time zone. – tymtam Nov 02 '17 at 05:32