Fast query runs slow in SSRS

performance reporting-services report reportingservices-2005
user275554 picture user275554 · Feb 17, 2010 · Viewed 105.7k times · Source

I have an SSRS report that calls out to a stored procedure. If I run the stored procedure directly from a query window, it will return in under 2 seconds. However, the same query run from an 2005 SSRS report takes up to 5 minutes to complete. This is not just happening on the first run, it happens every time. Additionally, I don't see this same problem in other environments.

Any ideas on why the SSRS report would run so slow in this particular environment?

Answer

user275554 picture user275554 · Feb 17, 2010

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.

Related questions

Query runs fast but report renders slow: how to debug this?

I'm running SSRS 08. My query has four parameters (all varchars). I can run the query through management studio and it runs in under 10sec. I get the same performance if I run the query in Visual Studio's query designer. However...…

Read More
Which "href" value should I use for JavaScript links, "#" or "javascript:void(0)"?

The following are two methods of building a link that has the sole purpose of running JavaScript code. Which is better, in terms of functionality, page load speed, validation purposes, etc.? or function myJsFunc() { alert("myJsFunc"); } <a href="javascript:…

Read More
Fastest way to check if a value exists in a list

What is the fastest way to know if a value exists in a list (a list with millions of values in it) and what its index is? I know that all values in the list are unique as in this …

Read More