SQL Query taking too much time in entity framework using ToList()

Question

I'm utilizing Entity Framework Core. Using this code, fetching 2000 records takes over 1 minute. Please assist me in finding the data within seconds.

var incomingEmails = acompDbContext.IncomingEmails.AsNoTracking().Where(ie => ie.ReceiverRuc == req.ReceiverRuc)
.Select(ie => new IncomingEmail
{
    // Select only necessary properties
    IncomingEmailId = ie.IncomingEmailId,
    IdStatusMail = ie.IdStatusMail,
    MessageId = ie.MessageId,
    From = ie.From,
    To = ie.To,
    Subject = ie.Subject,
    Body = ie.Body,
    AttachmentName = ie.AttachmentName,
    Attachment = ie.Attachment,
    AttachmentType = ie.AttachmentType,
    AttachmentId = ie.AttachmentId,
    EmitterRuc = ie.EmitterRuc,
    EmitterBusinessName = ie.EmitterBusinessName,
    ReceiverRuc = ie.ReceiverRuc,
    Date = ie.Date,
    Valid = ie.Valid
}).ToListAsync();

I tried to reduce the data but I need all 2000 records.

Answer 1

Three things come to mind. First check that this ReceiverRuc column is indexed in the database. Querying data against a non-indexed column will be slower, especially with tables with a lot of data.

Second, this query does appear to be pulling more info that it probably should. The warning flags I see are the Email Body and Attachment. When pulling thousands of rows to iterate over consider leaving out heavyweight fields that aren't immediately necessary. If you do want to eventually iterate over all 2000 results you can fetch these heavy fields by ID, or even batch them up and fetch in smaller groups of 10 or 100 at a time while processing the email records.

Third is the Attachment itself, is this a column in the Email table or a row in a separate attachments table? If it is the later than you will want to project this down to a simplified attachment object, again with just the columns you need:

 .Select(ie => new IncomingEmail
{
    // Select only necessary properties
    // ...
    Attachment = new Attachment { ... }
    // ...
}).ToListAsync();

The query itself requires an await if you are using ToListAsync().

The next thing you can do to get an idea of what is going on is to run a profiler against the database to capture SQL statements being run. Culprits to look out for are things like lazy loading calls. For instance if you kick off this method and capture an SQL line appearing to load the data from the email, but then see 2000 or so additional SQL statements pulling data relating to some table around the attachments (assuming Attachment was a row in a different table) then you might be running foul of lazy loading references off the included attachment entity. This issue itself would be solved by projecting the attachment into a new container. (Third point above) Still, when reading data a profiler can help identify performance issues relating to lazy loading. You can also check the actual SQL statement being run to get an execution plan which can provide tips on performance.