I recently encountered a fairly significant performance issue with SQL Server Reporting Services 2008 R2 and Reporting Services Azure. After having built out an electoral report that broke down election results poll by poll, I used what I think is a fantastic new feature in Reporting Services 2008 R2, sparklines.
A sparkline is essentially a mini-graph – a visual representation of a single row of data. In my case, it is the results, by party (as indicated by colour) for a given poll. It looks like this:
Once I got this report looking the way that I wanted it to, I deployed it to Azure Reporting Services. However, when I ran the report, it took an incredibly long time to load (3 minutes and 30 seconds). I immediately blamed Azure RS for this, as it’s still a preview edition. However, further testing revealed the same behaviour on an on-premise deployment. The puzzling thing was that it rendered very quickly using the preview in BIDS or in Report Builder.
Adding to the mystery is the fact that the report renders fairly quickly when called from the Reporting Service web service to create a PDF file, or even an HTML file. I managed to discover this when I decided to pre-create a number of these reports in PDF format to reducre the load time for users (more on how I did this in an upcoming post).
The oddest part is precisely where the performance problem shows up. When the report is run using a browser, the browser thread’s CPU utilization goes up to 100% of available resources (a 2 core machine CPU runs up to 50%). This is happening on the client side.
On a hunch, I tried removing the sparklines. Presto, the load time dropped to 20 seconds. So I don’t get to use my sparklines in the live report which is unfortunate, but at least I found the culprit. What I wish I knew was why this was happening, or if there’s something I’m simply doing wrong.
If anyone has any ideas, I’m all ears.
2 Comments