Entity Framework Issue When running Stored Procedures

Question

I have an issue with stored procedures and Entity Framework.

Let me explain what is happening... and what I have tried thus far.

I have a stored procedure, which does not do an awful lot

SELECT 
    COUNT(DISTINCT(EmailAddress)) AcceptedQuotes, 
    CONVERT (DATE,QuoteDate) QuoteDate
FROM
    Quote Q
JOIN 
    Person P on Q.PersonPk = P.Pk
JOIN 
    Product Pr on Q.ProductPk = Pr.Pk
JOIN 
    Accepted A on Q.Pk = A.QuotePk
WHERE               
    QuoteDate between @startDate and @endDate
    AND CompanyPk = @companyPk
    AND FirstName != 'Test'
    AND FirstName != 'test'
    AND FirstName != 'EOH'

I want to execute this, and it works fine in SSMS and does not even take 1 second.

Now, I import this in to Entity Framework, it times out and I set the command timeout to 120...

Ok so what I have tried thus far and what I have tested.

If I use SqlCommand, SqlDataAdapter, DataTable way, with my own connection string, it executes as expected. When I use Entity Framework connection string in this scenario, it times out.

I altered my stored procedure to include "Recompile" option and also tried the SET ARITHABORT way, no luck, it times out when run through the EF.

Is this a bug in EF?

I have now just about decided to rewrite this using "old school" data access.

Also note that the EF executes fine with other stored procs, from the same database.

Any ideas or help would be greatly appreciated...

PS. I found this article, but no help either :(

http://www.sommarskog.se/query-plan-mysteries.html

Answer 1

This may be caused by Parameter Sniffing

When a stored procedure is compiled or recompiled, the parameter values passed for that invocation are "sniffed" and used for cardinality estimation. The net effect is that the plan is optimized as if those specific parameter values were used as literals in the query.

1. Using dummy variables that are not directly displayed on parameters also ensure execution plan stability without need to add recompile hint, example below:

create procedure dbo.SearchProducts @Keyword varchar(100) As Declare @Keyworddummy as varchar(100) Set @Keyworddummy = @Keyword select * from Products where Keyword like @Keyworddummy

2. To prevent this and other similar situations, you can use the following query option:

OPTIMIZE FOR RECOMPILE

3. Disable auto-update statistics during the batch