Entity Framework - extremely long execution time for stored procedure

Question

I'm following up on my question yesterday, Entity Framework 6 get complext return value from a stored procedure. My stored procedure now runs under entity framework. However, it times out after 3 minutes, the connection time out.

I run the stored procedure in my SQL Server Management Studio with the line (customer information omitted):

EXEC spGetDupWOs @ProjectName=N'...', @City=N'...', @State=N'LA', @ProposalNum=N'201703080740-001', @County=N'...', @Owner=N'...', @QuoteRecipients=N'...', @ProjectID=-1

It executes in less than a second. When Entity framwork executes it, it takes forever.

Using the SQL Server Profiler, I determined that Entity Framework is sending this line to the SQL server:

exec sp_executesql N'EXEC spGetDupWOs',N'@ProjectName nvarchar(19),@City nvarchar(6),@State nvarchar(2),@ProjectNum nvarchar(12),@County nvarchar(10),@Owner nvarchar(23),@QuoteRecipients nvarchar(23),@ProjectID bigint',@ProjectName=N'...',@City=N'Holden',@State=N'LA',@ProposalNum=N'201703080740-001',@County=N'Livingston',@Owner=N'...',@BID_RECIP=N'...',@ProjectID=-1

When I run this in SSMS, it takes forever to run.

Reading the similar questions it looks like the issue is Parameter Sniffing and a change in execution plan.

Here is my call to execute the stored procedure in my application:

            List<DuplicateProposals> duplicateCheckResults = 
                db.Database.SqlQuery<DuplicateProposals>("spGetDupWOs", 
                spl.ToArray())
                .ToList();

After reading a bunch of articles online, I'm even more confused. How can I change my call to resolve this?

Answer 1

The issue identified is parameter sniffing in SQL Server. There are multiple approaches to handle this, but the most optimal for your scenario depends on your real use case, utilization, etc.

Here are some options.

1. Recompile the stored procedure with every execution. This may become very heavy CPU utilization, and is typically overkill. I would NOT recommend this option unless you have a very good reason. To implement: Use WITH RECOMPILE or the OPTION(RECOMPILE) hint at the end of your query.

2. Optimize for hint. This can be a workaround for the parameter sniffing, but may result in a subpar execution plan for all of your queries. Typically, not an optimal approach. To implement: Use OPTION(OPTIMIZE FOR UNKNOWN)

3. Copy parameter to a local variable. Was more common in older versions of SQL Server. To implement: Declare a local variable, then copy the value from your input parameter to your local variable. DECLARE @local_var1 char(1) = @InputParam1;

4. Turn off parameter sniffing at query level. This approach uses the QUERYTRACEON hint. This may be the most optimal approach for this case scenario. I would recommend to explore this option as a primary strategy. To implement: add OPTION(QUERYTRACEON 4136) to the end of your query.

Example:

SELECT * FROM  dbo.MyTable T
WHERE T.Col1 = @Param1 and T.Col2 = @Param2
OPTION(QUERYTRACEON 4136)