Laravel "Update or Create" From CSV: 5 Eloquent Performance Optimizations

When importing data from CSV or another source, you may need to check every record whether it exists in the DB already, and then update that record, otherwise create a new one. There are multiple ways to perform it.

General Non-Optimized Way

Imagine we're importing a CSV with 1000+ records of accounts from CSV.

The original array $accountsArrayFromCSV - column 0 is the account number, column 1 is the account balance:

 1array (
 2  0 =>
 3  array (
 4    0 => '610768',
 5    1 => '630',
 6  ),
 7  1 =>
 8  array (
 9    0 => '773179',
10    1 => '403',
11  ),
12  2 =>
13  array (
14    0 => '346113',
15    1 => '512',
16  ),
17  // ...
18);

For that import, you may see this code:

 1foreach ($accountsArrayFromCSV as $row) {
 2    $account = Account::where('number', $row[0])->first();
 3    if ($account) {
 4        $account->update(['balance' => $row[1]]);
 5    } else {
 6        Account::create([
 7            'number' => $row[0],
 8            'balance' => $row[1]
 9        ]);
10    }
11}

This code will work, as it does exactly what is needed: look for the records and then update/create them.

But there are two problems:

• The code is quite long and can be shortened with Laravel features
• The code launches too many DB queries - 1 or 2 queries for each record

If we take a look at what Laravel Debugbar shows, it's this:

In this case, I already have 100 accounts in the DB, and intentionally changed 2 records in the imported CSV: 1 with the new account number and 1 with the updated balance.

As you can see, it's launching select for every record, and insert/update for the new/updated records.

So, we have at least 100 DB queries or as many queries as the number of rows in the CSV source. Plus insert/update operations, so potentially up to 200 queries.

Here are the potential solutions.

Optimization 1. Readability with updateOrCreate()

The same code can be accomplished with...