Description
During development, processCollectionItems executes SQL INSERT queries one at a time in a loop, which is extremely slow on SQLite. Wrapping them in a transaction provides ~75x speedup.
Benchmark
| Metric |
Before |
After |
| SQL exec (323 queries) |
3864ms |
51ms |
| Content processing total |
4681ms |
1038ms |
Fix
Simple 2-line change:
const sqlDumpList = Object.values(collectionDump).flatMap((a) => a);
db.exec(\`DROP TABLE IF EXISTS \${infoCollection.tableName}\`);
+ db.exec('BEGIN TRANSACTION');
for (const sql of sqlDumpList) {
db.exec(sql);
}
+ db.exec('COMMIT');Why this works
SQLite by default wraps each statement in an implicit transaction. When you run 323 INSERT statements individually, SQLite does 323 separate transactions (with fsync to disk each time). Wrapping them in a single explicit transaction does one fsync at the end.
Environment
Description
During development,
processCollectionItemsexecutes SQL INSERT queries one at a time in a loop, which is extremely slow on SQLite. Wrapping them in a transaction provides ~75x speedup.Benchmark
Fix
Simple 2-line change:
const sqlDumpList = Object.values(collectionDump).flatMap((a) => a); db.exec(\`DROP TABLE IF EXISTS \${infoCollection.tableName}\`); + db.exec('BEGIN TRANSACTION'); for (const sql of sqlDumpList) { db.exec(sql); } + db.exec('COMMIT');Why this works
SQLite by default wraps each statement in an implicit transaction. When you run 323 INSERT statements individually, SQLite does 323 separate transactions (with fsync to disk each time). Wrapping them in a single explicit transaction does one fsync at the end.
Environment