Entity Framework - how to avoid query recompilation?

azure azure-sql-database c# entity-framework entity-framework-6

Question

I am working on optimization of our Entity Framework code, and currently I am facing an issue I'm not sure how to solve.

We are using Azure SQL + Code-First Entity Framework 6.1.3 + Asp.net Web Api v2. Endpoint is hosted in the cloud and I'm using it for testing.

I have an API action, that is used for getting filtered, sorted and paged data. Here's a simplified code of what I do with data:

var entities = DbContext.Services
        .Include(q => q.Consumer.Building.Address.Country)
        .Include(q => q.Consumer.Building.Address.State);

entities = entities.OrderBy(x => x.Consumer.RegisteredAt);

entities = entities.Where(x => x.IsDeleted == false); 
entities = entities.Where(x => userId.HasValue ? x.Owner.Id == userId ? true); //this part comes from deep internals, so I cannot change it quickly
var page = entities = entities.Skip(skip).Take(take).ToList();
var count = entities.Count();

var dtoPage = Mapper.Map<IEnumerable<ServiceDto>>(page);

return Page<ServiceDto>(dtoPage, count);

So the code does nothing exceptional - does not use IN clause, etc.., just simple filter, sorting and paging.

The problem: Calling this method has unstable execution time. I used a simple script to call API endpoint that calls this code with different params: it gets pages 0..5 consequently 200 times, one after another. For each call (except for the first) I expected the call to take less than 300 ms. BUT: from 1200 overall calls, 36 call times exceeded 1 second - like the query was re-compiled again during this time. Average time for calls was 250ms, but it spiked sometimes to 1000ms, it's 4 times difference.

Common behavior for tests is:

  1. First query is slow

  2. Second query is faster, but still slower then rest

  3. Rest queries are pretty stable fast, except for some spikes every now and then.

No one used testing environment except for me, so it's not load issue, I can reproduce it on any environment, even on fast local PC - i5 + 8gb RAM + SSD.

Also even small delay between tests -

So my question in short is: What does this issue come from?

  1. Is this really recompilation issue, if not, what is the possible source of the issue?
  2. Is there a document about query cache invalidation for Entity Framework 6?
  3. Is there a way to keep a query in the cache for longer time, so that if I query the method now and in 30 minutes, it would not have to re-compile it?
1
4
1/18/2016 1:21:02 PM

Accepted Answer

Is this really recompilation issue,

I don't think it is a recompilation issue. But you can mitigate recompilations by passing lambdas in your Skip and Take calls. See 4.2 Using functions that produce queries with constants

if not, what is the possible source of the issue?

AFAIK SqlAzure is shared environment. It may be the cause of your problem.

Is there a document about query cache invalidation for Entity Framework 6 ? Is there a way to keep a query in the cache for longer time, so that if I query the method now and in 30 minutes, it would not have to re-compile it?

Your Entity Framework query will stay in memory as long as the AppDomain, the sql query plan will stay in Sql Server memory as long as the server isn't in need of memory. The Skip and Take with lambdas will also help on that matter. See on the same page :

"In EF6 these methods have a lambda overload that effectively makes the
cached query plan reusable because EF can capture variables passed to
these methods and translate them to SQLparameters. This also helps
keep the cache cleaner since otherwise each query with a different
constant for Skip and Take would get its own query plan cache entry"
4
1/18/2016 1:56:26 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow