September 5, 2018 at 3:14 pm
I wanted to see if anyone else had experienced the problem I'm currently dealing with.
Our reporting services overall perform very well. Out of 88+ reports we currently have 3 that are having performance issues. The query for each report returns in a matter of seconds when run in SSMS, however, when the report is used it is taking over 10 minutes to display the data.
In my first report I found that when I removed some of the joins and criteria from the query (which runs in 20 seconds) the report executed in less than a minute. I believe this second report I am looking at has the same issue because the query runs in 20 seconds returning 4256 rows. The report which has no grouping or any special functions in it takes 38 minutes to display the data in a simple table format.
I have started trying to optimize the query for this second report but I still do not understand why changing the structure of the query is affecting the performance of the report.
September 5, 2018 at 3:39 pm
yoyodynedata - Wednesday, September 5, 2018 3:14 PMI wanted to see if anyone else had experienced the problem I'm currently dealing with.
Our reporting services overall perform very well. Out of 88+ reports we currently have 3 that are having performance issues. The query for each report returns in a matter of seconds when run in SSMS, however, when the report is used it is taking over 10 minutes to display the data.
In my first report I found that when I removed some of the joins and criteria from the query (which runs in 20 seconds) the report executed in less than a minute. I believe this second report I am looking at has the same issue because the query runs in 20 seconds returning 4256 rows. The report which has no grouping or any special functions in it takes 38 minutes to display the data in a simple table format.
I have started trying to optimize the query for this second report but I still do not understand why changing the structure of the query is affecting the performance of the report.
There are different report components to look at such as data retrieval, processing, rendering.
Take a look at the ExecutionLog view in the ReportServer database. You can find where the most time is spent. This blog walks you through using ExecutionLog2 for tuning but you can also use ExecutionLog3 which is more current.
ExecutionLog2 View – Analyzing and Optimizing Reports
It can help to look at the trends with those times for individual reports and sort those by Time start (or end)
Sue
September 13, 2018 at 3:49 am
Check to see if the Interactive page height is set to 0, this can slow down any page rendering.
Gordon Barclay
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply