-1

The codebase I work on uses Entity Framework and the repository pattern. In order to get decent performance (i.e., 150 ms database calls instead of 1500 ms calls), I've found I need to use AsNoTracking when selecting data to avoid caching.

The following code works splendidly: 

using (var context = new DeviceCloudModel())
{
    var model = context.Devices
        .AsNoTracking()
        .Include(d => d.DeviceSettings)
        .Where(d => d.SerialNumber == serialnumber && o.IsActive).FirstOrDefault();
}

However, this doesn't, and is just as slow (I'm guessing it's caching again):

var predicate = (filter ?? (x => true)).Compile(); // assume filter is always null

var model = _repository
        .Get(o => o.SerialNumber == serialnumber && o.IsActive)
        .Where(predicate)
        .FirstOrDefault();

Here's my repository Get method:

public override List<Device> Get(Expression<Func<Device, bool>> filter = null)
{
    var predicate = (filter ?? (x => true)).Compile();

    var ret = _context.Devices
        .AsNoTracking()
        .Include(d => d.DeviceSettings)
        .Where(predicate)
        .ToList();

    return ret;
}

We want to improve our performance, but we don't want to get rid of the repository pattern or throw an ugly hack on top of it. Is there any way to use AsNoTracking with the current repository code we have in place?

Slothario
  • 2,830
  • 3
  • 31
  • 47
  • 1
    `However, this doesn't, and is just as slow (I'm guessing it's caching again):` <= **Don't guess**. Use a profiling tool (like Sql Server Profiler if this is SqlServer) and see what the actual query being executed is and compare that to what you are expecting it to be (based on your first code sample that "works splendidly"). – Igor Aug 02 '18 at 19:53
  • 1
    Also query 1 calls `FirstOrDefault`, the 2nd query calls `ToList` and *Then* calls FirstOrDefault – Igor Aug 02 '18 at 19:56
  • Also the two queries doesn't seem to be equivalent. What happens if you replace the `.FirstOrDefault()` in " splendidly working" example with `.ToList()`? – Ivan Stoev Aug 02 '18 at 19:57
  • @Igor I did look at the queries and it executes a reasonable one. What takes so long is getting Entity Framework to start up. If I run a similar query again, it runs very fast. So basically I'm not 100% sure what EF is doing, but I do know the problem is with the set up and not the query it's generating. – Slothario Aug 02 '18 at 20:07
  • @Ivan It is not a list problem. The query generated by the old method still only looks up by one serial number. – Slothario Aug 02 '18 at 20:07
  • 1
    EF almost always incurs an initial startup cost the first time it is loaded into an/the application domain. See also [Entity framework very slow to load for first time after every compilatio](https://stackoverflow.com/q/30423838/1260204), [How to “warm-up” Entity Framework? When does it get “cold”?](https://stackoverflow.com/q/13250679/1260204), [Six seconds warmup time for the first entity framework 6 nonquery](https://stackoverflow.com/q/20565679/1260204) – Igor Aug 02 '18 at 20:08
  • 2
    Why do you compile the expression inside the Get` method? You shouldn't! Also, another "don't guess" comment: check the content of the context's cache and notice that in your second example no entities are tracked either. I think converting the expression to `Func` is what hits performance, because the filter isn't transferred to the SQL statement. – Gert Arnold Aug 02 '18 at 20:49

1 Answers1

1

Query 1 calls FirstOrDefault and returns the first result from the database. So even if the database contains 5000 matches only the first one will be returned from the database.

Your repository wrapper call calls ToList and returns all the results from the database and only then does your code take the first result from everything already returned. So if ToList returns 5000 matches all of those will be loaded into memory and then you take the first one.

Side note 1

(I'm guessing it's caching again)

Don't guess. Use a profiling tool (like Sql Server Profiler if your Database is Sql Server) and see what the actual query being executed is and compare that to what you are expecting it to be (based on your first code sample that "works splendidly"). In this case you could capture the queries and notice that the first probably returns a single result and the other returns many other records.

Side note 2

I would like to point out that it is almost always a bad idea to wrap the Entity Framework functionality in a Repository or UoW patterns.

The type DbContext is an implementation of a UoW pattern and the type DbSet<T> is an implementation of a Repository pattern. Why re-wrap these types in your own implementation of the same pattern? You are adding nothing of value, just more code and a poor abstraction which results in code that is harder to read, debug, and use correctly.

Igor
  • 60,821
  • 10
  • 100
  • 175
  • I did look at the query, and the query is reasonable and fast. The problem is the first time you RUN the query -- EF takes those 1.5 seconds to warm up, which I guess is the caching. Perhaps someone more knowledgeable than me can say for sure. Unfortunately, I can't rip out the repository pattern because it's the code architect's call. I've made the suggestion before. – Slothario Aug 02 '18 at 20:09
  • It is necessary in applications were you are accessing more than one repository, in a modern application is very probable that somethings are saved in a sql database, but others could use redis or a nosql db, or any cloud service, so you want to split and make a clear distinction between the thigs that you save and how you save it, so NO, IT IS NOT ALWAYS A BAD IDEA TO INCLUDE A REPOSITORY PATTERN – rekiem87 Aug 02 '18 at 21:29