SQL Server Query Slows When Hosted Inside a Stored Procedure

Question

I have a query that I am inserting into a stored procedure. When I run a query with local variables, the query takes ~ 1 second to run. When I put the same request inside a stored procedure and call SP, it takes about 2 minutes to start.

From previous questions in SO, I think this might be related to parameterization. When I had this problem, before I declared local variables inside my SP and then used local variables. This has worked in the past, but doesn't seem to help me in this case.

I currently have

CREATE PROCEDURE dbo.ProcedureName @DIV VARCHAR(4), @STD VARCHAR(1), -- S or N @scen varchar(20) AS BEGIN DECLARE @DIV_copy VARCHAR(4), @STD_copy VARCHAR(1), @scen_copy varchar(20); SELECT @DIV_copy = @DIV, @STD_copy = @STD, @scen_copy = @scen; 

I also tried adding WITH RECOMPILE like this:

 CREATE PROCEDURE dbo.ProcedureName @DIV VARCHAR(4), @STD VARCHAR(1), -- S or N @scen varchar(20) WITH RECOMPILE AS BEGIN DECLARE @DIV_copy VARCHAR(4), @STD_copy VARCHAR(1), @scen_copy varchar(20); SELECT @DIV_copy = @DIV, @STD_copy = @STD, @scen_copy = @scen; 

In addition, I tried adding OPTION(RECOMPILE) to the end of my SP like this:

 SELECT * FROM #Output OPTION(RECOMPILE) END GO 

I also tried using:

 OPTION(OPTIMIZE FOR UNKNOWN ) 

As well as:

 OPTION(QUERYTRACEON 4136) 

In this case, I do not have the appropriate permissions to track requests.

None of the 5 fixes listed above fixes the problem, because the stored procedure still takes from 2 minutes to 2 minutes and 30 seconds for the same query, which takes 1 or 2 seconds outside the stored procedure.

All of these fixes are taken from this article. Various Approaches to Fixing SQL Server Sniffing Parameters

Has anyone had similar problems? Thank you for your time!

SQL Server 2008R2

Answer 1

Unfortunately, I did not understand why the same exact query had so many problems when placing inside a stored procedure compared to sql statements.

As a workaround, I spent some time optimizing my queries in SP. I realized that the one table I joined was very large (millions and millions of rows), so I grabbed the relevant data from a large table and created a temporary table to store it, and then joined the (much) lower temporary table and it seemed to do the trick. SP starts in 3-4 seconds.

I'm still a little new to SQL outside the basics, so I learn a lot. Although this serves as a reminder of a thorough study of your needs, there is often room for improvement. It looks like scotch tape and paper clips, but my problem has been resolved.

Thank you all for your input.