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?

Yes, it has about 9 parameters. The report parameter types match the stored procedure parameter types. — user275554, Feb 17 '10 at 20:06
Feel free to accept your own answer so the question can be used as by duplicate questions. — Dale K, Jul 03 '19 at 03:12

score 113 · Answer 1 · answered Feb 17 '10 at 21:47

113

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.

answered Feb 17 '10 at 21:47

user275554

2,061
2
13
6

This is a known issue of SQL Server... not actually related to SSRS itself. – AlexCode Mar 06 '13 at 13:33
Grrr! Thanks for posting solution and issue. It was optimizing correctly Fri, come in and it's DOA, reassign params to new vars and it's back to normal. – Volvox Jun 10 '13 at 22:12
3

Rather than reassign all the variables you can also mark the query OPTIMIZE FOR UNKNOWN. See http://stackoverflow.com/questions/12979493/multi-tenant-sql-server-databases-and-parameter-sniffing – Mike Mar 06 '14 at 20:55
1

This worked for me as well. I only had to reassign **one** of four variables to make it work. – Darren Griffith Nov 02 '15 at 22:22

score 16 · Answer 2 · edited Nov 19 '11 at 22:04

16

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

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

edited Nov 19 '11 at 22:04

animuson

50,765
27
132
142

answered Mar 23 '11 at 18:51

Brian Smedley

161
1
2

This will actually recompile the stored procedure every time you run it. I'm not sure if it's a good solution as it kind of defeats the purpose of stored procedures. – Yodacheese Oct 01 '13 at 05:05
6

If there is an individual query that is running slow, adding an option(recompile) at the query level can make a big difference. Just remember: recompilation = optimization. If you need the query to run in a very short time (100 ms or under, for example), then recompilation time might be unacceptable, and bypassing parameter sniffing may work better for you. But on large reports that take minutes to complete, the cost of recompilation is negligible compared to the penalty of having a bad query plan. – Paul Williams Feb 25 '14 at 15:07

score 14 · Answer 3 · answered Sep 20 '11 at 18:52

14

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!

answered Sep 20 '11 at 18:52

JHFB

411
1
5
19

3

Thank you for sharing this. I was also doing the non-stored procedure method and this just saved me a lot of time. – Bob Horn Nov 23 '12 at 17:52
1

parameter sniffing; esp if front end has bunch of params. re-setting them to internal params did the trick for me in past hence i vote for this answer. Re-compiled option did not work in my case of having 8 report params ; 3 with multi-parameter value(s). – junketsu Nov 16 '18 at 15:25

score 12 · Answer 4 · answered Oct 18 '12 at 21:32

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

"I created a store procedure which would generate 2200 Rows and would get executed in almost 2 seconds however after calling the store procedure from SSRS 2008 and run the report it actually never ran and ultimately I have to kill the BIDS (Business Intelligence development Studio) from task manager".

What I Tried: I tried running the SP from reportuser Login but SP was running normal for that user as well, I checked Profiler but nothing worked out.

Solution:

Actually the problem is that even though SP is generating the result but SSRS engine is taking time to read these many rows and render it back. So I added WITH RECOMPILE option in SP and ran the report .. this is when miracle happened and my problem got resolve.

score 6 · Answer 5 · answered Aug 18 '11 at 12:59

I had the same scenario occuring..Very basic report, the SP (which only takes in 1 param) was taking 5 seconds to bring back 10K records, yet the report would take 6 minutes to run. According to profiler and the RS ExecutionLogStorage table, the report was spending all it's time on the query. Brian S.'s comment led me to the solution..I simply added WITH RECOMPILE before the AS statement in the SP, and now the report time pretty much matches the SP execution time.

score 5 · Answer 6 · edited Oct 07 '12 at 22:12

5

I simply deselected 'Repeat header columns on each page' within the Tablix Properties.

edited Oct 07 '12 at 22:12

matthias krull

4,262
3
29
54

answered Oct 07 '12 at 22:05

Antony

51
1
1

Yes, before deselecting my report rendered in approximately 10 seconds now it renders in 2 seconds. – Antony Oct 07 '12 at 22:25

score 5 · Answer 7 · answered Mar 24 '14 at 18:52

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.

score 5 · Answer 8 · answered Jun 01 '16 at 14:13

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”).

score 4 · Answer 9 · edited Oct 09 '16 at 16:31

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.

edited Oct 09 '16 at 16:31

marc_s

675,133
158
1,253
1,388

answered Jan 19 '11 at 17:10

jay

41
1

score 3 · Answer 10 · edited Oct 11 '12 at 09:14

3

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.

edited Oct 11 '12 at 09:14

Stephan

37,597
55
216
310

answered Jul 18 '12 at 18:42

Joanne

31
1

score 3 · Answer 11 · answered Sep 24 '13 at 19:48

Aside from the parameter-sniffing issue, I've found that SSRS is generally slower at client side processing than (in my case) Crystal reports. The SSRS engine just doesn't seem as capable when it has a lot of rows to locally filter or aggregate. Granted, these are result set design problems which can frequently be addressed (though not always if the details are required for drilldown) but the more um...mature...reporting engine is more forgiving.

score 3 · Answer 12 · answered Aug 11 '17 at 21:37

In my case, I just had to disconnect and connect the SSMS. I profiled the query and the duration of execution was showing 1 minute even though the query itself runs under 2 seconds. Restarted the connection and ran again, this time the duration showed the correct execution time.

score 1 · Answer 13 · answered Feb 17 '10 at 20:07

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.

score 1 · Answer 14 · answered Aug 12 '14 at 22:49

1

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

answered Aug 12 '14 at 22:49

Solomon

11
1

score 1 · Answer 15 · answered Jul 17 '16 at 05:59

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.

score 1 · Answer 16 · edited Jul 03 '19 at 03:10

1

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.

edited Jul 03 '19 at 03:10

Dale K

16,372
12
37
62

answered Apr 20 '17 at 17:04

MiguelSlv

9,553
7
72
127

score -1 · Answer 17 · answered Mar 29 '17 at 19:44

-1

In our case, no code was required.

Note from our Help Desk: "Clearing out your Internet Setting will fix this problem."

Maybe that means "clear cache."

answered Mar 29 '17 at 19:44

user7788038

7
1

Fast query runs slow in SSRS

17 Answers17

Linked