Reputation: 89
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
Upvotes: 9
Views: 1405
Reputation: 1401
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.
- 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
- To prevent this and other similar situations, you can use the following query option:
OPTIMIZE FOR RECOMPILE
- Disable auto-update statistics during the batch
Upvotes: 1