CODEWITHSUNDEEP
c#linqentity-framework
Chris
Chris

Reputation: 27394

Entity framework loads all records for large table when only a subset is required

I have a table that lists all errors and warnings for an device (a hardware device whose details we store in the db). That is covered by a single DeviceLog.

The DeviceLog table stores both current and archive errors, this means though that for devices with a large archive it is very slow to get out the current error / warning state. 

device.Errors = databaseDevice.Errors.Where(
    e => e.Current && 
    e.LogEntryType == Models.DeviceLogEntryType.Error)
        .Select(e => new DeviceErrorLog()).ToList();

So the in this current case there are about 5000 entries in DeviceLog for this specific device, but no current ones so device.Errors.Count() == 0 but if, in intellisense, I hover over databaseDevice.Errors it shows the actual full count.

Is this behaviour expected, if so then how can I make this faster as I thought this should be a fast operation especially as I am specifying a very direct and easily searchable subset of the data

To clarify the data structure:

public class DeviceLogEntry
{        
    public DeviceLogEntryType /* An Enum */ LogEntryType { get; set; }
    public bool Current { get; set; }
}

Upvotes: 1

Views: 2232

Answers (2)

Jon Hanna
Jon Hanna

Reputation: 113342

but if, in intellisense, I hover over databaseDevice.Errors it shows the actual full count

Why shouldn't it? If you are hovering over databaseDevice.Errors, then intellisense is going to try to get you useful information about databaseDevice.Errors, of which the Count (produced by calling select count(*) from errors) is an example of such.

It is device.Errors that includes the Where constraint, not databaseDevice.Errors.

if so then how can I make this faster as I thought this should be a fast operation

Passing select count(*) should be a fast operation. But that's also irrelevant to your code, since your code isn't that which you have run by intellisense in this case; your code is:

databaseDevice.Errors.Where(
    e => e.Current && 
    e.LogEntryType == Models.DeviceLogEntryType.Error)
        .Select(e => new DeviceErrorLog()).ToList()

Which will execute something like:

select * from errors where e.current = 1 and e.LogType = 52

(Or whatever integer that enum value corresponds to).

And then building a list from it. It doesn't matter what intellisense did in a case that doesn't correspond to the actual executed code.

Two things you can do to improve performance though:

  1. Check the indices on the database table, as being able to quickly lookup on current and logEntryType will affect how fast the underlying SQL executes.

  2. Drop the ToList() unless you'll be dealing with those results more than once. If you will be dealing with it more than once, then that's great; get it into memory and hit it repeatedly as needed. If you'll not be dealing with it more than once, or if those ways you deal with it will involve further Where, then don't waste time building a list, just to go and query that list again, when you could just query the results themselves.

Upvotes: 4

Peter Smith
Peter Smith

Reputation: 5550

From my understanding of Entity Framework the behaviour described above us as expected. The context databaseDevice.Errors knows how many records there are in the table, however, due to lazy loading the actual results are not fetched until the .ToList() is executed. The full table is not fetched from the database.

I would not expect any performance issues with this but, if there are, adding some indexes to the table should fix that.

Upvotes: 1

Related Questions