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

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

• @nuxt/content: 3.9.0
• 31 collections, 173 files
• Discovered in: https://github.com/nimiq/website