Creating DB Records in Laravel (Quickly)

php

I recently had cause to create lots of records in a SQL database, running on Windows Server 2016. That’s the backstory, but the crucial thing was that whilst building the tool, I made use of Eloquent’s save method for each record. Imagine this:

1
2
3
$flight = new Flight;
$flight->name = $request->name;
$flight->save();

Read the full Laravel docs here.

That works nicely. It’s readable, uncomplicated, but very, very slow, when you are saving thousands of records as I was. How could we improve upon it?

Inserting

Well, firstly, let’s not use Eloquent in this way. Instead, let’s use the database INSERT command instead.

1
2
3
4
5
6
7
8
$flightData = array();
$flightData[] = array(
    'name' => 'London'
);
...
$flightData[] = array(
    'name' => 'New York'
);

I’ve added my separate rows manually when in reality, you would use a loop and some data source like a POST request, but you get the idea.

Now, we do an INSERT for the whole lot in one whack:

1
Flight::insert($chunk);

That should maybe halve your time taken.

But, what about those created_at and updated_at fields? Well, you need to cater for them, too, because they are no longer auto-populated. Try something like this:

1
2
3
4
5
6
7
$now = \Carbon\Carbon::now();
$flightData[] = array(
    'name' => 'New York',
    'created_at' => $now,
    'updated_at' => $now,
);

From here, I do have some tips on how I managed to shave off a few more milliseconds, but nothing spectacular.

Removing Fields

Do you need to send all the fields in the insert statement? Well, maybe not. For example, those created_at and updated_at fields could have defaults set on them in the migration.

1
2
$table->dateTime('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));
$table->dateTime('updated_at')->default(DB::raw('CURRENT_TIMESTAMP'));

That saved me almost 1 second on a 2,500 row insert. With a bit of investigation, there could be others you could default, too.

Using Transactions

If you find you have to chunk the data due to your db driver, it can help if you wrap your inserts into a transaction, and then the insert will only occur once, rather than in …. um … chunks.

1
2
3
4
5
DB::beginTransaction();

... do your inserts

DB::commit();

Don’t forget to cater for any exceptions, of course.

Other Ideas

Here’s a couple more ideas that I considered and I will add more should I think up any, later.

  • Are you excessively indexing? That can slow down inserts.
  • Your database might be logging all transactions? Does it need to?
  • Is your database local to the web server or on another machine? Is that machine in the same geographic region if in the cloud? Running locally made my inserts HUGELY quick, as you can guess.

That’s all folks, as they say. Hope it’s been helpful.

Hi! Did you find this useful or interesting? I have an email list coming soon, but in the meantime, if you ready anything you fancy chatting about, I would love to hear from you. You can contact me here or at stephen ‘at’ logicalmoon.com