Hi I am using an entity framework object context's ExecuteStoredQuery to run a stored procedure. It has always ran fine and takes around 4 seconds. However, now it takes over 60 seconds to run the same stored procedure using entity framework. It still takes 4 seconds if I run the sp from SQL Management Studio.
What could this be?
I would start by checking out:
Stored procedure slow when called from web, fast from Management Studio
The basic idea of what the answer gets at (and I have experienced first hand as well) is that specific flags are set up for executing queries from the web which aren't set when executing through SSMS. The specific flag i've had issue with is ARITHABORT. If you set this flag on in SSMS, often you will have similar timing issues as your web.
The problem comes down to bad execution plans existing for one reason or another - the bad plan is often used with the web settings (arith abort on), whereas a new plan and/or good plan is used from SSMS with arith abort off.
http://www.sommarskog.se/query-plan-mysteries.html is quite a lengthy blog post on the issue (at least the reasoning behind why I experienced the issue.) Take a look and see if it helps.
One of the major reasons behind the issue according to the blog post is parameter sniffing and how the execution plans are created based on sub-optimal parameters. You didn't post your SP so I'm assuming it is a SP that runs with input params. Maybe you could post that information, as well as some code and it could be better assessed.
