Is there a way to update 12000+ rows from txt file in less then 2mins?

Question

I need to update a table with more then 12000 row using php Codeigniter and a txt file.. reading the file and the foreach loop are fine but when updating line by line it takes like 30 mins, I guess the problem is I'm searching by name because I have no id in the txt file... Here is my code:

controller:

$fn = fopen($this->upload->data('full_path'),"r");

$update = true;
while(! feof($fn) && $update)  {
    $pieces = explode("|", fgets($fn));
    if(sizeof($pieces) == 9 && is_numeric(trim($pieces[1]))) {
        $update = $this->model_products->update3s($pieces);
    }
} 

fclose($fn);

Model:

public function update3s($product) {
    if ($product) {
        $product[2] = trim(str_replace("'","''",$product[2]));
        $product[1] = trim($product[1]);
        $product[6] = trim($product[6]);
        $product[3] = trim($product[3]);
        $sql = "UPDATE products set qty = $product[3], price_vente = $product[6]  where (name = '$product[2]')";
        echo $sql.'<br>';
        $update = $query = $this->db->query($sql);
        return $update;
    }

    return false;
}

@AkshitAhuja i have a primary key (ai int) but the only thing that's unique in the txt file is the name.. — nab, Jul 25 '19 at 12:32
Adds a unique index to the `name`, uses a parameterized request, prepares the statement before the loop — jcheron, Jul 25 '19 at 12:47
Some tips: * [Disable foreign key constraints in MySQL, temporary](https://stackoverflow.com/a/15501754/1461181) * [Use bulk inserts](https://stackoverflow.com/questions/779986/insert-multiple-rows-via-a-php-array-into-mysql) * [Use prepared statements](https://stackoverflow.com/a/60496/1461181) — odan, Jul 25 '19 at 13:01
You can merge multiple update statements with a CASE END cluase if you update one table something like `UPDATE table SET qty =(CASE WHEN name = 'product_1' THEN 1 ELSE NULL END) WHERE name IN(...)` ... Keep in mind to write/test this on "offline" data... Also using TRANSACTION can speed up multiple updates queries and not to forgot the indexing the column(s) which you use in WHERE clause of the update. — Raymond Nijland, Jul 25 '19 at 13:20

score 4 · Accepted Answer · answered Jul 25 '19 at 13:01

You can use transaction and add index for column name in database table.

$fn = fopen($this->upload->data('full_path'),"r");

$update = true;

$updatedCount = 0;

while(! feof($fn) && $update)  {
    $pieces = explode("|", fgets($fn));
    if(sizeof($pieces) == 9 && is_numeric(trim($pieces[1]))) {
      if ($updatedCount == 0) {
         $databaseInstance->beginTransaction();
      }
      $update = $this->model_products->update3s($pieces);
      ++$updatedCount;

      if ($updatedCount > 500) { //in one transaction update 500 rows
         $databaseInstance->commit();
         $updatedCount = 0;
      }
    }
} 

if ($updatedCount > 0) { // if we have not commited transaction 
   $databaseInstance->commit();
}

fclose($fn);

odan · Answer 2 · 2019-07-25T14:27:39.247

3

Some tips

Add index to field name
Use prepared statements
Disable the MySQL forgeign key check Read more

edited Jul 25 '19 at 14:27

answered Jul 25 '19 at 13:04

odan

4,757
5
20
49

Swarna Sekhar Dhar · Answer 3 · 2019-07-25T13:15:41.177

writing sql function can do that even in much lesser time . using feature like :

in a mysql user defined function

CREATE FUNCTION update3s(hole_file_content LONGTEXT) RETURNS Boolean
BEGIN 
                    -----Your implementation(same logic in sql ) ------ 
END

then coll it just by if it is CI 3

$this->db->call_function('update3s', file_get_contents($this->upload->data('full_path')));

else

$this->db->query("select update3s(".file_get_contents($this->upload->data('full_path')).")");

Is there a way to update 12000+ rows from txt file in less then 2mins?

3 Answers3