On 2015-08-28 04:15 AM, Yuri wrote: > 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.
On the point of inserting and selecting to get keys.. you don't /have/ to let the autoincrement or primary key assign keys, if I do bulk inserts, I check the once what the next key should be (highest key + 1) and then simply count up from there inserting every next item with it's next key which I then don't need to select to find. Much faster this way. Check extra Indices and Triggers are not slowing things down. Also - I am not sure that turning the Journal off saves much for that size transaction. The cache will spill to disk anyway. Alternatively, break it into several smaller transactions, maybe doing just 100k inserts at a time. Best of luck, Ryan > > 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 > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
