Painfully Slow Report coming from SSRS 2016

0
votes

I am in the process of moving a batch of reports (26) from SSRS 2014 to SSRS 2016. One of them is absolutely painfully slow, but only in a browser ( both IE and Chrome ). The report depends on a query within the report (not a stored proc). I can determine no significant difference between this reports and its sisters, which return results in 3-4 seconds regardless of browser. The time the query takes to run in SSMS is comparable to times to run similar queries in sister reports. Here are some details. SSMS Query • Execution Plan:

• Returns 1124 Rows • Time Messages o SQL Server Execution Times:  CPU time = 15 ms, elapsed time = 261 ms. o SQL Server parse and compile time:  CPU time = 0 ms, elapsed time = 21 ms. o SQL Server Execution Times:  CPU time = 0 ms, elapsed time = 0 ms. Report Development Report Builder: • Runs well in 5 seconds or less. Visual Studio 2017: • Runs well in 7-10 seconds.   Browsers • Both IE and Chrome deliver results in about 9 minutes. Report Differences in the .rdl Files There are two obvious differences in the .xml files. SSRS 2016 1. DataSources and DataSets are defined at the top of the .xml 2.

SSRS 1. DataSources and DataSets are defined starting at line 4678. 2.

I have done about everything I can think of and have run out of ideas. Query language, .rdl files, and Execution Plan available on request.

1
reporting-servicesssrs-2008-r2reportbuilderssrs-2016
Check the execution logs and see what is taking the time, you'll get 3 times, Data Retrieval; Processing and Rendering. It might point you in the right direction. Query with something like... SELECT el.* FROM ReportServer..ExecutionLog el JOIN ReportServer..Catalog c on el.ReportID = c.ItemID WHERE c.Name = 'MyReportName' ORDER BY TimeStart desc – Alan Schofield
Great suggestion. This is what the results look like: TimeStart : 2018-03-14 13:53:24.940; TimeEnd: 2018-03-14 13:53:26.843; TimeDataRetrieval: 310; TimeProcessing: 458; TimeRendering: 1,118 – Steve_Malcolm
So that looks all OK, about 2 seconds to complete. Did it still take ages to render in the browser when you ran this? If so then I would look at what is happening in the browser. Not something I am experienced in but something like Fiddler might help track down browser/network issues. – Alan Schofield
F12 tools will tell how long everything takes in the browser. Though it's usually difficult to tell whats going on. You need to basically rebuild the report bit by bit until you identify the component causing the issue. You'll probably find you'll rebuild the whole thing and it'll work but you won't know why. – Nick.McDermaid

1 Answers

1
votes

Try disabling error and usage reporting for SQL Server through SQL Server Error and Usage Reporting. I had exactly the same issue, and the case was that reporting server didn't have internet access, but was trying to get some javascript file and would timeout after some time and that was the thing causing the delays in rendering.