Wednesday, March 20, 2013

Calling Stored procedure from EF takes long time and times out!

Hi Folks,
I was going through an interesting performance issue when calling a stored procedure from my web application more than one time and the WCF was timing out because of the stored procedure execution takes long time to execute.
At the first, I have a parameterized stored procedure that takes different parameters. First time it executes as expected.
The second time, the EF calls the stored procedure and i don't get any results back to my application. i thought there is an issue in the Stored procedure but no because the stored procedure works as expected from the SQL management studio.
This means, there is something is going on between the EF bindings and getting results to mapped views/entities we have in the DBML file.
I was searching and trying to find any clue why this could happen ? I tried different things but it didn't work, until i read the following articles about performance issues in EF:
I realized that i don't have issues in terms of the SQL performance, but EF does have some overhead on executing any stored procedure and getting the results back to mapped view objects. Since this search stored procedure return list of objects to one of my entities, i want to know why does it take long time to execute and i don't get my results back ?!
I found the solution for my problem by doing 2 steps:
1) Since i don't want to track any loaded objects for my entities, i added the following merge option with No Tracking when i call my stored procedure.

C# Code:

this.ObjectContext.Search(EmpId,EmpName, DeptId,DeptName, System.Data.Objects.MergeOption.NoTracking).ToList();

2) Since SQL Server has a known performance issue called Paramter sniffing, which means the SQL Server engine allows to compile the plan for the stored procedure and reuse this plan for subsequent calls of the same stored procedure with different paramters! and this was my case, i pass long list of parameters but with different values. the first time executes as expected because the server chose to compile the plan with the results but the second time it re-uses the compiled plan which might not work well with the results that i want and this takes long time to get the expected results!
This is a known issue when your stored procedure inlcudes Like keyword in your queries and you use the input paramters directly to filter your results without using other temp paramters assigned from the input parameters.
What you need to do is to force SQL to compile the query each call and not to use a precompiled plan and this can be accomplished by using : RECOMPILE query hints along with your query. This is supported in SQL 2005 and above. I'm using SQL 08 R2 server.

Select * FROM myView
WHERE DeptName like '%' + @DeptName+ '%' 
OPTION (RECOMPILE); -- This enhance the performance query and fix the parameter sniffing issue for multiple calls stored procedure.

 After these 2 changes, I ran my web application, the application works as expected and no times out for the stored procedure that is being called twice!

Hope this helps.

1) Table Hints in SQL Server:

2)  Paramter Sniffing in SQL Enginer:


No comments: