0

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.

user1919
  • 3,818
  • 17
  • 62
  • 97
  • what part takes `ages`? – cmorrissey Jul 21 '15 at 16:35
  • Executing the UPDATING part inside the first IF statement. – user1919 Jul 21 '15 at 16:36
  • 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 Jul 21 '15 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 Jul 21 '15 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 Jul 21 '15 at 16:50
  • That is not your oracle version, you've just given me your SQL Developer version. Please post the code for `validatePerformanceData()` and the SQL from `oci_fetch_array($stid, OCI_ASSOC)` – MonkeyZeus Jul 21 '15 at 18:14

1 Answers1

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).

Jessica
  • 7,075
  • 28
  • 39
  • I think this post should be taken into consideration especially since OP is pulling 2 million rows. http://stackoverflow.com/questions/1869753/maximum-size-for-a-sql-server-query-in-clause-is-there-a-better-approach – MonkeyZeus Jul 21 '15 at 17:04
  • That wasn't explained when I posted, just to clarify. – Jessica Jul 21 '15 at 17:59
  • Duly noted. I didn't realize the time difference between the comment and your answer – MonkeyZeus Jul 21 '15 at 18:12
  • And thats the issue doing it this way: ORA-01795: maximum number of expressions in a list is 1000 in – user1919 Jul 22 '15 at 05:38