Re: pro C versus PL/SQL

On 23 Mar 2003 17:36:38 -0800, lolitagroux_at_hotmail.com (Lolita) wrote:
>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.
The PL/SQL code is being parsed at run time, that's why a straight translation from C to PL/SQL runs slower. These days (9i), you can compile PL/SQL code to a faster C representation, but that's another subject.

The PL/SQL table is a good idea. You can use the FORALL statement to zip through all the elements in the table and issue an UPDATE for each one, using the same subscript to reference the input table and tables of new values.

300 separate ELSIF conditions makes it sound like too much data is coming out in the first place. Do all the selected records get matched by one of the conditions? Seems like there should be some better way to get the data out in the first place:

Multiple queries, each selecting a smaller number of rows.

Select a computed value in the query that indicates which condition the row matches.

Include all the SELECT logic in the UPDATE statement so you don't have to do a separate query or store temporary results (UPDATE ... WHERE ROWID in (SELECT ROWID...)).

Build a function-based index so that the results of all 300 ELSIF conditions are precomputed for every row.

John

--
Photo gallery: http://www.pbase.com/john_russell/