Query taking too long

[urgent72]

HI,
I have a oracle stored procedure running through Crystal reports 10.The query is taking too long to retrive the data.Its taking like more than 2 hours to retrive the data.
Any suggestions on how to make is more robust and cut the time down??I am not a DBA and is there any way that i can track why it is taking too long and reduce the output time??any suggestions please.

 

[Turkbear]

Hi,
Try executing the SP from SqlPlus ( or have whoever coded it do so)..
Have the DBA monitor the excution plan for the SP's
actions..

If possible post the folowing:
The Version of Oracle
Method on connecting ( Native Driver, ODBC ( whose?) , etc)
The SP code..

 

[pgtek]

hi
i have 120 formula, and 15 views and tables and takes only 1.45 minutes to run, return 10,000,000 records
the problem your having is the linking
you getting loop somewhere
Try to crate a view and bring the table and make your link there then get the field you need for your report

cheers

pg

pgtek

 

[synapsevampire]

I'd guess that you have an inefficient SP.

Follow Turk's suggestions, and also make sure that you're using the Oracle Native connectivity. Your dba should be able to add indexes or suggest alternative means to improve the SP.

Crystal doesn't add much overhead to execution times, it's purely the driver used and the efficiencies of the database.

-k