[sqlite] Insertion into the large DB drastically slows down at some point

[Clemens Ladisch]

Yuri wrote:
> I followed every advise I could find:
> * Database is written into memory
> * Whole insertion happens in one transaction in one giant BEGIN TRANSACTON; 
> ... END TRANSACTION; block.
> * Foreign keys are deferred: PRAGMA defer_foreign_keys=ON;
> * Journal is disabled: PRAGMA journal_mode = OFF;
> * Synchronous mode is disabled: PRAGMA synchronous = OFF;

On modern PCs, SQLite's page cache does not have a large effect because
of the file cache of the OS, but you should do, e.g.,
  PRAGMA cache_size = -1000000;
for a 1 GB cache (default is only a few MB).


Regards,
Clemens