Batch inserts to SQLite

I have a need to insert hundreds of thousands of rows to a SQLite database. Initially I tried to insert one row at a time, but the speed was very slow, even with a prepared statement. Using the multiple-rows insert statement significantly increased the performance.

Here is the PHP code sample:

Option 1 (Slow) $stmt = $dbh->prepare("insert into wordindex (key, value) values (:k, :v)"); foreach ($data as $k => $v) { $stmt->execute(array(':k' => $k, ':v' => $v)); } Option 2 (Fast, up to 100 times)

uses the SQL syntax like the following : insert into table (col1, col2) value (a1, a2), (b1, b2), (c1, c2), ... Here is what the PHP code looks like: $batchSize = 500; $chunks = array_chunk($data, $batchSize, TRUE); foreach($chunks as $chunk) { $sql = "insert into wordindex (key, value) values "; $pairs = array(); foreach($chunk as $k => $v) { $pairs[] = "('$k','$v')"; } $sql .= implode(',',$pairs); $dbh->query($sql); } Since we are not using SQL parameter binding in option 2, we need to pay attention to values for $k and $v, to avoid SQL injection.

Enjoy!