I build a fairly large DB, with major tables having 800k..5M rows, with several relationships between tables.
At ~30% into the insertion process it slows down rapidly. Records #171k..172k are still fast, and records #172k...173k are already ~10 times slower. (all records are more or less similar) During Insertion process, selects are also performed to obtain keys to insert into other tables. 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; Plan for each "select" statement shows that it uses an index or primary key. Every insert statement is a simple insert "insert into xxx(x,x,x) values(?,?,?)" Selects are also all simple one-table selects. All statements used in prepared form. How can I understand why the slowdown occurs? Especially, why the slowdown in so "sharp"? Something drastic happens, like some strategy is recomputed, some index is rebuilt, etc... Thank you, Yuri
