Slow in SSRS but fast in SSMS

Question

I have a query that runs fast in SSMS but runs very slow in SSRS and As I was searching for a solution, I came about this solution below provided by user275554

"Thanks for the suggestions provided here. We have found a solution and it did turn out to be related to the parameters. SQL Server was producing a convoluted execution plan when executed from the SSRS report due to 'parameter sniffing'. The workaround was to declare variables inside of the stored procedure and assign the incoming parameters to the variables. Then the query used the variables rather than the parameters. This caused the query to perform consistently whether called from SQL Server Manager or through the SSRS report".

My Problem is that I tried assigning parameters to the variables but it seems I really don't know how to do it so the report didn't produce any data.

An example of what I tried is this:

CREATE PROC MissingData 

AS 

DECLARE @Office varchar (200)
DECLARE @employee varchar (100)

SET @Office = @Office -- @office is the parameter from SSRS
SET @employee = @employee-- @employee is the parameter FROM SSRS

Can someone help me on how to assign the parameter to use the variables as provided by the solution.

Thanks Mi

can you add the link to that answer from where you got this solution ?? — M.Ali, Jan 05 '14 at 01:21
The link where I got this solution is http://stackoverflow.com/questions/2283943/fast-query-runs-slow-in-ssrs — user2809139, Jan 05 '14 at 17:51

score 0 · Answer 1 · answered Jan 05 '14 at 12:30

See this article for a good explanation of parameter sniffing, performance problems and solutions.

Since it's a reporting procedure my guess is the easiest way to get around this is to add WITH RECOMPILE to the CREATE PROC statement, which means SQL Server will recompile the proc each time it's run and therefore will work out the best query plan based on the parameters you're calling it with, rather than parameters you previously called it with.

score 0 · Answer 2 · answered Apr 23 '16 at 23:02

This is the right format to pass the parameters to the Stored Proc and then create the local variables to prevent parameter sniffing. As was mentioned, you can also add WITH RECOMPILE

CREATE PROC MissingData 
    @Office varchar(200)
    ,@employee varchar(100)
WITH RECOMPILE
AS 

DECLARE @ramOffice varchar (200)
DECLARE @ramEmployee varchar (100)

SET @ramOffice = @Office -- @office is the parameter from SSRS
SET @ramemployee = @employee-- @employee is the parameter FROM SSRS

Slow in SSRS but fast in SSMS

2 Answers2