Entity Framework 6.1.3 exec sp_executesql Causing Huge Performance Issue

Question

I have a pretty basic Entity Framework Query that is extremely slow (30 seconds for 2 records) against a SQL Server database. I worked with our DBA to get the actual query getting executed on the Database:

exec sp_executesql N'SELECT 
    [Project1].[DocumentID] AS [DocumentID], 
    [Project1].[CreationDate] AS [CreationDate], 
    [Project1].[DocType] AS [DocType]
    FROM ( SELECT 
        [Extent1].[DocumentID] AS [DocumentID], 
        [Extent1].[DocType] AS [DocType], 
        [Extent1].[CreationDate] AS [CreationDate]
        FROM   [dbo].[DocFile] AS [Extent1]
        LEFT OUTER JOIN [dbo].[Demographics] AS [Extent2] ON [Extent1].[DemographicsKey] = [Extent2].[DemographicsKey]
        LEFT OUTER JOIN [dbo].[Client] AS [Extent3] ON [Extent1].[ClientKey] = [Extent3].[ClientKey]
        WHERE (([Extent1].[DocType] = @p__linq__0) OR (([Extent1].[DocType] IS NULL) AND (@p__linq__0 IS NULL))) AND ([Extent1].[CreationDate] >= @p__linq__1) AND (([Extent2].[SSN] = @p__linq__2) OR (([Extent2].[SSN] IS NULL) AND (@p__linq__2 IS NULL))) AND (([Extent3].[ControlNumber] = @p__linq__3) OR (([Extent3].[ControlNumber] IS NULL) AND (@p__linq__3 IS NULL)))
    )  AS [Project1]
    ORDER BY [Project1].[CreationDate] DESC',N'@p__linq__0 nvarchar(4000),@p__linq__1 datetime2(7),@p__linq__2 nvarchar(4000),@p__linq__3 nvarchar(4000)',@p__linq__0=N'AST',@p__linq__1='2013-07-01 15:52:25.4288579',@p__linq__2=N'818760001',@p__linq__3=N'8187600002'

When I run this exact query from SQL Server Management Studio it also take approximately 30 seconds.

However, when I take out the exec sp_executesql and replace the variables with inline values the query only takes a few milliseconds:

SELECT 
    [Project1].[DocumentID] AS [DocumentID], 
    [Project1].[CreationDate] AS [CreationDate], 
    [Project1].[DocType] AS [DocType]
    FROM ( SELECT 
        [Extent1].[DocumentID] AS [DocumentID], 
        [Extent1].[DocType] AS [DocType], 
        [Extent1].[CreationDate] AS [CreationDate]
        FROM   [dbo].[DocFile] AS [Extent1]
        LEFT OUTER JOIN [dbo].[Demographics] AS [Extent2] ON [Extent1].[DemographicsKey] = [Extent2].[DemographicsKey]
        LEFT OUTER JOIN [dbo].[Client] AS [Extent3] ON [Extent1].[ClientKey] = [Extent3].[ClientKey]
        WHERE (([Extent1].[DocType] = 'AST') OR (([Extent1].[DocType] IS NULL) AND ('AST' IS NULL))) AND ([Extent1].[CreationDate] >= '7/1/2013') AND (([Extent2].[SSN] = '818760002') OR (([Extent2].[SSN] IS NULL) AND ('818760002' IS NULL))) AND (([Extent3].[ControlNumber] = '8187600002') OR (([Extent3].[ControlNumber] IS NULL) AND ('8187600002' IS NULL)))
    )  AS [Project1]

Why would these 2 queries have such dramatically different performance? What can I do to either fix the EF code or fix the DB to make the code perform MUCH faster?

Thanks!

Where are you getting these variables? Make sure you don’t take them from EF’s proxy type entities with lazy loading enabled (and if needed properties are not loaded yet). It would make a separate SQL query for receiving each needed property from the DB. If it’s not the case for you, then I’m not sure what can cause those issues. — Ivan Yurchenko, Jul 03 '16 at 01:48
Iv don't understand what you mean by EF's proxy type entities. The variables are coming from query string The from a restful Web api application. — jkruer01, Jul 03 '16 at 01:51
Check this: http://stackoverflow.com/questions/15767803/entity-framework-query-slow-but-same-sql-in-sqlquery-is-fast — Ivan Yurchenko, Jul 03 '16 at 01:57
That looks like the issue. Thanks! I will verify on Tuesday once our team is back in the office from the long weekend! Thank you. — jkruer01, Jul 03 '16 at 02:16
Can you add an answer so I can't accept it as the correct answer? — jkruer01, Jul 03 '16 at 02:17

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

1

So looks like the correct solution is here: Entity Framework query slow, but same SQL in SqlQuery is fast

Most likely it’s some problem with types matching (like a nullable type or varchar vs nvarchar etc.)

Will be glad if it helps you to solve the problem. ;)

edited May 23 '17 at 11:45

Community

1
1

answered Jul 03 '16 at 11:33

Ivan Yurchenko

3,762
1
21
35

Entity Framework 6.1.3 exec sp_executesql Causing Huge Performance Issue

1 Answers1