Re: performance tuning of stored procedure

KK wrote:
> Is there any better way of writing the below stored procedure logic for
> performance tuning? It is taking time for large number of records.
>
> This has 2 for loops. The second loop gets executed (n-1) number of
> times for each record of count n. I appreciate your ideas?
>
> Thanks.

Dump the cursor loops and move to array processing. See the demo in Morgan's Library titled "FAST WAY" (www.psoug.org). Use FOR loops after the BULK COLLECT and before the FORALL to do work in memory.

If the ORDER BY clause is not required getting rid of it will help too.

I would expect getting away from single-row processing with a cursor loop to increase performance by at least 50X.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org