CODEWITHSUNDEEP
sql-server-2008entity-framework
Niels Brinch
Niels Brinch

Reputation: 3632

Stored Procedure slow from Entity Framework because of date parameter

I have discovered an interesting issue where a Stored Procedure executes very slowly from Entity Framework. I have already solved the problem, but I would like to hear if anyone can tell me why the solution works.

Issue

I have a Stored Procedure GetLoginCount that receives a @date parameter of type DATETIME. When I execute this Stored Procedure directly on the database it executes within a second. When executing through my application via Entity Framework, it takes around 45 seconds.

I have tried using WITH RECOMPILE on the Stored Procedure and cleared execution plans on the server, to ensure it hadn't cached some slow version of the execution plan that didn't use the correct index.

Fast forward after 2 days of experiments, I found that if I simply put the following in the beginning of my Stored Procedure: DECLARE @date1 DATETIME = @date and use @date1 instead, the Stored Procedure executes in 1 second, also from Entity Framework.

WHY?

I have solved my problem and that's all good and fine, but I need to know why this specific solution works.

Upvotes: 1

Views: 1113

Answers (1)

Niels Brinch
Niels Brinch

Reputation: 3632

Martin Smith gave the correct answer in a comment, but as he hasn't put it as an answer, I'm inserting it here, so I can correctly mark the question as answered:

"Assigning to a variable and using the variable disables parameter sniffing. i.e. SQL Server no longer has a specific date it can look up in the statistics to get selectivity estimates and just guesses as per OPTIMIZE FOR UNKNOWN"

Using OPTIMIZE FOR UNKNOWN indeed solves the problem.

Upvotes: 1

Related Questions