Last week I had to check a slow executing report. It was an SSRS 2016 report. I am not that familiar with the system so I took the task as a challenge.
The execution time of the report was around 35 seconds. This time was very unrealistic based on the volume of the used dataset. My first task was to start removing queries one by one (and running the report) so I can catch a bad optimized one. Surprisingly there was no such a query. The queries were not perfectly optimized but not badly written. Also, it was interesting that when the report was executed in Visual Studio (2017) there was no problem. The execution was slow only by using the SSRS Web portal or our system integration. Therefore, I said to myself – “this is strange”. At this point, I knew that there is some other problem and more optimization of the queries will not get me anywhere.
Like any self-respecting developer, I started looking online for solutions :). I found some similar cases but all of them included parameter sniffing and stored procedures. I did not have any stored procedures. All of the queries were running over tables (not even views) with parameters directly passed by the SSRS report viewer.
While looking over the queries, something hit me. The report was using over 20 datasets with different queries. I did not know if this is normal (as I said earlier, I am not familiar with SSRS). It was experimentation time. I copied 5-6 of the biggest queries in one dataset. I set the dataset to return only the result of the last query but to execute all of them. After that, I deleted all copied datasets and their usage in the report elements. Voila! The report execution time dropped to 15 seconds. I had some overhead from all these datasets and queries.
Knowing that I started looking for a way to execute queries in batches (is this even possible in SSRS 2016?). While searching I found this option in the data source settings. I am sure you can guess what happened after I checked it… The original report (not the one edited by me) started to execute for 7-8 seconds (initially 35). After some query optimizations, I managed to reduce the execution time to 3 seconds.
I do not know if this is valid for other versions of SSRS but it worked on SSRS 2016 (MS SQL Server 2017).
