Entity Framework 4 - Stored Procedure Excessively Slow

Question

When executed from Management Studio, the stored procedure ends in <0.1 s, but when executed through EF, it takes more than 2 seconds.

Below is the profiler output:

A bit ironic, since the reason for creating SP was to improve performance in an EF request, which took about 1.2 seconds.

UPDATE Whatever the cost, the SP result maps to a complex EF type. I am calling without focus:

var menuTags = db.GetMenuTags(2, "en-US"); 

SQL generated by EF:

 exec [dbo].[GetMenuTags] @CustTypeId=2,@LanguageId='en-US ' 

The only difference is that empty EF spaces are added at the end of @LanguageId, but they do not affect performance. I tried the same SQL in MSSMS and it works the same as without spaces.

Answer 1

SPs executed through EF always return all rows; this may be a delay.

Maybe posting your SP and source EF-generated SQL will help

Edited to add what seems like a solution.

Another thought (from the past days) The sniffing parameter was the only culprit of the slow SP, which was launched by the developer with bad parameters, and then created a nasty execution plan. Adding the WITH RECOMPILE option to get a new plan based on decent options will solve this problem basically. Another cheat was to assign the passed parameters to local SQL variables (local to SP) and use only local variables in the query.

Now that you have an SP with a decent execution plan, you must remove "WITH RECOMPILE" and it will work anyway. The parameter Sniffing problems are rare, I would say.