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.
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.
I have an outdated version of CR that came with our bug tracking system. I really liked it (perhaps because we don't use it much and aren't fully familiar with all of its unique charms??) and wanted to upgrade. The upgrade was available for a fee which we would have been willing to pay if we just could have figured out what the improved features were. Unfortunately the company had changed hands, and all their literature was written in such terrible marketingese that we didn't feel that we had any useful information at all. Which is pretty remarkable, considering the sheer volume of it.
In the marketing writers' defense, I know what it's like to be asked to produce pages and pages of copy based on virtually no info at all. I just wonder who thinks it's a good way to sell.
Sorry for the hindleg. Here's some more hindlegging: the next version of our bug tracking system isn't going to use crystal reports. They're making their own reporting system. We are still trying to decide whether we want to pay for THAT upgrade… so far, all the specifics on the upgrade have also been written in marketingese.
Julie
I agree with you Chuck, Crystal Reports is an absolutely horrible piece of software. Every time I am forced to use it at work I cringe in horror at what havoc it will wreak upon me.
By the way, what's Crystal Reports?
Tom
Merry Christmas and Happy Hannukah to everyone reading this blog!
Well, Tom, normally I'd refer you to their parent company's website, but as I may have mentioned before, that won't tell you anything.
Crystal Reports is a once-nifty, now bloated and buggy, chunk of software that (when it works) extracts information from your database(s) and presents it all in a variety of textual and/or graphical formats, plus counts, subtotals, totals, summaries, etc. Whether or not the reports are useful and pretty depends a lot on the user who designs the reports and whether or not the software cooperates.
When I was unemployed and had no Crystal Reports experience, it would have been a good thing to have on my resume. It was THE database reporting software. I am not sure that CR is still held in such high esteem. I don't see it listed in job requirements as often now.
I've never held CR in high esteem. I've used it on different projects over the last eleven years or so going all the way back to the very early days of the product. IMNSHO Crystal is ass and has always been ass. There are many ways I might choose to describe Crystal. “Once nifty” isn't one of them.
Crystal may be the most widely known and used reporting package for databases, but it needs better competition, because it sets the bar way too low.
Fair enough, I'm a lightweight user and I'm comparing CR to basically nothing.
Several jobs ago, our product had its own reporting engine, which was of course flawless, but it's hardly fair to compare a homegrown, integrated DOS product to a one-size-fits-all Windoze app.
J.
If you want a better alternative, please try http://www.WindwardReports.com
Crystal Reports Sucks? NO! You got it somewhat wrong… Crystal Reports sucks, their support sucks, many web postings about how to use Crystal suck and are just plain wrong, and insult of insults when you do get any help from SAP for Crystal – its wrong. I have had 3 times now that I contacted Crystal Support and after going through their ridiculous support requirements (Auth code, version code, blood sample, urine test, and names of three close relatives!!!) ((kidding somewhat…)) on ALL THREE occassions, the information they gave me was wrong. Indeed, on my 3rd phone call to them, the tech told me the first two techs were “completely incorrect”, but then, so was the third guy!!!
I have almost 30 years in software development and without doubt, Crystal is the biggest Cluster F__k to ever come down the pike. These bozos couldnt find their way out of wet paper bag with map!!!
One thing Crystal is doing though – inspiring lots of us to just dump their crap product and make someone else the “King” of reporting.
I highly, highly, highly recommend Windward Reports, it is SO much easier than Crystal. I am sure there are other packages out there, but we use Windward and its AutoTag design tool (for template design) and it has made my life, and some of my co-workers lives tremendously easier
Yes just imagine if all the hard programming was aready done. I would suggest you give Stonefield Query a try. Fast, easy to use, and inexpensive compared to Crystal reports. Stonefield Query has all of the built-in Features you expect in an Business Intelligence enterprise reporting solution including a Report Scheduler, Role-based Security, Report Templates, Drill Downs, Exclusion/Inclusion Filters, Emailed Reports, Customizable SQL Select, Advanced Report Designer and Multiple Output Options (PDF, HTML, XML, XPS, Excel®, CSV, Word® RTF, DBF).
Now your sales people, administrative staff, and management can take advantage of the wealth of information stored in your database system when they need it. They can create elegant and persuasive Quick Reports, Charts/Graphs, Cross-Tabulation Reports and Labels (mailing, barcode, etc) in minutes with little or no technical knowledge.
Pingback: SQL Server Reporting Services | Stagelogboek Jan-René Deplorez