CODEWITHSUNDEEP
sql-serverentity-framework
Michiel Cornille
Michiel Cornille

Reputation: 2097

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

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

Upvotes: 17

Views: 9342

Answers (5)

kirk
kirk

Reputation: 1007

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

Upvotes: -2

Christopher
Christopher

Reputation: 10627

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.

Upvotes: 15

ali
ali

Reputation: 1351

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

Upvotes: 2

Derek
Derek

Reputation: 997

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();

Upvotes: 2

Michiel Cornille
Michiel Cornille

Reputation: 2097

(DBCC FREEPROCCACHE)
DBCC DROPCLEANBUFFERS

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

Upvotes: 2

Related Questions