robotpony/Presto — pull request #69 by adam-patterson (2013-08-12 19:51:33+00:00)
Title: Multi insert for DB wrapper

@bruce-alderson and @ngallagher87 for review.

Adds a function to the db wrapper to provide support for multiple row insertions. Uses the approach for multi-row inserts via PDO documented [here](http://stackoverflow.com/questions/16384605/inserting-multiple-rows-in-one-query-from-an-array?lq=1): prepare the statement first and execute a separate insert for each row (wrapped in a transaction).

### Usage:

* $sql: an `INSERT` statement of the form `INSERT INTO Foo (C1, ..., Cn) VALUES (:key1, ..., :keyn)`
* `$dataTypes`: an array containing the PDO data types of the data values of the form `array(0 => PDO::dataType, ..., n => PDO::dataType)`
* `$data`: a 2D array of the form `array(0 => array(key1 => v01, ..., keyn => v0n), ..., m => array(key1 => vm1, ..., keyn => vmn))`

Example call: `$this->db->multi_insert($sql, $dataTypes, $values);`

### Performance

Limited size test inserting ~160 rows of people data into reporting cache on my local environment, 3 trials averaged for each

* String interpolation method gives ~0.26 ms for building data, ~2.5 ms for insertion
* Multi-row insert method gives ~0.22 ms for building data, ~20.1 ms for insertion

Multi-row insert approach is slower by an order of magnitude but correctly offers full parameter binding and is cleaner/clearer. Also, for `reports` this ~20ms insertion time is dwarfed by the time it takes to actually get the data from core.