Re: pro C versus PL/SQL

Lolita wrote:
>
> Hi,
>
> I am testing a part of code in pro C and PL/SQL. The code in C takes 2
> minutes to execute while the PL/SQL code takes 7 minutes.
> It would be really easier to code and manage if the program could be
> written in PL/SQL. I would like to know if it is possble to improve
> the PL/SQL performance.
>
> The code does the following:
> 1- Select the data from the database
> 2- For each record seleted it looks through a serie of elsif
> statement, if the record meets the criteria of one elsif statement,
> the data of this record is updated into the database. There can be up
> to 300 elsif statements.
>
> Number 2 takes most of the time.
>
> My questions are the following:
>
> 1- Why C is more performant than PL/SQL?
> 2- Would it be faster to use else if statments instead of elsif
> statments?
> 3- Would it be better to store all the records that have met a elsif
> statment into a PL/SQL table and then loop through the table to update
> them into the database? There can be up to 60000 records to put in the
> PL/SQL.
>
> Thanks in advance
> Lolita

The biggest performance killer in PL/SQL is commonly taking a procedural approach to a set-based problem. If the test's you are coding within the loop and relatively straightforward there may an opportunity to use a standard relational table and some judicious joins to acheive the same result.

Also, look at use of the bulk facilities in plsql to minimise the amount of row-at-a-time processing done

hth
connor

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"