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

entity-framework sql-server

Question

The linq-to-entities query below throws

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

after being ToList()'ed.

 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() offers 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 tracestring query executes in less than a second in sql management studio, but times out when to-listing it? Once again, how is it possible?

This takes longer to execute than the EF ToList() (more than 30 seconds).

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
1
14
7/5/2012 2:38:21 PM

Accepted Answer

I saw this problem with EF6.

await _context.Database.SqlQuery<MyType>(sql) even with my timeout setting set to 60 seconds, it still clocking out. However, running the exact same SQL in SSMS (using the profiler to verify that the SQL I gave in was unaltered) produced the desired results in a matter of seconds.

Run sp updatestats.

I have the problem resolved.

8
12/22/2016 8:42:01 PM

Popular Answer

I realize that this is a bit late, but I have the solution: here.

Entity Framework, by default, loves to monitor everything. Turn it off if you don't need it to speed up your queries (i.e., aren't adding, updating, or removing things).

Using Entity Framework Code First, you may do this by doing the following:

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


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow