How to mass update in Eloquent given an array of keys and values efficiently-CodePudding

I have a very large (millions of rows) database table where I need to add some missing data from a 3rd party to every row.

The data source has a 'reference key' which is my only way to map to the correct item in the table

Each row needs 1 number updated

I can loop through the 3rd party data source and perform an eloquent Update to each row using a unique identifier, but this is very slow from my tests:

Orders
  id,  reference_key,  new_value
  int, string,         double(8,2)

foreach ($xml as $row) {
    Order::where('reference_key', $reference_key)
        ->update('new_value', (float)$row->new_value);
}

Is there a more efficient way I can do this?

CodePudding user response：

I would do update statement to do all in once like this :

UPDATE OrderTable
INNER JOIN table_to_fill ON OrderTable.refkey = table_to_fill.refkey
SET OrderTable.value = table_to_fill.value

CodePudding user response：

Eloquent is very powerfull to manage complicated relationship, joins, eager loaded models etc... But this abstraction has a performance cost. Each model have to be created, filled and saved, it is packed with tons of features you don't need for this precise use case.

When editing thousand or even millions of records, it is highly inefficient to use Eloquent models. Instead you can either use the laravel Query builder or a row SQL statement.

I would recommend this approach:

$table = Db::table('orders');
foreach ($xml as $row) {
    $table->where('reference_key', $reference_key)
        ->update('new_value', (float)$row->new_value);
}

But you can also do something like this:

foreach ($xml as $row) {
    DB::statement('UPDATE orders SET new_value=? WHERE reference_key=?', 
    [(float)$row->new_value, $reference_key]);
}

It will cut down your execution time significantly but the loop over millions of XML lines will still take a long time.