1

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.

DeadZone
  • 1,633
  • 1
  • 17
  • 32
  • Which browser are you using? We got timeout errors in IE that we didn't receive in Chrome for the exact same report. – Eric Hauenstein Aug 07 '13 at 20:35
  • I've been running it in IE9. But I just tried Chrome. In Chrome, the "processing" indicator (the whirling green circle) goes away after about 30 seconds or so, but the report doesn't display at all. However, if I export the report to excel, the export works as expected. You think this is a browser issue? – DeadZone Aug 08 '13 at 15:25
  • It could be, although in our case reports would render in 20 seconds in Chrome and time out in IE9. This only occurs for those specific parameters? Very strange. – Eric Hauenstein Aug 08 '13 at 15:58
  • Well, those particular parameters generate a recordset of over 20,000 records. The report is typically much smaller. My initial thought was that this was a rendering issue (hence adding the page breaks) but the SSRS Execution Log says that it's rendering in about 8 seconds. So I'm stumped. – DeadZone Aug 08 '13 at 16:38
  • One more comment... The fact that the report did not render in Chrome has already been addressed. [This question](http://stackoverflow.com/questions/5968082/ssrs-2008-r2-reports-are-blank-in-safari-and-chrome) deals with it nicely. – DeadZone Aug 08 '13 at 19:43

2 Answers2

1

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.

DeadZone
  • 1,633
  • 1
  • 17
  • 32
0

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

Mike Wright
  • 1
  • I take it that your SSRS server is the same machine (physical or virtual) as your SQL Server? If your SQL Server is consuming (nearly) all of your available resources, you should consider moving the SSRS server to a different machine. – DeadZone Aug 05 '14 at 18:32