Query runs in less than a millisecond in SQL, but times out in Entity Framework

Question

The following linq-to-entities query throws

Entity Framework Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

after ToList()ing it.

 var q = (from contact 
          in cDB.Contacts.Where(x => x.Templategroepen.Any(z => z.Autonummer == templategroep.Autonummer) 
                                && !x.Uitschrijvings.Any(t => t.Templategroep.Autonummer == templategroep.Autonummer)) 
          select contact.Taal).Distinct();

((System.Data.Objects.ObjectQuery)q).ToTraceString() gives me:

SELECT 
[Distinct1].[Taal] AS [Taal]
FROM ( SELECT DISTINCT 
[Extent1].[Taal] AS [Taal]
FROM [dbo].[ContactSet] AS [Extent1]
WHERE ( EXISTS (SELECT 
1 AS [C1]
FROM [dbo].[TemplategroepContact] AS [Extent2]
WHERE ([Extent1].[Autonummer] = [Extent2].[Contacts_Autonummer]) AND ([Extent2].[Templategroepen_Autonummer] = @p__linq__0)
)) AND ( NOT EXISTS (SELECT 
1 AS [C1]
FROM [dbo].[UitschrijvingenSet] AS [Extent3]
WHERE ([Extent1].[Autonummer] = [Extent3].[Contact_Autonummer]) AND ([Extent3].[Templategroep_Autonummer] = @p__linq__1)
))
)  AS [Distinct1]

the query from tracestring runs in under 1 seconds in sql management studio, but times out when actually to-listing it? how is that possible again?

*Update: added SQL PROFILER output for query * this runs as slow as the EF ToList() (>30seconds)

exec sp_executesql N'SELECT 
[Distinct1].[Taal] AS [Taal]
FROM ( SELECT DISTINCT 
    [Extent1].[Taal] AS [Taal]
    FROM [dbo].[ContactSet] AS [Extent1]
    WHERE ( EXISTS (SELECT 
        1 AS [C1]
        FROM [dbo].[TemplategroepContact] AS [Extent2]
        WHERE ([Extent1].[Autonummer] = [Extent2].[Contacts_Autonummer]) AND ([Extent2].[Templategroepen_Autonummer] = @p__linq__0)
    )) AND ( NOT EXISTS (SELECT 
        1 AS [C1]
        FROM [dbo].[UitschrijvingenSet] AS [Extent3]
        WHERE ([Extent1].[Autonummer] = [Extent3].[Contact_Autonummer]) AND ([Extent3].[Templategroep_Autonummer] = @p__linq__1)
    ))
)  AS [Distinct1]',N'@p__linq__0 int,@p__linq__1 int',@p__linq__0=1,@p__linq__1=1

Open up your SQL Profiler, and see what exactly is getting executed, when you hit EF code. This will show you, what kind SQLQuery, that is trying to execute, if at all it is executing. Try this http://www.petri.co.il/monitoring-with-sql-profiler.htm — Hari Gillala, Jul 05 '12 at 12:44
Read [Slow in the Application, Fast in SSMS? Understanding Performance Mysteries](http://sommarskog.se/query-plan-mysteries.html) — Remus Rusanu, Jul 05 '12 at 12:45
in sql it returns 1 -10 rows depending on the database, but it accesses +- 10000 rows to gather those — Michiel Cornille, Jul 05 '12 at 12:45

score 14 · Accepted Answer · answered Dec 22 '16 at 20:42

14

I observed this issue with EF6.

await _context.Database.SqlQuery<MyType>(sql) was timing out even when my timeout value was cranked up to 60 seconds. However, executing the exact same SQL (used profiler to confirm the sql I passed in was unmodified) in SSMS yielded expected results in one second.

exec sp_updatestats

Fixed the issue for me.

answered Dec 22 '16 at 20:42

Christopher

10,409
13
73
97

Encountered this as well. We believe that it was a bad execution plan. Update stats and rebuilding indexes fixed problem. We added additional code so that EF would add additional parameter to query that would return same results. It was different enough that it used a different execution plan and came back under a second. So we refreshed the stats removed the additional parameter and it returned results back quickly. So we scheduled stat refresh (and index rebuild) during least busiest hours once a month. – Charles Byrne Apr 13 '18 at 17:58

Michiel Cornille · Answer 2 · 2012-07-06T06:33:58.347

2

(DBCC FREEPROCCACHE)
DBCC DROPCLEANBUFFERS

made the problem go away for now, but I think that might just be a temp. solution

edited Jul 06 '12 at 06:33

answered Jul 06 '12 at 06:27

Michiel Cornille

2,067
1
19
42

score 2 · Answer 3 · edited May 23 '17 at 11:53

2

I know this is a little late, but I found the answer here.

Basically Entity Framework likes to track everything by default. If you don't need it (i.e. not inserting or updating or deleting entities), turn it off to speed up your queries.

If you're using Entity Framework Code First you can achieve this like so:

var q = (from contact
      in cDB.Contacts.AsNoTracking()
          .Where(x => x.Templategroepen.Any(z => z.Autonummer == templategroep.Autonummer) 
                            && !x.Uitschrijvings.Any(t => t.Templategroep.Autonummer == templategroep.Autonummer)) 
      select contact.Taal).Distinct();

edited May 23 '17 at 11:53

Community

1
1

answered Feb 21 '13 at 23:33

Derek

967
1
9
16

any Idea how running (DBCC FREEPROCCACHE) DBCC DROPCLEANBUFFERS helped then? since it's a solution in a different layer – Michiel Cornille Feb 22 '13 at 10:12
1

I'm not as experienced on the SQL side, but after reading up on what those commands do I would have thought performance of your query would have gone down. This [answer](http://stackoverflow.com/a/6862508/803349) seems to offer a clue in that SQL Server might be caching a bad execution plan. All in all, I think the 2 DBCC command fix is a bad long term solution since they could affect the cached execution plans of other queries. – Derek Feb 22 '13 at 19:56

score 2 · Answer 4 · answered Nov 21 '14 at 11:48

I had similar issue with EF6. When using SqlQuery function in EF, I got timeout although query was executed in milliseconds in Management Studio. I found that it happened due the value of one of the sql parameters that I used in EF query. To make it clear, below is the similar SQL query I experienced with.

SELECT * FROM TBL WHERE field1 > @p1 AND field2>@p2 AND field3<@p3

When @p1 is zero, I received timeout exception. When I made it 1 or something different, it was executed in milliseconds. By the way, the table that I queried on has more than 20M rows.

I hope it helps, Best

I have experienced this issue which caused repetitive timeouts in EF. I have been able to work around the problem by excluding the problematic condition in the case where the variable = 0 but it would never have occurred to me that the value of the variable can affect performance in this way! — Matt Austin, Apr 13 '18 at 10:09

score -2 · Answer 5 · answered Jun 07 '17 at 02:52

-2

You need to Add one column serves as uniqueId or key to be able to work in EF

answered Jun 07 '17 at 02:52

kirk

997
9
14

Query runs in less than a millisecond in SQL, but times out in Entity Framework

5 Answers5