Replacing a parameter in 2008 R2 SSRS

Question

I am using SSRS 2008 R2 and report builder 3.0. I have a cascading report issue that I need assistance with. The first report runs fine. Clicking on a link in the report passes a @processID parameter to the follow up report.

Now, when running the query with a string instead of the parameter directly in SSMS it takes under 1 second. When I run it through SSRS with the parameter it takes about 15 minutes. I've read that SSRS doesn't handle parameters very well. What I would like to do is find a way to change the parameter into a string and then send that or if anyone knows of a better way.

Below is the query the report is running:

SELECT     ResultDetail_View.processOid, ResultDetail_View.applicationId, outputItem.outputValue, ResultDetail_View.startTime, ResultDetail_View.resultStatus, 
                      ResultDetail_View.statusMessage, ResultDetail_View.endTime, ResultDetail_View.ErrRec, COUNT(Summary.Id) AS SumRec

FROM         ResultDetail_View LEFT OUTER JOIN
                      Summary ON ResultDetail_View.processOid = Summary.Id LEFT OUTER JOIN
                      outputItem ON ResultDetail_View.outputOid = outputItem.outputItemOid

GROUP BY ResultDetail_View.processOid, ResultDetail_View.applicationId, outputItem.outputValue, ResultDetail_View.startTime, ResultDetail_View.resultStatus, 
                      ResultDetail_View.statusMessage, ResultDetail_View.endTime, ResultDetail_View.ErrRec

HAVING      (ResultDetail_View.processOid = @processID)

ORDER BY ResultDetail_View.startTime

How are you running your query in the report? Is it a Stored Procedure? If so, this looks a lot like Parameter Sniffing which will hamper SQL Server's ability to pick the best query plan. — iamdave
Unfortunately these are inherited reports. These are in an ad-hoc format and not in a stored procedure. The code I posted is literally the dataset query that is run. I have not found parameter sniffing on this yet. I've done a recompile just in case and it still took the same time in both SSRS and SSMS. — bwilliamson
Have you run a trace on the sql server instance to see exactly what query is being executed on the server when the report runs. Once you have that, if you run it directly in SSMS on the same server you should get the exact same performance (whether that be good or bad). It's not an answer but it should at least help you investigate the issue. — Alan Schofield

iamdave iamdave · Accepted Answer · 2017-05-15T15:41:31

Regardless of the original issue, it looks like you can change your having to a where without changing the query result? If this is the case that will certainly help with performance.

Regarding the Parameter Sniffing, have you tried setting a local nvarchar variable and running the query this way in the report?

delcare @processIDLocal nvarchar(50) = @processID;

select r.processOid
      ,r.applicationId
      ,o.outputValue
      ,r.startTime
      ,r.resultStatus
      ,r.statusMessage
      ,r.endTime
      ,r.ErrRec
      ,count(s.Id) as SumRec
from ResultDetail_View as r
    left outer join Summary as s
        on r.processOid = s.Id
    left outer join outputItem as o
        on r.outputOid = o.outputItemOid
where r.processOid = @processID
group by r.processOid
        ,r.applicationId
        ,o.outputValue
        ,r.startTime
        ,r.resultStatus
        ,r.statusMessage
        ,r.endTime
        ,r.ErrRec
order by r.startTime;

Replacing a parameter in 2008 R2 SSRS

1 Answers