Over the past several weeks, I have been trying to find out why the Reporting Services report was taking so long to render. I just wrote my first Reporting Services report just a few weeks ago. I have administered Reporting Services for a few years but have never built a report. In this new job I took early this year, my first project with the new company was to create a cube and we would have reports executing against the cube. We hired a contractor to help me since this was the first cube where I would help build it.
We finished the project in time but since I was new and the contractor was helping, we did not always have all the facts. Right after he left, we deployed it to Production. Yes it was there. The processing of the cube is very fast, under 4 minutes average processing running over 80 stored procedures to gather the information. The cube processing time was excellent.
The data was in the cube very quick but running the reports was like watching paint dry on the wall. We only have 5 different reports. No matter how simple, the report was very slow. I could request a report and I could walk away from my desk, go get coffee or tea, talk to some other developers, return to my desk 8 minutes later and the report would still not be finished. The report has expandable columns and if we clicked to open an expandable part of the report you would have to wait another 5 minutes before you would see the expanded part of the report. As we all know, this is totally unacceptable.
I read all sorts of articles and blogs, nothing helped. We had two fact tables and I combined it to one Fact table. On the single Fact table, I added ColumnStore Index. This should fix the slowness of the report, right? No, the report was still slow.
While testing our product manager noticed images in the report to show either a check mark or a “x” mark. In the report we were using the gauge indicator to display the check mark or “x” mark depending upon the value in the database. Much to our dismay, the image coming from the indicator gauge was being rendered several hundred times on several of the reports causing the slowness. Not Good!
Solution to our slow running reports was to remove the gauge indicator and make the check mark and “x” mark a text value instead using the WindDings font. After changing all the reports to use text instead of a image, the reports rendered 300 times faster. No more slow reports, issue solved.