CODEWITHSUNDEEP
sql-serverreporting-servicesssrs-2008
DeadZone
DeadZone

Reputation: 1680

SSRS Report takes longer to display than the Execution Long Indicates

I've got a report running on SSRS 2008 r2. The report is pretty simple tablix report; no subreports or anything like that, not even any subtotalling. The users want the report to be "clean" so that when they export it to Excel they can manipulate it easily. The query for this report is a stored procedure that accepts three parameters.

In development, it runs in about 30 seconds or less. The stored procedure completes in less time than that, depending upon the parameters chosen. (Client number, year and month.) It's been running on the production server for a few weeks, now. And the client has run it for a specific set of parameters there it takes about 30 minutes to complete! I've gone through the usual debugging. The SP takes about 9 seconds for those parameters. I've read the article about Parameter Sniffing, and that's not it. And I've checked the values in the ReportServer.dbo.ExecutionLog3 view. They show results like the following:

TimeStart:          2013-08-07 08:36:45.173
TimeEnd:            2013-08-07 08:37:08.943
TimeDataRetrieval:  11598
TimeProcessing:      3408
TimeRendering:       8171

All of that seems to indicate that the report completed and rendered in about 23 seconds. But it took about 30 minutes for the report to display on the web page! Now, this particular set of parameters returns about 20,000 records. And there is no paging since the users want this all on one worksheet when they export it to Excel. But just to see what would happen, I tried introducing some paging after every 100 records. When I did that, the report finished in under 30 seconds. (But now the Excel workbook has 200+ worksheets in it.)

Can anyone tell me why this is taking so long to display? According to the Execution Log, it's not data retrieval, it's not processing and it's not rendering. So what could it be doing?

Thanks,

Scott

EDIT: I forgot to mention that I also went into the "Processing Options" for the report and changed the Report Timeout setting. It was set to use the system default (which was 1800 seconds or 30 minutes). It's now set to "Limit report processing to the following number of seconds" 2700 (or 45 minutes.) The reason that the client complained was because when he tried to export the report to Excel he was getting an "rsExecutionNotFound" error, presumable due to the report's session timing out. The report still ran in 30 minutes and I still got the rsExecutionNotFound error. So I'm trying to fix the root problem.

Upvotes: 1

Views: 5177

Answers (2)

Mike Wright
Mike Wright

Reputation: 1

I ran into a similar problem....

My report extracts data from SQL2008 via a stored procedure.

Running the stored procedure in SSMS returns all the records in about 8 seconds, but trying to run the report results in having to end task on VS2008 after 30 minutes with no results returned.

I refactored the sp, removing a common table expression and substituting it with a temporary table, and the report rendered in under 30 seconds!

Our SQL server runs very hot - memory fully maxed out at 10Gb (I have no control over this) so it seems that using temporary tables (which get written out to TempDB) saved enough on resources to be able to render the report.

Hope this helps.

Cheers

Mike

Upvotes: 0

DeadZone
DeadZone

Reputation: 1680

I found the resolution. When I used the report wizard to build the report it placed the tablix inside of a list. (This allows it to generate more customized headers for the tablix as described in this blog post.) Apparently, if the list/tablix doesn't page regularly it takes forever to display the report in IE8+. (This other forum post suggests that Microsoft Premier Support said that the slowness is a bug with SSRS 2008.) Regardless, removing the list and just placing a tablix without the custom grouping header did the trick. the report renders correctly and within about 30 seconds or less.

Upvotes: 1

Related Questions