Hey Jen,
Everyone so far has underlined the significance of different things to take into consideration - and they're all right. I'm going to toss in my twopence now, so I hope you can keep track of all these points.
You mentioned that you can't reveal the generated SQL from Crystal. Where a selection criteria has been applied in the report, I've not known this to happen. But then, I've never written a report with 19 potentially wildcarded parameters. I'll come back to that later, but for now, it's essential that you find out what SQL is being processed by the database in order to get you your results. If you can't get it from Crystal, you'll have to get it from Oracle.
If you use TOAD (Tool for Oracle Application Developers, which you can download for a free trial at
you can view the SQL which is being processed by the database account being used by the Crystal user. If you don't have access to a product like TOAD, or SQL Nav, then pass the buck to your DBA who should be able to tell you what the SQL is. Watch this process and determine if the query itself is taking a long time on Oracle, before Crystal even gets back into the mix. As you're waiting 2.5hrs before you lose the 'Accessing Database' legend, I'm suspecting that Oracle is grumbling about what it's been given.
Back to the parameters now, if a lot of them are pointing at ALL, then, depending on join criteria, they're potentially going to be looking at entire tables - so you'll need to make sure you're taking advantage of indices where they're available. (Get your DBA to run an Explain Plan or Trace against your query to let you know if you're currently doing this.)
If you are using all your available indices, and the query still won't pull it's finger out, persuade your DBA to run an analyse script across the database. Broadly speaking, analysing the database basically tidies the database up, allowing queries to be processed quicker where possible. Then rerun the query in Oracle. (If you do this, remember to have the database unanalysed immediately afterwards, as other queries which were running fine might object to having an analyse suddenly pop up out of the blue.)
On the Crystal side, how much time elapses between the first time the status changes from 'Accessing Database' to 'Reading Records' to when the report actually finishes processing? If it's very little time, then this implies that little has to be tweaked on the Crystal end.
If it isn't already, try executing the report with the Database/Perform Grouping on Server option on, which may speed the report up a little, as it performs as much processing as it can on the server, passing only the bare essentials to your workstation.
Out of curiosity, what's with this:
{@z Billing Provider} = {?Billing Provider}
instead of
{Table.BillingProvider} = {?Billing Provider}?
What does {@z Billing Provider} do?
As far as the 'Accessing Database'/'Reading Records' mystery goes, your former explanation is a bit dubious in my books. I would tend to support your latter explanation myself. 'Accessing Database' to me is simply indicating when the ball is in the datasources court. This appears once connection has been made and the query is passing/passed across from Crystal. Once the datasource has completed processing and has records to return, Crystal can read the records. However I believe 'Reading Records' remains whether the database is currently returning the recordset, or if Crystal is applying it's own processing to the records once returned.
All the best with this,
Naith
