Monday, 20 May 2013

Poor performance when upgrading SSRS reports from 2005

I recently had to upgrade some SSRS reports from 2005 to 2012.  I installed the SSDT/BIDS upgrade for VS2012 and imported them straight in.  I did come across the following issues:

But otherwise it all went pretty smoothly.  However after a while we started to get feedback about reports either loading very slowly or, in some cases, crashing/freezing the browser completely.  I tested the offending reports and although they did cause a freeze of a few seconds in IE9, IE10, and Chrome on Windows 7 64-bit – they did eventually render.  However they caused Firefox to display an error message:

A script on this page may be busy, or it may have stopped responding. You can stop the script now, or you can continue to see if the script will complete.

Which prevented the report ever rendering and could only be made to go away by ending the process with Task Manager.  Also, running the report in VS2012 took over 2 minutes, whereas in VS2005 it only took a few seconds.  Exporting the report to a (MHTML) web archive did yield a slight difference in output size (the 2012 version was about 20% larger), but not enough to warrant the problems I was experiencing.  What was interesting was that exporting to Excel from either version (using the rs web service) was about 12-15 times faster than viewing in the browser.

What I eventually did (i.e. should have done in the first place) was to run a network capture with IE10 developer tools and identify the point of the freeze.  It was happening immediately after a call that was returning the body of the report – about 17MB of data.  My Internet connection is pretty fast so the download was only taking about 2 seconds, the freeze was happening after the download.  Understanding why took me back to the difference between SSRS 2005 and later versions.

Basically SSRS 2005 pre-rendered the whole report in cache before displaying.  So if you have a 100 page report then it would take as long to view it even if you only looked at 1 page.  SSRS 2008 and later versions only pre-rendered the visible page, thus placing less load on the server and being much more scalable.  So the fact that on a large report (1000 rows of data), SSRS 2005 was outperforming 2012 seemed counterintuitive.  What I failed to realise is that the problem was with the Report Viewer control.

The first versions of the Report Viewer displayed the report in an IFRAME – so it loads in the same way as every other web page, via a plain old HTTP request.  This is good when you want more data, less often – the whole page loads in one go.  The 2010 viewer uses Ajax (better for less data, more often), which means the whole body of the request has to be loaded into the JavaScript pipeline before it gets displayed in the browser.  Ajax isn’t intended for shoving large blocks of data around – it’s designed to break the process of loading a web page into easily manageable chunks.  So for multi-page reports it performs much better as the amount of content displayed at once is much less and it doesn’t have to reload the whole page.  However, 17MB on an Ajax request is a bit much (and very bad practice).  Too much, it would seem, for Firefox.  Certainly too much for older versions of IE that don’t use the Chakra engine.

So the solution is to introduce paging.  There are two ways of doing paging in SSRS:

  • Add page breaks
  • Set InteractiveHeight to a non-zero value

The difference is that page breaks will apply in all rendering extensions.  So a report with page breaks exported to Excel will put each page on a different worksheet.  Likewise exported to PDF it will put each page on a different page of the PDF (and often throw in some unwanted pages for good measure).

InteractiveHeight is what’s known as a soft page break.  It only appears in rendering extensions that are interactive (i.e. HTML in the browser).  For other extensions (e.g. Excel) it will render a single long report.  You should experiment to get the right size, both in terms of visuals and response size.  By cutting down my report to 20 items a page I reduced the response to 0.6MB and the rendering to 1 second.

Incidentally, multi-page reports don’t preclude you being able to do a search for words across the whole (i.e. visible and non-visible pages) of the report.  Using the find text box on the Report Viewer toolbar (as opposed to the browser’s search on page feature) will search the entire report.

I realise that this isn’t a solution as much as a workaround.  If you have money for bandwidth coming out of your ears and really must allow users to download 17MB web pages then some kind of hack that involves exporting to MHTML or otherwise being very clever with IFRAMEs and the reporting service web service, may be your only solution.

See also:

http://connect.microsoft.com/SQLServer/feedback/details/651339/ssrs-interactiveheight-0-and-ie-script-performance

http://www.bidn.com/blogs/MMilligan/bidn-blog/2903/ssrs-gotcha-interactiveheight=0

No comments:

Post a Comment