CODEWITHSUNDEEP
phparraysfor-loopparameter-passingconcatenation
osappuk
osappuk

Reputation: 51

Optimise string building with concatenation in PHP loop

I have a PHP function that do batch insertion into a MYSQL table. The function take input parameter as an array, it then loop thru the array to build the insert query like this:

public function batchInsert($values){

    $nbValues = count($values);
    $sql = 'INSERT INTO vouchers (`code`,`pin`,`owner_id`,`value`,`description`,`expire_date`,`lifetime`) VALUES ';
    for ($i=0; $i < $nbValues; $i++) { 
        $sql .= '(:col1_'.$i.', :col2_'.$i.', :col3_'.$i.', :col4_'.$i.', :col5_'.$i.', :col6_'.$i.', :col7_'.$i.')';
        if ($i !== ($nbValues-1))
            $sql .= ',';
    }
    $command = Yii::app()->db->createCommand($sql);
    for ($i=0; $i < $nbValues; $i++) {
        $command->bindParam(':col1_'.$i, $values[$i]['code'], PDO::PARAM_STR);
        $command->bindValue(':col2_'.$i, sha1($values[$i]['pin']), PDO::PARAM_STR);
        $command->bindParam(':col3_'.$i, $values[$i]['owner_id'], PDO::PARAM_INT); 
        $command->bindParam(':col4_'.$i, $values[$i]['value'], PDO::PARAM_INT);
        $command->bindParam(':col5_'.$i, $values[$i]['description'], PDO::PARAM_STR);
        $command->bindParam(':col6_'.$i, $values[$i]['expire_date'], PDO::PARAM_STR);
        $command->bindParam(':col7_'.$i, $values[$i]['lifetime'], PDO::PARAM_INT);
    }
    return $command->execute();
}

If the input array has 1K elements, the building of this sql query would take quite a large time. I believe this is caused by the way $sql variable is reconstructed after every loop. Is there any better way that you could suggest for me to optimise this? Thank you!

P/S: At the end of this batch insertion, I need to export all generated vouchers to an Excel file. Hence, if I built one single query and if the query was successful then the export function is called. By doing many seperate insertions, I cannot keep track of which one is inserted and which one is not (e.g. voucher code is unique, randomly generated and may have a chance of colliding). That's why I need a single query (or am I wrong?).

Upvotes: 1

Views: 116

Answers (3)

Richard Parnaby-King
Richard Parnaby-King

Reputation: 14892

What I did was change the string into an array, then implode it at the last step:

public function batchInsert($values){

    $nbValues = count($values);
    $sql = array();
    for ($i=0; $i < $nbValues; $i++) {
        $sql[] = '(:col1_'.$i.', :col2_'.$i.', :col3_'.$i.', :col4_'.$i.', :col5_'.$i.', :col6_'.$i.', :col7_'.$i.')';
    }
    $command = Yii::app()->db->createCommand('INSERT INTO vouchers (`code`,`pin`,`owner_id`,`value`,`description`,`expire_date`,`lifetime`) VALUES (' . implode('),(',$sql) . ')');
    for ($i=0; $i < $nbValues; $i++) {
            $command->bindParam(':col1_'.$i, $values[$i]['code'], PDO::PARAM_STR);
            $command->bindValue(':col2_'.$i, sha1($values[$i]['pin']), PDO::PARAM_STR);
            $command->bindParam(':col3_'.$i, $values[$i]['owner_id'], PDO::PARAM_INT); 
            $command->bindParam(':col4_'.$i, $values[$i]['value'], PDO::PARAM_INT);
            $command->bindParam(':col5_'.$i, $values[$i]['description'], PDO::PARAM_STR);
            $command->bindParam(':col6_'.$i, $values[$i]['expire_date'], PDO::PARAM_STR);
            $command->bindParam(':col7_'.$i, $values[$i]['lifetime'], PDO::PARAM_INT);
    }
    return $command->execute();
}

Upvotes: 0

N.B.
N.B.

Reputation: 14091

Let's define your requirement first:

  • You need to insert 1000 records, record being defined in an array
  • It should be fast
  • An insert could fail so it has to be repeated

First problem is that you are dealing with a database here. Modern MySQL uses InnoDB as storage engine - it's a transactional engine. PDO, by default, uses something called auto-commit.

What does all this mean for you? Basically, it means that a transactional engine will force the hard drive to really write the record before it tells you it's written. Engines such as MyISAM or NoSQLs won't do this. They will just let the OS to worry about the writing and OS will just queue the information that it should write to the disk. Disks are terribly slow so OS tries to compensate, and some disks even have caches where they store a lot of temp data.

However, unless information is really written to the disk, it's not saved since it could be lost. This is the D part of ACID in database - data is durable, ergo it's on a permanent storage device. This is why MySQL and other transactional databases are slow - because hard drives are painfully slow devices. A mechanical hard drive is able to perform between 100 - 300 writes per second (we'll call it IOPS or Input-output Operation Per Second). This is snail-like slow.

So, what PDO does by default is that it forces every query to be a transaction. That means every single query you do will take that 1 IOPS and you only have a few of them. So when you run 1000 inserts, if everything is great and you really do have 300 IOPS available, your inserts will take a while. If they fail and you have to retry them, then it's even worse since it lasts longer.

So what can you do to make it quicker? You do two things.

1) You wrap several inserts into a single transaction using PDO's method beginTransaction and commit when you're done. That makes the hard drive write several records using 1 IOPS. If you wrap all 1000 inserts into a single transaction, it will most likely be written extremely fast. Even though disks have low IOPS, they pack quite some bandwith so they will be able to eat up all 1000 inserts in a single go

2) Make sure that all your inserts will be successful. That means you should probably generate your voucher code at a later stage in the game, once everything's inserted. Remember, if a single query in a transaction fails - all of them fails (the A of ACID - Atomicity).

Basically, what I'm trying to highlight here is that Mark Baker posted a great answer and you should most likely modify your logic a bit. Prepare the statement once, execute multiple times. However, do wrap several calls to execution in a transaction - that will make it go real fast.

Upvotes: 1

Mark Baker
Mark Baker

Reputation: 212512

Rather than build one gigant-enormous string, consider executing individual inserts, but taking advantage of the use of a prepared statement

public function batchInsert($values){
    $nbValues = count($values);
    $sql = 'INSERT INTO vouchers (`code`,`pin`,`owner_id`,`value`,`description`,`expire_date`,`lifetime`) 
        VALUES (:col1, :col2, :col3, :col4, :col5, :col6, :col7)';
    $command = Yii::app()->db->createCommand($sql);
    for ($i=0; $i < $nbValues; $i++) {
        $command->bindParam(':col1', $values[$i]['code'], PDO::PARAM_STR);
        $command->bindValue(':col2', sha1($values[$i]['pin']), PDO::PARAM_STR);
        $command->bindParam(':col3', $values[$i]['owner_id'], PDO::PARAM_INT); 
        $command->bindParam(':col4', $values[$i]['value'], PDO::PARAM_INT);
        $command->bindParam(':col5', $values[$i]['description'], PDO::PARAM_STR);
        $command->bindParam(':col6', $values[$i]['expire_date'], PDO::PARAM_STR);
        $command->bindParam(':col7', $values[$i]['lifetime'], PDO::PARAM_INT);
        $command->execute();
    }
}

So you only prepare the short insert statement once, and just bind/execute inside the loop

Upvotes: 4

Related Questions