note 103287 modified in pdostatement.execute by sobak

When using a prepared statement to execute multiple inserts (such as in a loop etc), under sqlite the performance is dramatically improved by wrapping the loop in a transaction. I have an application that routinely inserts 30-50,000 records at a time. Without the transaction it was taking over 150 seconds, and with it only 3. This may affect other implementations as well, and I am sure it is something that affects all databases to some extent, but I can only test with PDO sqlite. e.g. <?php $data = array( array('name' => 'John', 'age' => '25'), array('name' => 'Wendy', 'age' => '32') ); try { $pdo = new PDO('sqlite:myfile.sqlite'); } catch(PDOException $e) { die('Unable to open database connection'); } $insertStatement = $pdo->prepare('insert into mytable (name, age) values (:name, :age)'); // start transaction $pdo->beginTransaction(); foreach($data as &$row) { $insertStatement->execute($row); } // end transaction $pdo->commit(); ?> [EDITED BY sobak: typofixes by Pere submitted on 12-Sep-2014 01:07] --was-- When using a prepared statement to execute multiple inserts (such as in a loop etc), under sqlite the performance is dramatically improved by wrapping the loop in a transaction. I have an application that routinely inserts 30-50,000 records at a time. Without the transaction it was taking over 150 seconds, and with it only 3. This may affect other implementations as well, and I am sure it is something that affects all databases to some extent, but I can only test with PDO sqlite. e.g. <?php $data = array( array('name' => 'John', 'age' => '25'), array('name' => 'Wendy', 'age' => '32') ); try { $pdo = new PDO('sqlite:myfile.sqlite'); } catch(PDOException $e) { die('Unable to open database connection'); } $insertStatement = $pdo->prepare('insert into mytable (name, age) values (:name, :age)'); // start transaction $pdo->beginTransaction(); foreach($data as &$row) { $pdo->execute($row); } // end transaction $pdo->commit(); ?> http://php.net/manual/en/pdostatement.execute.php