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!

Answer 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. ;)