PDO - Insert large array to MySQL database

Question

I need some help to improve my current code. I have a huge array (about 20,000 objects inside it). The array looks like this:

  Array
(
    [0] => Player Object
        (
            [name] => Aaron Flash
            [level] => 16
            [vocation] => Knight
            [world] => Amera
            [time] => 900000
            [online] => 1
        )

    [1] => Player Object
        (
            [name] => Abdala da Celulose
            [level] => 135
            [vocation] => Master Sorcerer
            [world] => Amera
            [time] => 900000
            [online] => 1
        )

    [2] => Player Object
        (
            [name] => Ahmudi Segarant
            [level] => 87
            [vocation] => Elite Knight
            [world] => Amera
            [time] => 900000
            [online] => 1
        )

    [3] => Player Object
        (
            [name] => Alaskyano
            [level] => 200
            [vocation] => Royal Paladin
            [world] => Amera
            [time] => 900000
            [online] => 1
        )

    [4] => Player Object
        (
            [name] => Aleechoito
            [level] => 22
            [vocation] => Knight
            [world] => Amera
            [time] => 900000
            [online] => 1
        )

And so on... with about 20,000 Player Object in total.

Now I want to insert them all in to my database. I'd like to find a way to not loop through all players. It is causing a lot of performance issues and it's almost killing my computer. I'd like to make it in a single query, all at once.

But how can I get the Player Object attributes, like the "name", "level" and "vocation" of each individual object without looping them through?

This is what my code looks like:

// Insert player list to database
$sql = $db->prepare("INSERT INTO players (name, level, vocation, world, month, today, online) VALUES (:name, :level, :vocation, :world, :time, :time, :online) ON DUPLICATE KEY UPDATE level = :level, vocation = :vocation, world = :world, month = month + :time, today = today + :time, online = :online");

foreach ($players as $player) {
  $query = $sql->execute([
    ":name" => $player->name,
    ":level" => $player->level,
    ":vocation" => $player->vocation,
    ":world" => $player->world,
    ":time" => $player->time,
    ":online" => $player->online
  ]);
}

Because right now on that foreach at the bottom, it is looping through 20,000 player objects in my array, and getting their names/level/vocation/world and so on.

Is there a better way to do this? My way of doing it can't be the best solution. I can hear my PC is working overload and it feels as if it's about to crash.

So there is no quick way to grab the $player->name and such without looping through each? Can't I somehow tell it that ":name" should equal to the "name" inside each Player Object? Looping this is causing so much lag and performance issues. — rick astley
– rick astley, Commented Feb 14, 2017 at 18:07
You can get items via [] syntax but that doesn't help you much — u_mulder
– u_mulder, Commented Feb 14, 2017 at 18:09
Like: ":name" => $players['name'] ? (My array is called $players) — rick astley
– rick astley, Commented Feb 14, 2017 at 18:10
Have you considered using 'batched transactions'. Currently, I suspect it commits each row (autocommit)?. If you ue transactions and commit say, every few hundred rows, then you cut down the work the database has to do quite a lot. Worth a try? It isn't a lot of change to your code, just a count - commit and start transaction inside the loop, so is easy to try? It can be surprising ;-/ — Ryan Vincent
– Ryan Vincent, Commented Feb 14, 2017 at 20:10

Sammitch · Accepted Answer · 2017-02-15 01:58:54Z

7

While I still doubt that transactions and/or batched inserts are a viable solution to your resource usage problem, they're still a better solution than preparing massive statements like Dave has suggested.

Give these a shot and see if they help.

The following assumes that PDO's error handling mode is set to throw exceptions. Eg: $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); If, for some reason, you can't use Exception mode then you'll need to check the return of execute() each time and throw your own Exception.

Single transaction:

$sql = $db->prepare("INSERT INTO players (name, level, vocation, world, month, today, online) VALUES (:name, :level, :vocation, :world, :time, :time, :online) ON DUPLICATE KEY UPDATE level = :level, vocation = :vocation, world = :world, month = month + :time, today = today + :time, online = :online");

$db->beginTransaction();
try {
    foreach ($players as $player) {
        $sql->execute([
            ":name" => $player->name,
            ":level" => $player->level,
            ":vocation" => $player->vocation,
            ":world" => $player->world,
            ":time" => $player->time,
            ":online" => $player->online
        ]);
    }
    $db->commit();
} catch( PDOException $e ) {
    $db->rollBack();
    // at this point you would want to implement some sort of error handling
    // or potentially re-throw the exception to be handled at a higher layer
}

Batched Transactions:

$batch_size = 1000;
for( $i=0,$c=count($players); $i<$c; $i+=$batch_size ) {
    $db->beginTransaction();
    try {
        for( $k=$i; $k<$c && $k<$i+$batch_size; $k++ ) {
            $player = $players[$k];
            $sql->execute([
                ":name" => $player->name,
                ":level" => $player->level,
                ":vocation" => $player->vocation,
                ":world" => $player->world,
                ":time" => $player->time,
                ":online" => $player->online
            ]);
        }
    } catch( PDOException $e ) {
        $db->rollBack();
        // at this point you would want to implement some sort of error handling
        // or potentially re-throw the exception to be handled at a higher layer
        break;
    }
    $db->commit();
}

answered Feb 15, 2017 at 1:58

Sammitch

32.5k7 gold badges57 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

rick astley Over a year ago

Hey! Sorry for the late reply. I just tested it and HOLY SH*T!!!! You are a God. You are literally a God. Why is there such a massive performance difference while using the "beginTransaction"? It completed it all within seconds. 20,000 took only 2 or 3 seconds. Compared to my original code that would take well over 6 or 7 minutes. That is insanely quick and only went up to about 18% resource usage. My code would go up to the 90-95% range. God damn! I gotta take a look at the transaction manual in PHP. This blew my mind! THANK YOU A BUNCH!!

Sammitch Over a year ago

I took a spin over to a mysql channel on IRC and asked if transactions would help in a case like this and, from what I recall, with one large transaction mysql can potentially defer writes to Tlog, index, and data files until the end of the transaction. There was one caveat mentioned though, if the database is replicated large transactions can introduce replication lag as large as the time taken to complete the transaction. I wouldn't classify it as something to lose sleep over, but something to bear in mind nonetheless.

Sammitch Over a year ago

Also bear in mind that any error during the transaction that triggers rollback() while the transaction is open will undo all changes. As well, DDL changes [CREATE/ALTER/etc] will trigger an implicit commit in mysql.

dave · Accepted Answer · 2017-02-14 18:53:56Z

-1

I think the biggest performance gain you will get is by not doing one query per insert, but doing a single query for all inserts. Something like:

$sql = "INSERT INTO players (name, level, vocation, world, month, today, online) VALUES ";
$inserts = [];
$values = [];
$idx = 0;
foreach ($players as $player) {
    $idx++;
    $inserts[] = "(:name{$idx}, :level{$idx}, :vocation{$idx}, :world{$idx}, :month{$idx}, :today{$idx}, :online{$idx})";
    $values[":name{$idx}"] = $player->name;
    $values[":level{$idx}"] = $player->level;
    $values[":vocation{$idx}"] = $player->vocation;
    $values[":world{$idx}"] = $player->world;
    $values[":month{$idx}"] = $player->time;
    $values[":today{$idx}"] = $player->time;
    $values[":online{$idx}"] = $player->online;
}
$sql .= implode(",", $inserts);
$sql .= " ON DUPLICATE KEY UPDATE level = VALUES(level), vocation = VALUES(vocation), world = VALUES(world), month = month + VALUES(time), today = today + VALUES(time), online = VALUES(online)";

$query = $db->prepare($sql)->execute($values);

edited Feb 14, 2017 at 18:53

answered Feb 14, 2017 at 18:10

dave

65.1k6 gold badges81 silver badges98 bronze badges

17 Comments

rick astley Over a year ago

That only inserted the last Player Object of the array. It ignored the rest.

dave Over a year ago

Sorry was missing the [] after $values in the loop, just fixed it

rick astley Over a year ago

Notice: Array to string conversion in D:\xampp\htdocs\index.php on line 75 line 75 is the $query = $db->prepare($sql)->execute($values);

dave Over a year ago

Alright I guess php doesnt support named parameters on multi row inserts, you'll have to do question mark placeholders, but this should work now

rick astley Over a year ago

Still the same error. Hmm... why would it try turn the array into a string?

|

Collectives™ on Stack Overflow

PDO - Insert large array to MySQL database

2 Answers 2

3 Comments

17 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

17 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related