0

I have potentially 500,000 records to either insert or update into my database.

I'm using the updateOrCreate function in Laravel but it's still taking a very long time.

I'm current using a foreach loop wrapped in a database transaction but is there a better solution?

DB::transaction(function() use ($items, $client) {
        foreach($items as $item) {

                $data = array(
                    'external_id' => $item->external_id,
                    'comment' => $item->comment,
                    'code_id' => $item->code_id,
                    'client_id' => $client->id
                );

                Registration::updateOrCreate(
                    [
                        'user_id' => $item->user_id,
                        'date' => Carbon::parse($item->date)->format('Y-m-d'),
                        'session' => $item->session
                    ],
                    $data
                );
        }
    });
Tom
  • 12,776
  • 48
  • 145
  • 240
  • In Laravel 8 and later you can use [`upsert()` which accepts an array of records](https://laravel.com/docs/8.x/eloquent#upserts). – Don't Panic Aug 02 '21 at 11:54
  • Does this answer your question? [Laravel insert or update multiple rows](https://stackoverflow.com/questions/40863015/laravel-insert-or-update-multiple-rows) – Don't Panic Aug 02 '21 at 11:55
  • 1
    Sorry, should have elaborated - I expect `upsert()` with bulk sets of data will be faster than 1-by-1. I would suggest trying with different batch sizes and seeing what batch size performs best (eg 10, 50, 100 ...?). – Don't Panic Aug 02 '21 at 13:17

2 Answers2

1

Well since you have so many records its inevitable for it to take a long time, my suggestion is to chunk the data you are getting like so

foreach($items->chunk(1000) as $chunk) {
   foreach($chunk as $item) {
      ...
   }
}

The above method will go over 1000 (or as many as you want) items at a time, and should theoretically decrease the load time by a bit. But still I really don't think you can make it a lot faster.

dz0nika
  • 882
  • 1
  • 5
  • 16
0

I think you should here use ShouldQueue approach of Laravel and instead of updateOrCreate method use Query builder to update single row using where('id',$id) this will ma

DevAtStack
  • 3
  • 3