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

Had the same problem, and fixed it by giving the shared dataset a default parameter and updating that dataset in the reporting server.

Answer 2

If your stored procedure uses linked servers or openquery, they may run quickly by themselves but take a long time to render in SSRS. Some general suggestions:

• Retrieve the data directly from the server where the data is stored by using a different data source instead of using the linked server to retrieve the data.
• Load the data from the remote server to a local table prior to executing the report, keeping the report query simple.
• Use a table variable to first retrieve the data from the remote server and then join with your local tables instead of directly returning a join with a linked server.

I see that the question has been answered, I'm just adding this in case someone has this same issue.

Answer 3

Couple of things you can do, without executing the actual report just run the sproc from within the data tab of reporting services. Does it still take time? Another option is to use SQL Profiler and determine what is coming in and out of the database system.

Another thing you can do to test it, so to recreate a simple report without any parameters. Run the report and see if it makes a difference. It could be that your RS report is corrupted or badly formed that may cause the rendering to be really slow.

Answer 4

I was able to solve this by removing the [&TotalPages] builtin field from the bottom. The time when down from minutes to less than a second.

Something odd that I could not determined was having impact on the calculation of total pages.

I was using SSRS 2012.

Answer 5

I had the report html output trouble on report retrieving 32000 lines. The query ran fast but the output into web browser was very slow. In my case I had to activate “Interactive Paging” to allow user to see first page and be able to generate Excel file. The pros of this solution is that first page appears fast and user can generate export to Excel or PDF, the cons is that user can scroll only current page. If user wants to see more content he\she must use navigation buttons above the grid. In my case user accepted this behavior because the export to Excel was more important.

To activate “Interactive Paging” you must click on the free area in the report pane and change property “InteractiveSize”\ “Height” on the report level in Properties pane. Set this property to different from 0. I set to 8.5 inches in my case. Also ensure that you unchecked “Keep together on one page if possible” property on the Tablix level (right click on the Tablix, then “Tablix Properties”, then “General”\ “Page Break Options”).