CODEWITHSUNDEEP
phpmysqlpdo
kentor
kentor

Reputation: 18504

Multiple row inserts as fast as possible

I've seen multiple threads discussing this but there always has been totally different conclusion in the answers. Especially I wonder whether it is really necessary to create a own prepared statement (with the right amount of placeholders) in order to insert it as single query. I expected that when I use beginTransaction and endTransaction before and after my for loop, that pdo/php waits with the transaction until all data is collected and it will send these data's as a single query once the server hits the line endTransaction.

How would I need to rewrite such a for loop insert with multiple inserts in order to reach the best performance (it has between 1 and 300 rows usually but it also could reach 2000 rows).

for($i=0; $i<$baseCount; $i++)
    {
        $thLevel = $bases[$i]["ThLevel"];
        $gold = $bases[$i]["Gold"];
        $elixir = $bases[$i]["Elixir"];
        $darkElixir = $bases[$i]["DarkElixir"];
        $dateFound = $elixir = $bases[$i]["TimeFound"];

        $query = $db->prepare("INSERT INTO bot_attacks_searchresults (attack_id, available_gold, available_elixir, available_dark_elixir, date_found, opponent_townhall_level)
        VALUES (:attack_id, :available_gold, :available_elixir, :available_dark_elixir, :date_found, :opponent_townhall_level)");
        $query->bindValue(':attack_id', $attackId);
        $query->bindValue(':available_gold', $gold);
        $query->bindValue(':available_elixir', $elixir);
        $query->bindValue(':available_dark_elixir', $darkElixir);
        $query->bindValue(':date_found', $dateFound);
        $query->bindValue(':opponent_townhall_level', $thLevel);
        $query->execute();
    }

Upvotes: 0

Views: 57

Answers (2)

N.B.
N.B.

Reputation: 14060

  • Prepare the statement once. MySQL lexes it once, so any subsequent call to the query will be quick since it's already lexed and juts needs parameters.

  • Start the transaction before the loop. This is done so your hard drive can write down all the rows in one input output operation. The default mode is that 1 insert query = 1 I/O of the hdd.

  • Create the loop, bind your parameters there and call the $query->execute();

  • Exit the loop and commit() the transaction.

Full code:

$db->beginTransaction();

$query = $db->prepare("INSERT INTO bot_attacks_searchresults (attack_id, available_gold, available_elixir, available_dark_elixir, date_found, opponent_townhall_level)
        VALUES (:attack_id, :available_gold, :available_elixir, :available_dark_elixir, :date_found, :opponent_townhall_level)");

for($i = 0; $i < $baseCount; $i++)
{
    $thLevel = $bases[$i]["ThLevel"];
    $gold = $bases[$i]["Gold"];
    $elixir = $bases[$i]["Elixir"];
    $darkElixir = $bases[$i]["DarkElixir"];
    $dateFound = $elixir = $bases[$i]["TimeFound"];

    $query->bindValue(':attack_id', $attackId);
    $query->bindValue(':available_gold', $gold);
    $query->bindValue(':available_elixir', $elixir);
    $query->bindValue(':available_dark_elixir', $darkElixir);
    $query->bindValue(':date_found', $dateFound);
    $query->bindValue(':opponent_townhall_level', $thLevel);
    $query->execute();
}

$db->commit();

Upvotes: 1

Strawberry
Strawberry

Reputation: 33935

Here's a very crude proof of concept:

<?php

$values = array();

for($i=0;$i<10;$i++)
{
$values[] = "($i)";
}

$values = implode($values,',');

$query = "INSERT INTO my_table VALUES $values";

echo $query;

?>

outputs INSERT INTO my_table VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)

You would need to restructure this slightly to work with prepare (PHP is not my forte), but the principle is the same; i.e. you build the query inside the loop, but execute it only once.

Upvotes: 0

Related Questions