Sunday, September 22, 2013

Optimizing SRS report performance

Gather information
1. In SSMS navigate to ReportServer database
2. Execute the following query

DECLARE @path varchar(50) = '/<Folder>/<ReportName>'
SELECT CAST(convert(varchar(40),timestart,101) AS datetime) dt,
       avg(DATEDIFF(SECOND,timestart,timeend)) run_time_seconds,
       AVG( CASE TimeDataRetrieval WHEN -1 THEN NULL ELSE TimeDataRetrieval END ) / 1000 data_retrieval_seconds,
       AVG( CASE TimeProcessing WHEN -1 THEN NULL ELSE TimeProcessing END ) / 1000 processing_seconds,
       AVG( CASE TimeRendering WHEN -1 THEN NULL ELSE TimeRendering END ) / 1000 rendering_seconds,
       COUNT(*) num_runs
FROM ExecutionLog2
WHERE timestart > getdate()-30
  AND Reportpath=@path
GROUP BY CAST(convert(varchar(40),timestart,101) AS datetime)
ORDER BY dt DESC

Optimize based on the results
In my case my data retrieval is taking much alot of the time. Based on the tips from "My data takes too long to retreive in the link below" i did the following:
1. Take sorting out of the report and move it to the query

Resources
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

References
1. http://social.msdn.microsoft.com/Forums/sqlserver/en-US/6ef426ee-2111-49db-8d88-ba2b809ef2e1/ssrs-report-performance-optimization

No comments:

Post a Comment

There was an error in this gadget