While a write transaction is open, SQLite needs to keep the changed pages in memory. When the size of a transaction (measured in changed pages) exceeds the available memory, SQLite starts to spill the transaction to disk. The optimal transaction size would be just before this occurs, but there is no interface available to determine this, so the number of records is a commonly used proxy.
Creating indexes after insert is faster because the writes are localized to the index pages, so you get more logical inserts per disk write and file system buffering and read ahead have more cache hits too. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von mailing lists Gesendet: Dienstag, 10. September 2019 17:26 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: [EXTERNAL] Re: [sqlite] How to increase performance when inserting a lot of small data into table using indices Hi, I cannot really put all the inserts into one transaction because in case of a failure I loose all the already inserted data. Though I made some tests. There is hardly any performance gain anymore when doing 1000 or 10 000 insertions in one transaction including immediate insertion into indices (in my case the difference is in the per cent range). What is the background that index creation is so much faster than insertion using indices? Once I heard something about fragmentation but on solid state disks fragmentation should not play a role as long as indices and data are separated, are they? Regards, Hartwig > Am 2019-09-10 um 17:16 schrieb Richard Hipp <d...@sqlite.org>: > > On 9/10/19, mailing lists <mailingli...@skywind.eu> wrote: > >> So, the best solution I found so far is to disable indexing while >> insertion and to index the table afterwards > > I think that is the best solution. Be sure to also do all of your > inserts (and the CREATE INDEX statements) inside of a transaction. > > -- > D. Richard Hipp > d...@sqlite.org > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
