Fast query runs slow in SSRS

Question

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 1

Add this to the end of your proc: option(recompile)

This will make the report run almost as fast as the stored procedure

Answer 2

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.

Answer 3

DO you use "group by" in the SSRS table?

I had a report with 3 grouped by fields and I noticed that the report runed very slowly despite having a light query, to the point where I can't even dial values in the search field.

Than I removed the groupings and now the report goes up in seconds and everything works in an instant.

Answer 4

I came across a similar issue of my stored procedure executing quickly from Management Studio but executing very slow from SSRS. After a long struggle I solved this issue by deleting the stored procedure physically and recreating it. I am not sure of the logic behind it, but I assume it is because of the change in table structure used in the stored procedure.

Answer 5

I Faced the same issue. For me it was just to unckeck the option :

Tablix Properties=> Page Break Option => Keep together on one page if possible

Of SSRS Report. It was trying to put all records on the same page instead of creating many pages.

Answer 6

I will add that I had the same problem with a non-stored procedure query - just a plain select statement. To fix it, I declared a variable within the dataset SQL statement and set it equal to the SSRS parameter.

What an annoying workaround! Still, thank you all for getting me close to the answer!