Well, I would like to share my personal first experience on SSRS performance improvement. The story began with getting complaints by the user on the long waiting time in report generation. Loading… Loading… Loading… then time out error?!! Holy Happy!
I have found a useful Link regarding SSRS best practise, not all of them could be applied to my SSRS report, eventually, by applying a few of them, the report generation time was significantly reduced!
- Modify page break properties. Please ensure the KeepTogether properties for Tablix is also False. [Displaying all data on a single page will typically result in a performance issue. The report will not display any data until it completes the report processing and rendering.]
Note that you have multiple rectangles in a report, and originally they are one rectangle per page, now you might get crazy by setting this page break properties. Just imagine you have 100 pages in the first rectangle, are you going to click 100 times before reaching the second rectangle? No worries, I would advise using document map, in each rectangle properties, set the DocumentMapLabel as the screenshot below.
2. Avoid data conversion or having the custom format on the report example format date with dd-MMM-yyyy. Instead of this, please enhance your query. [Suppose a string data type is being stored as a date value. If you need to perform date operations to create three different values for your report, you will have to convert the value three times. If you performed the data conversion at the query level then you could directly perform the date operation to get the three new values. This would reduce the burden on the report server and would make it easier to maintain.]
- select CONVERT(VARCHAR(11), T.DateABC, 106) as DateABC from Table1
- Get DateABC on the report without any formatting
A first attempt in optimising performance! Only 2 steps, my SSRS report is generated > 10x faster!
To be continued…