Re: pro C versus PL/SQL

From: Lolita <lolitagroux_at_hotmail.com>
Date: 27 Mar 2003 12:12:04 -0800
Message-ID: <8027ad1.0303271212.37e16679@posting.google.com>

Hi,

Thanks for your answers.

First I give you a better description of my system and after I answer your questions.

The program has two parts.

The first part is a client interface where the user can enter rules.

The format of a rule is like the where clause of a select statement. For example a rule would look like this:

         CieName=&#8217;test&#8217; and EmpNum=34

The user can enter up to 999 rules.

The user can use any fields of three specific tables in the database.

The second part is a batch process.

The goal of this process is to apply a specific action on the records that meet the criteria of a rule.

       The steps are the following:
1- Select the records from the three specific tables. For example: Select t1.name

	    T1.number
	    .
	    .
	    .(all the field of the three tables.)

from table1 t1,
	 table2 t2,
	 table3 t3

where key equalisation

        This select statement will select an average of 900000 rows.

2- A package is dynamically created to translate all the rules (around 300 can go up to 999) into elsif statements. This can&#8217;t be static because we never know how the rules are going to be.

Example of the elsif statement:
If t1.name=&#8217;test then

Elsif t1.number=89 and t1.name=&#8217;rt&#8217; then
.
.
.

And so on for all the rules.

3- Then the selected records (900000) will be submitted to the elsif statements to see if it meets the requirements of a rule. A record can only meet the requirement of one rule. So once a record hits a rule the action is done (update three fields of the record) and it get out of the elsif statement. Go through all the elsif statements is the longest part.

Here is an example of the testing results. This is the results with 267 elsif statements

Record selected= 900005
Number of rules=267
Number of updtates=60009
Time to apply the rules on the records=28.4 minutes Total time= 38.6 minutes

In C the total time= 2.95 minutes.

As you can see C is much faster.

At first I wanted do to it with native dynamic SQL using select statements but it was a big job to take care of the indexes. I thought this approach would be easier but I did not think it could take so much time.

Let me know if you have any ideas or questions. I am looking to your answers thanks.

Here the answers to your questions.

1- The batch process runs on the server. 2- Around 60000 records will match a rule. So it leaves 840000 records that have to go to all the elsif statements. I am not able to separate the data with one major condition or to have a condition that gets rid of the records that don&#8217;t apply to a rule&#8230; 3- We use Oracle 8i

Thanks again Received on Thu Mar 27 2003 - 14:12:04 CST