Why would a Entity Framework count statement timeout when the direct sql returns instantly?

Question

We recently deleted a large number of records from out database. After the delete this statement generated from the Entity Framework is timing out. We're setting SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED for these statements as well.

Entity framework code

_emailSendResultsRepository.GetTable().Count(e => e.EmailId == email.Id && e.SendDate >= startDate);

Generates this sql

SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
        COUNT(1) AS [A1]
        FROM [dbo].[EmailSendResults] AS [Extent1]
        WHERE ([Extent1].[EmailId] = @p__linq__0) AND ( CAST( [Extent1].[SendDate] AS datetime2) >= @p__linq__1)
)  AS [GroupBy1]

If I run the same statement via Sql management studio it returns instantly.
Counts to the same table without the date return quickly
All other data calls seems to be working fine.

Any ideas what would be causing the timeout?

Why are you calling `.GetTable()`? Also, can you make other calls to the same database? Are you getting any errors? — user1477388, Feb 20 '14 at 17:49
@user1477388 GetTable is just a way to get to the IQueryable. Other calls work correctly. Thanks! — NullReference, Feb 20 '14 at 18:00
If emailId is a Guid you should do `e.EmailId == new Guid(email.Id)` in your lambda — user1477388, Feb 20 '14 at 18:01
Have you tried profiling the code that is sent to the server? This should give you a good clue. — Crono, Feb 20 '14 at 18:02
I am somewhat surprised that EF is not generating parametized SQL for you. It is supposed to. — RBarryYoung, Feb 20 '14 at 18:05
@RBarryYoung It is I just accidentally put the sql I tested in mgt studio in there. — NullReference, Feb 20 '14 at 18:10
@Crono Can you explain what you mean by profiling the code? I've extracted the sql the EF generates and I thought that was profiling... — NullReference, Feb 20 '14 at 18:11
When using the Count on the EF, ALL records are returned and then it does a count of those records. If you have several million records, this is not a good idea. — Steve, Feb 21 '14 at 16:23
"If I run the same statement via Sql management studio it returns instantly." -- As your edit shows, you weren't running the same statement from the management studio. What happens when you do run the same statement from the management studio? Copy the *exact* text from the profiler. This should involve the `sp_executesql` system stored procedure. — , Feb 27 '14 at 08:25

score 1 · Accepted Answer · edited May 23 '17 at 11:59

It may not directly help you, but please checkout:

Which method performs better: .Any() vs .Count() > 0?

Without having had yet any performance issue like you described, I changed my checks about existance of records in tables from .Count() to .Any(). But this may only help you, when you really don't need the exact count. Additionally I need to mention that I currently don't delete any records in my large tables and normally I will do the .where() clauses before I check the .count().

When you change your query to .Where() you could try to check the .Any() before you gather the details of .Count()

var queryList = _emailSendResultsRepository.GetTable()
    .Where(e => e.EmailId == email.Id)
    .Where(e.SendDate >= startDate)
    .ToList();

    if (queryList.Any())
    {
        ....
        queryList.Count()....
        ....
    }

In a post in the msdn forums there is mentioned:

"But even SELECT COUNT(*) FROM Table will be fairly inefficient on large tables as SQL Server does a full table scan."

score 1 · Answer 2 · edited Sep 08 '15 at 08:27

Actually MarkusEgle, the statement you provided:

var queryList = messages
.Where(t => t.LogTimeStamp >= fromDate)
.Where(t => t.LogTimeStamp <= toDate)
.Where(t => t.LogType != stateInfoNoAuditFile)
.OrderBy(m => m.LogID)
.ToList();

Performs the query and fetches the results so if you then invoke queryList.Count it does not touch database at all, it just counts objects in the list (memory). You should remove ToList to get a result you want.

Why would a Entity Framework count statement timeout when the direct sql returns instantly?

2 Answers2