Re: Improving performance of calculations in PL/SQL

"Paul Dixon" <root_at_127.0.0.1> wrote in message news:<bv31ol$bkp$1_at_visp.bt.co.uk>... _snip_
> Charlie,
>
> I suspect the difference in performance for the PL/SQL is down to the
> difference in CPU speed between your Intel based PC and the PA RISC (?)
> based HP server.
>
> Are you sure that the PL/SQL calculations are the bottleneck in your batch
> run ? From the performance you are getting on your development machine you
> should be able to get a couple of billion loops of the above code in 100
> hours !
>
> You didn't provide any details about how you get the data into the PL/SQL
> procedure or what you do with the output, but I'll make a wild guess and say
> that these are probably the areas taking most of the time in the batch run.
>
>
> Paul Dixon

I think you're right about the CPU speed. My 1.1MHz PC is faster than our bottom-of-the-range entry-level HP-UX box :-(

I've spent a lot of time analyzing the code. Each loop used to do a number of lookups based on year or age (i.e. integer) from tables. I loaded this data into PL/SQL tables in the package initialisation code so I could access the data directly using year or age as the index. Doing this sort of thing got me a 20% performance improvement.

Each loop now does no database lookups - only calculations using variables, pl/sql tables data from cursors (in outer loops). I've done lots of timings and the bottleneck is definitely in the loops.

In some of the loops, the calculations are a lot more complicated than my example and the total estimated number of loops performed is around 800 million!

Thanks for your input

CE Received on Tue Jan 27 2004 - 03:31:52 CST