Accessing Database : What does that really mean?

[jenschmidt]

I've had many explanations of what my local PC is doing when I'm running a report and, immediately after clicking the lightning bolt, the bottom lefthand corner says "Accessing Database" - and all the explanations have been different. Does anyone know what is really being done at this point in the process?

(The reason I would like to know is because one of our reports is taking 2+ hours to complete this process and I would like to see if we can improve that.)

Thanks!
Jen jennifer.giemza@uwmf.wisc.edu

 

[lyanch]

Essentially, it means that Crystal Reports has sent the SQL request to the database and is waiting for a "done". What probably is more important is to watch the record count on the right hand side.

Does it say something like "2 of 100" or any non equal numbers? That would imply that at least some of your selection formula is not being sent to the database. To check to see exactly what SQL is being passed, check under database -> show SQL query. Is this what you expect it to be?

If it is then try running just the query outside of Crystal. Is it still slow? If so you need to work on the query itself.

If it isn't, then you need to amend your selection formula etc, until it is all passed via SQL.

Lisa

 

[synapsevampire]

Let us know the version of Crystal you're using, and the type and version of the database.


Then go under Report->Edit Selection Formula->Record and copy the text out of there and paste it in here.

Then go under Database-Show SQL Query and copy and paste that text in here.

Someone will probably be able help you out with the performance if you do.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[dgillz]

Does your report have any subreports? If so lets look at the nature of the subreports, and see if there is a way to create the report without using subreports. That will typically dramatically increase performance. Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[jenschmidt]

No subreports in the report, and the 2+hours is before it even starts the "Reading Records" portion of the process.

We are using Crystal 8.5 and Oracle (I'm not sure of the version of Oracle)

Selection Criteria:
{CLARITY_TDL.POST_DATE} in Date(Year({?End Date}), 01, 01) to {?End Date} and

//The next statement is just a sample of the 19 other
//pieces that are similar to this - we are using flexible
//grouping for the end-user.

(if {?Billing Provider}<> &quot;All&quot; then {@z Billing Provider} = {?Billing Provider} else True) and

{CLARITY_TDL.DETAIL_TYPE} < 39.00 and
(if {@Original Product Type} in [&quot;GHC&quot;, &quot;PPIC&quot;, &quot;Unity&quot;]
and {CLARITY_TDL.POST_DATE} <= Date(2002, 08, 31)
then {UWMF_CAP_PERCENT.PERIOD}=&quot;OLD&quot; else

if {@Original Product Type} in [&quot;GHC&quot;, &quot;PPIC&quot;, &quot;Unity&quot;]
and {CLARITY_TDL.POST_DATE} >= Date(2002, 09, 01)
then {UWMF_CAP_PERCENT.PERIOD}=&quot;NEW&quot; else True)

SQL Statement:
When I go to copy the SQL statement, the choice under Database is grayed out. This doesn't happen on any new reports I open, but for some reason won't show me the statement on this report and others like it - maybe it's due to the flexible grouping?

One person told me that during &quot;Accessing Database&quot;, the PC is simply sending a message to the server to verify that the server is actually there and the &quot;Reading Records&quot; starts the communication. Others have said &quot;Accessing Database&quot; grabs the data and &quot;Reading Records&quot; is passing the data back to the report.

Thank you everyone for your help! jennifer.giemza@uwmf.wisc.edu

 

[Naith]

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

http://www.quest.com/solutions/download.asp),

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