note 103287 added to pdostatement.execute

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(); ?> ---- Server IP: 69.147.83.197 Probable Submitter: 124.168.116.224 ---- Manual Page -- http://www.php.net/manual/en/pdostatement.execute.php Edit -- https://master.php.net/note/edit/103287 Del: integrated -- https://master.php.net/note/delete/103287/integrated Del: useless -- https://master.php.net/note/delete/103287/useless Del: bad code -- https://master.php.net/note/delete/103287/bad+code Del: spam -- https://master.php.net/note/delete/103287/spam Del: non-english -- https://master.php.net/note/delete/103287/non-english Del: in docs -- https://master.php.net/note/delete/103287/in+docs Del: other reasons-- https://master.php.net/note/delete/103287 Reject -- https://master.php.net/note/reject/103287 Search -- https://master.php.net/manage/user-notes.php