Home » SQL & PL/SQL » SQL & PL/SQL » What r the ways of reducing cost & time?
What r the ways of reducing cost & time? [message #248135] Thu, 28 June 2007 02:34 Go to next message
fastfreeeasy
Messages: 25
Registered: June 2007
Junior Member
A PL/SQL procedure is being used to generate a report from Crystal. There are tables having master-detail relationship. I want all master records in the report. Data may not exist in child table. Report generation is taking 20 min. to execute the procedure. How can I reduce time?

I already applied the following tricks:
1. An outer join with master table
2. Explicit cursor for child table (having 1,00,00,000) records
3. Temporary table to insert filtered record from child table to global temporary table.

Is there any other way to reduce cost & time?
Re: What r the ways of reducing cost & time? [message #248142 is a reply to message #248135] Thu, 28 June 2007 02:58 Go to previous message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't think you need all the children for all the masters.
So wait for user ask for the children of a master key to ask for them.

Regards
Michel
Previous Topic: TO_CHAR(TO_DATE()) error...!!!!
Next Topic: passing table name and column names as parameter to procedure
Goto Forum:
  


Current Time: Fri Dec 02 12:44:21 CST 2016

Total time taken to generate the page: 0.10128 seconds