SSRS Performance Improvement – First experience

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!

  1. 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.]

PageBreakNote 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.

DocumentMap

Result:

DocumentMap2

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.]

Suggestion:

  1. select CONVERT(VARCHAR(11), T.DateABC, 106) as DateABC from Table1
  2. 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…

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s