Ian Warner-4 wrote:
>
> Just wondered what the best methods were to do Bulk Inserts
>
I assume that by "best" you mean that you want this to run fast (everybody
does).
Although the Table->createRow() solution is the most convenient and
consistent with the OO usage, it's actually the slowest solution, because it
prepares and executes a new SQL statement for each row.
One improvement is to prepare an INSERT query with parameter placeholders,
and execute the prepared query in a loop, providing one row to be inserted
as parameters.
Another way to speed up bulk inserts is to use explicit transactions,
instead of committing after each row inserted. This applies to autocommit
as well.
$db = $bugs->getAdapter();
$db->beginTransacation();
$stmt = $db->prepare("INSERT INTO bugs (bug_description, bug_status, ...)
VALUES (?, ?, ...)");
for ($arrayOfRows as $row) {
// $row is an array of values matching the columns in the INSERT
statement
$stmt->execute($row);
}
$db->commit();
However, the fastest solution is to use MySQL's LOAD DATA INFILE statement
(or equivalent for other brands). Using the database's solution for bulk
loading can be ten to twenty times faster than doing inserts in a loop.
See also http://dev.mysql.com/doc/refman/5.1/en/insert-speed.html for more
tips.
Regards,
Bill Karwin
--
View this message in context:
http://www.nabble.com/Zend-DB-tp15459316s16154p15461543.html
Sent from the Zend Framework mailing list archive at Nabble.com.
