We had a Crystal Report for Billing that took over an hour to create 230 bills in PeopleSoft 9.1.
Before I continue, I should add that this was a bespoke report developed by off-shore resources. I should also add that I had been doing development QA for quite a while on this project when this issue came up. I was also responsible for application performance analysis and tuning.
I undertook a performance analysis of this report, fully expecting some badly written SQL to be the root cause as that was a common issue with our off-shore resources. However, what I found was even more incredible in my view. To explain:
The problem report used sub-reports to display Bill header notes. In itself, not a problem – in fact exactly what you would use a sub-report for. But … and it’s a big BUT … the underlying queries used by the sub-reports did not have any bind variables (prompts in Peoplesoft Query parlance). So, in order to ensure only the correct notes were shown on the bill, the developer opted to filter the data in the report. Clueless frankly, and really fundamental in my view. How did they think this would ever perform well? A keyed lookup should be, well, keyed! I’m 100% certain the developer didn’t even know what would execute behind the scenes. In fact, I think the whole team thought Crystal would miraculously add a WHERE clause to the query for them.
As the site had some 22,000 bill header notes and the header notes were shown in multiple sub-reports on the main report, this “approach” resulted in over 35 million bill header rows being processed to create 240 bills. Hardly surprising it took over an hour really. The fixed report took under a couple of minutes for over 600 bills. And it was only that slow because of issues with the underlying SQL – but that is an entirely different issue.
As I said, I had QA’d lots of reports for this project. And I never ceased to be amazed at some of the most basic design and coding mistakes off-shore resources made. In this case, my mistake, it would seem, was to assume that Crystal Reports developers actually know what they are doing at the fundamental level. How wrong I was eh?
Of course, once you find one of these problems you just have to lift that manhole cover and look for more don’t you? Well I wish I hadn’t. Every report that contained sub-reports they had developed had the same issue. Sigh.
Now I don’t want to tar all off-shore resources with the same brush, but my personal experience has not been good. Some of the resources are really good and some are very poor – there are hardly any average ones. One common thread seems to be a lack of experience – we have all been in that position so hardly the individuals fault. But the resources and their abilities are over-sold by salemen to (frankly) naive management who all believe that “developers/coders/report writers/DBAs are easy to find”. Not good ones.
You get what you pay for.