Fast query runs slowly in SSRS

Question

I have an SSRS report that calls a stored procedure. If I run the stored procedure directly from the query window, it will return in less than 2 seconds. However, the same query that runs from the 2005 SSRS report takes up to 5 minutes. This happens not only at the first start, it happens every time. In addition, I do not see this problem in other environments.

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

Answer 1

Thanks for the suggestions here. We found a solution, and it turned out to be related to the parameters. SQL Server created a minimized execution plan when executed from an SSRS report due to "sniffing parameters." The workaround was to declare the variables inside the stored procedure and assign input parameters to the variables. Then, the query used variables, not parameters. This resulted in the query being executed sequentially, invoked from SQL Server Manager or through an SSRS report.

Answer 2

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

This will cause the report to run almost as fast as the stored procedure.

Answer 3

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

What an annoying workaround! Nevertheless, thank you all for bringing me to justice!

Answer 4

I had the same problem, here is my description of the problem

"I created a warehouse procedure that will generate 2200 rows and will run in almost 2 seconds, but after calling the storage procedure from SSRS 2008 and running a report that it never actually ran, and eventually I need to kill BIDS (Business Intelligence Development Studio) from the task manager. "

What I tried: I tried to run SP from the login to Reportuser, but SP also worked fine for this user, I checked Profiler, but nothing worked.

Decision:

Actually, the problem is that even if the SP generates the result, but the SSRS mechanism takes time to read these many lines and return them back. So I added the WITH RECOMPILE parameter to SP and ran the report .. it happened when a miracle happened and my problem was resolved.

Answer 5

I had the same scenario ... The main base report, SP (which takes only 1 parameter), took 5 seconds to return 10K records, but the report would take 6 minutes. According to the profiler and the RS ExecutionLogStorage table, the report spent all the time on the request. Comment by Brian S. led me to a solution. I just added WITH RECOMPILE before the AS expression in SP, and now the report time pretty much matches the SP runtime.

Answer 6

I simply did not select “Repeat heading columns on every page” in the Tablix properties.

Answer 7

If your stored procedure uses linked servers or openquery , they can start quickly on their own, but SSRS takes a lot of time. Some general recommendations:

• Get data directly from the server where the data is stored, using a different data source instead of using a linked server to retrieve data.
• Download data from a remote server to a local table before running the report, while maintaining a simple report request.
• Use a table variable to first retrieve data from a remote server and then join your local tables instead of directly returning a connection to the linked server.

I see that the question has been answered, I just add this if someone has the same problem.

Answer 8

I ran into a similar problem with my stored procedure, running quickly from Management Studio, but very slow with SSRS. After a long struggle, I solved this problem by physically destroying the stored procedure and recreating it. I am not sure about the logic of this, but I assume that this is due to a change in the structure of the table used in the stored procedure.

Answer 9

I had a problem with the output of an html report in a report that received 32,000 rows. The request was fast, but the output to the web browser was very slow. In my case, I had to activate Interactive Paging so that the user could see the first page and be able to generate an Excel file. The advantage of this solution is that the first page appears quickly, and the user can generate export to Excel or PDF, the cons is that the user can scroll only the current page. If the user wants to see more content, he should use the navigation buttons above the grid. In my case, the user accepted this behavior because exporting to Excel was more important.

To activate Interactive Paging, you must click on a free area in the report panel and change the "InteractiveSize" \ "Height" property at the report level in the "Properties" panel. Set this property as opposed to 0. I set 8.5 inches in my case. Also make sure you clear the "Keep together on one page, if possible" checkbox at the Tablix level (right-click on Tablix, then Tablix Properties, then General, Page Break Options).

Answer 10

I faced the same problem. For me it was just unselecting:

Tablix Properties => Page break option => Keep together on one page, if possible

SSRS report. He tried to put all the records on the same page, and not create many pages.

Answer 11

In addition to the sniffing-parameter issue, I found that SSRS is generally slower to process on the client side than (in my case) Crystal reports. The SSRS engine simply does not seem as capable when it has many rows for local filtering or aggregation. Of course, these are problems with the design of the result set, which can often be solved (although not always if the details are necessary for detailing), but all the more um ... mature ... the reporting mechanism is simpler.

Answer 12

In my case, I just had to disconnect and connect SSMS. I profiled the request, and the execution time showed 1 minute, although the request itself took less than 2 seconds. Rebooted the connection and started again, this time the duration showed the correct runtime.

Answer 13

A couple of things you can do without having to run the actual report just run sproc from the report services data tab. Still need time? Another option is to use SQL Profiler and determine what enters and exits the database system.

Another thing you can do to check it is to recreate a simple report without any parameters. Run the report and see if it has changed. Perhaps your RS report is corrupted or poorly formed, which can cause rendering to be very slow.

Answer 14

Had the same problem and fixed it by providing a common default dataset and updating this dataset on the report server.

Answer 15

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

I had a report with 3 groups by groups, and I noticed that the report works very slowly, despite the easy query, to such an extent that I can’t even type in the search field.

Than I deleted the groupings, and now the report grows in seconds, and everything works instantly.

Answer 16

I was able to solve this problem by deleting the embedded [& TotalPages] field below. The time when down from a minute to a second.

Something strange that I could not determine had an impact on the total page count.

I used SSRS 2012.

Answer 17

In our case, the code was not required.

Please note that in our help desk: "Cleaning up your Internet settings will fix this problem."

Perhaps this means "clear the cache."