On Tuesday, 10 September, 2019 09:26, mailing lists <mailingli...@skywind.eu> wrote:
>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? When you "create" an index as a single operation, you scan the table to collect the key data, do a sort, and then do an in-order insertion into the B-Tree, and then write out the entire tree all at once (it is a single transaction). When the index already exists, you have to "fiddle" with the B-Tree for each record because it is no longer being built in order. You have to split and combine pages and shuffle the data about as each record is inserted into the index. The more records that can be inserted per transaction the less I/O will be required (and you have to have a big enough cache). If you can pre-sort the records so that they are inserted in-order and use a relatively large number of records inserted per transaction, then there will be very little difference between the two. Of course, if you have multiple indexes then pre-sorting into the most efficient insertion order is a high art (and often not entirely possible anyway). -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
