Gawd I hate Crystal Reports. Today I got pulled out of my project to support someone who was running a Crystal Report that was taking well over an hour to complete…
When I ran the report myself I could see that it was definitely running appallingly slow, and though the record set was largish (order of about 10,000 rows) it was not overwhelming.
When such problems arise my first thought is typically “What did you expect? You're using Crystal Reports.” But since that isn't a very useful thought I put it aside and go looking at the SQL driving the report. The SQL behind this report looked pretty good, and it was all implemented with stored procedures (we call them SP's) on a SQL Server database. So I ran the SP's by hand in Query Analyzer, using the same parameters I had given Crystal, and they ran in 8 seconds.
Now theoretically I'm calling the same SP's Crystal is calling, passing the same parameters that Crystal is passing, on the same database. How do we get from 8 seconds to over an hour?
Eventually I fired up SQL Profiler which is a tool that will monitor a database and show you all the queries run against that database as they happen along with statistics. I was startled to find that one of the problems was that Crystal was calling the SP's multiple times… MANY multiple times… like 18 times.
There were two SP's being called from the report header, this is the part of the report which appears at the very beginning, and appears only once. One of these SP's was being called twice, and the other was called five times. There was no reason for them to be called more than once each.
Then there was an SP being called from the page header on the report. This information was being displayed once for each page. The report had 83 pages, but the information in the page header would not change throughout the report so there was no reason for this procedure to be called more than once. Although, Crystal being as flawed as it is, I wouldn't be surprised if it called that procedure 83 times… that at least, would have had an obvious (though stupid) explanation. Nope. This procedure was called 4 times.
Which then gets us to the body of the report. This report contained data grouped by year, and one of the procedures was invoked once for each year. These were the years I was running the report for: 2005. For the math challenged, that's one year, and hence there was no reason to call this SP more than one time. Instead it was called 3 times.
The other procedure appeared in the group footer and broke the data out by quarter. These were the quarters in the report: Q4-2005. One quarter of one year, and therefore no reason to invoke this procedure more than once. Which is why Crystal ran it 4 times.
I painstakingly went over the report at length to verify that there was no way we were asking Crystal to run these SP's multiple times. Nope. Crystal was making that decision all on its own.
One might think this was bad enough, but even if each SP took 8 seconds to run, running 18 SP's still adds up to only a couple minutes. WTF? Not only was Crystal executing and pointlessly reexecuting the same procedures, it was also somehow slowing them down to a crawl.
I spent ALL DAY debugging this. Eventually I discovered a workaround. Crystal can run reports in two ways, it can run on-demand where you wait for the report to finish and then look at the results, or it can run scheduled where you specify at what intervals the report should run, and you can check back later and look at the report for each run. I found that if I asked the Crystal Management Console to “run this report right now” it would take an hour to finish, but when I asked the Crystal Management Console to “schedule this report for later” when it ran, it would run in under a minute, even with the SP's being called a zillion times. So I had a workaround for the customer: schedule your report for later, schedule it to run once, tell it to run zero seconds from now.
And I had a great big bug report for Crystal. There is nothing quite so betarded as to offer two ways for running reports, one for people who are waiting around and want the results as quickly as possible, and one for others who will check in later, and then have the check-in-later folks get their results immediately and the I-need-it-now folks wait an hour.
Which is why Crystal Reports continues to suck donkey dork. It wouldn't be so bad if this was an isolated incident, but I have dozens of stories like that one. What a piece of crap Crystal is.