Updating table in SQL Developer with PHP too slow

Question

I want to UPDATE some hundreds of thousand of rows stored in Oracle database. For this I use PHP and transactions. But the whole process is extremely slow. This is what I do:

while (($row = oci_fetch_array($stid, OCI_ASSOC))) {

// define input for function
$id = $row['ID'];
$upstream_attn = $row['UPSTREAM_ATTN'];
$downstream_attn = $row['DOWNSTREAM_ATTN'];
$attainable_bitrate_adsl = $row['ATTAINABLE_BITRATE_ADSL'];
$stability = $row['STABILITY'];
$performance = $row['PERFORMANCE'];
$dsl_type = $row['DSL_TYPE'];
$snr_profile = $row['SNR_PROFILE'];

// execute function
$validationStatus =  validatePerformanceData($upstream_attn,$downstream_attn,$attainable_bitrate_adsl,$stability,$performance,$dsl_type,$snr_profile); // return true or false

if ($validationStatus== 'OK'){

    $stid_tmp = oci_parse($connection,"update " . ALUWS_DATA_HISTORICAL_TBL . " set VALIDATION_STATUS = 1 where ID = :id_bbn");
    oci_bind_by_name($stid_tmp, ":id_bbn", $id);
    $r_tmp = oci_execute($stid_tmp, OCI_NO_AUTO_COMMIT);

}
else{
    $y = $y +1;
}
}

oci_commit($connection);

//disconnect from database
oci_close($connection);

The whole process takes for ages. I have added in the oci_execute function the mode: OCI_NO_AUTO_COMMIT in order to avoid auto commit but this didn't make a big difference.

Any other suggestions are mostly welcome.

Can you give us some numbers to explain the magnitude of the effect you're seeing? "for ages" might be seconds, minutes, or hours. How many rows are being processed? How long do you believe it should take? At a minimum, assuming that ALUWS_DATA_HISTORICAL_TBL is a constant at least within the loop, you'd want to do your oci_parse once and just bind and execute multiple times within the loop. Where is the data in your array coming from? If you're pulling it from the database, it would be much more efficient to do a single update and let the database work in sets. — Justin Cave
– Justin Cave, Commented Jul 21, 2015 at 16:38
I am processing 2 million rows. When I comment the part of UPDATING then the rest it runs approx in 1 minute. Hmm indeed the data in the array are pulled from the DB. — user1919
– user1919, Commented Jul 21, 2015 at 16:42
What does validatePerformanceData() do? Is it a simple function? Also, what does the SQL for oci_fetch_array($stid, OCI_ASSOC) look like? What version of Oracle are you running? — MonkeyZeus
– MonkeyZeus, Commented Jul 21, 2015 at 16:50

Jessica · Accepted Answer · 2015-07-21 16:38:38Z

2

Instead of running each update individually, build an array of the IDs, and then after the loop, update the table where ID in (comma-separated list of values).

answered Jul 21, 2015 at 16:38

Jessica

6,99630 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

MonkeyZeus Over a year ago

I think this post should be taken into consideration especially since OP is pulling 2 million rows. stackoverflow.com/questions/1869753/…

Jessica Over a year ago

That wasn't explained when I posted, just to clarify.

MonkeyZeus Over a year ago

Duly noted. I didn't realize the time difference between the comment and your answer

user1919 Over a year ago

And thats the issue doing it this way: ORA-01795: maximum number of expressions in a list is 1000 in

Collectives™ on Stack Overflow

Updating table in SQL Developer with PHP too slow

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related