question

yferguson1-4346 avatar image
0 Votes"
yferguson1-4346 asked yferguson1-4346 commented

Post SSRS 2012 to 2016 migration, Excel rendering of large reports is very slow

We recently migrated from SSRS 2012 to SSRS 2016. We have a few large reports ( 45 - 74 columns that typically returns 30k - 100k records), that took up-to 1 minute to render to Excel on the 2012 SRRS server, but is now taking 6+ minutes to render on the SSRS 2016 server.

Researching different articles online suggested ensuring that Interactive Size is not set to 0, which I confirmed is not the issue. I queried ExecutionLog3 and the results indicate that the most time is spent on rendering the report (i.e. TimeRendering column has the highest value)

The report is calling a stored procedure which is performing a select statements with 4 filters. The report is not performing any complex calculations, groupings, etc on the ssrs server side. The only special formatting in the report is the background coloring for the tablix column headers. I tried rebuilding the report in VS2017 for SSRS 2016 server, but the rebuilt report appears to run longer.

Both the SSRS 2012 and SSRS 2016 servers have 8GB of memory. On the SSRS 2012 server, SQL Server is assigned 4GB of memory. But, on the SSRS 2016 server, SQL Server is assigned 6GB of memory.

The reporting services and db servers are separate. I confirmed that the issue is not related to other large reports running at the same time.

Does anyone have any ideas on changes to the reports or changes to the 2016 SSRS server we should try to get the report to render quicker, similar to the rendering time on the 2012 SSRS server? Please note that reducing the number of columns is not an option according to our business users.

sql-server-reporting-services
5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Joyzhao-MSFT avatar image
0 Votes"
Joyzhao-MSFT answered yferguson1-4346 commented

Hi @yferguson1-4346 ,
Did you rebuild the report for the SSRS 2016 server in VS2017 and then deploy the report to the report server? If you upload the report definition file directly to the report server 2016, it means that the report has not been upgraded, and the report definition remains in the original structure. However, once the report is opened in the report designer of Visual Studio 2017, the report definition will be upgraded to the currently supported RDL schema. So, you first make sure that the report has been upgraded. Please refer to: Upgrade Reports (SSRS)

We could first enable execution logging, and then reopen the report (Or run it in report builder) on SSRS2016 and SSRS2012 with VS 2017. After that, run the following statement, and then combine the results with TimeStart / End, TimeDataRetrieval, TimeProcessing and Compare the value of TimeRendering:

 Use ReportServer
 Select * from ExecutionLog3 order by TimeStart DESC

After determining whether the delay time is in data retrieval, report processing or report presentation, please refer to: Troubleshooting Reports: Report Performance
It involves: My data takes too long to retrieve, My report takes too long to process, My report takes too long to render, Design Tips for Optimizing Report Processing.

Regards,
Joy

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


· 3
5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

yferguson1-4346 avatar image yferguson1-4346 ·

Hi Joyzhao-MSFT,

I added the report to VS 2017 for SSRS 2016, which upgraded the report to 2016. I also tried rebuilding the report from the ground up in VS 2017. When I run the report from the report server, TimeRendering is the largest value (6mins+) in ExecutionLog3. (Note: the data retrieval and time processing combined are under 1 minute.) I checked that page breaks are enabled on the report. Since the report does not have any merged fields, that's not an issue. I played around with the page breaks and the page size, but no luck there.



0 Votes 0 ·
MattSliva-7601 avatar image MattSliva-7601 yferguson1-4346 ·

Hi! It has been a few months. Have you found a solution for your problem? I am encountering a similar problem to yours. We've upgrade our Report Builder from 2012 to 2019 and now experience slow Excel exporting.

0 Votes 0 ·
yferguson1-4346 avatar image yferguson1-4346 MattSliva-7601 ·

Our issue has not been fully resolved. Our SQL DBAs opened a ticket with Microsoft SSRS support team. Their suggestions were migrate the reports to SSRS 2016, add additional memory to the SSRS server, and then update the SSRS server to the latest release (installing all CUs etc). Our DBAs are working on updating the SSRS servers. Hopefully, that will resolve the issue.

0 Votes 0 ·