sqlite+php - right and fastest way to do transactions with loop

Question

I'm a beginner in php/sql (6 months), and I noticed - transactions are faster than pure "insert into".

When I operate on huge amounts of data (with range: 10-500k inserts), I noticed my script is slow.

The goal: I wanna do the fastest way to save data into sqlite *.db file.

My script looks like that:

$array = array(
    'ronaldo' => 'gay' , 
    'mario basler' => 'cool guy'
);

$db = new Sqlite3('file.db')
$db->query('BEGIN;');

foreach($array as $kee => $val){
    $db->query("insert into table('name' , 'personality') values('$k' , '$v')");
}

$db->query("COMMIT");

Is that way is wrong?

Answer 1

What you do is absolutely correct. It will speed up your interaction with the database. Any command that changes the database will automatically start a transaction if one is not already in effect.

So, if you do many inserts without starting a transaction explicitly, for each operation a transaction will be created. You create 1 transaction and do all the operations in bulk.

How to insert 40000 records fast into an sqlite database in an iPad

https://www.sqlite.org/lang_transaction.html