PHP SQLite3 optimize large amount of inserts

PHP SQLite3 optimize large amount of inserts

One of the things many people forget to do is to start and end a transaction yourself instead of letting the internal SQLite3 do it.

If a transaction is not detected by SQLite, it will start a new transaction for each insert you do. As the numbers scale up, the time taken for the entire set of insert queries exponentially increases.

To start and end your own transaction, it is as simple as using the code below (remember to modify it for your own use)

// START TRANSACTION
$this->db->exec('BEGIN;'); 

// Prepare query
$stmt = $this->db->prepare($insert_query);

// Bind parameters to statement variables
$stmt->bindParam(':field', $field_value);

// Loop execute
foreach ($records as $record) {
    $field_value = $record['field'];
    $stmt->execute();	
}			

// END TRANSACTION
$this->db->exec('COMMIT;'); // END TRANSACTION