NullReference NullReference - 4 months ago 45
SQL Question

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

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?

Answer

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."

Comments