SSRS Report Performance improvement

Question

I have an SSRS report generating 100000 data at once. When executing the report with different parameters, getting the data in 95 sec but when I executed the stored procedure which is used in the report it is generating the data in 6 sec. Am using simple queries to fetch the data in the procedure and have enough indexes on the tables.
And I'm using page breaks for the report. I have tried Cache process for the report which is not suit for the parameterised report. Is there any possible way to improve the performance of SSRS report other than Cache process. Please Suggest

Answer 1

Hi @Venkatesh Gunda ,

1. At first, you need to understand how long it takes to process reports and other usage metrics. You can capture data through ExecutionLog3 and see if the report is taking a long time in TimeDataRetrieval, TimeProcessing or TimeRendering. see more: Log Fields (ExecutionLog3). For performance issues at each stage, please refer to: Troubleshooting Reporting Services Performance Issues.
2. Report processing and rendering are memory intensive operations. When possible, choose a computer that has a lot of memory. For more information about how to mitigate performance issues by tuning memory management configuration settings, see Configure Available Memory for Report Server Applications.
3. Hosting the report server and the report server database on separate computers tends to provide better performance than hosting both on a single high-end computer.
4. If all reports are processing slowly, consider a scale-out deployment where multiple report server instances support a single report server database. For best results, use load balancing software to distribute requests evenly across the deployment.
5. If a single report is processing slowly, tune report dataset queries if the report must run on demand. You might also consider using shared datasets that you can cache, caching the report, or running the report as a snapshot.

For more information, please refer to: SQL Server Reporting Services Best Practices for Performance and Maintenance.

Best Regards,
Joy

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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.

Answer 2

I have an SSRS report generating 100000 data at once

A million rows at once? Have to check the duration for data query, processing and rendering from ExecutionLog3 view?
https://learn.microsoft.com/en-us/sql/reporting-services/report-server/report-server-executionlog-and-the-executionlog3-view?view=sql-server-ver16

Answer 3

Hi @Venkatesh Gunda
As joy said，you may check the memory usage of your computer，if it is too high when you open the report，you may expand your memory.
When you view a report, the whole processing time is divided into data retrieval, report processing, and report rendering. Then you can use the report execution log to find out witch stage consumes a long time, there is room for optimization, and then optimize for this stage. For example: keep reports as lean as possible, without many expressions, or column groupings.

Use ReportServer
select * from ExecutionLog3 order by TimeStart DESC

Here is official document about Report Performance，you may check：https://learn.microsoft.com/EN-US/previous-versions/sql/sql-server-2008-r2/bb522806(v=sql.105)?redirectedfrom=MSDN

Best Regards
Isabella