in reply to Re^2: SQLite: INSERT into a unique column and retrieve rowid
in thread SQLite: INSERT into a unique column and retrieve rowid

I thought you were saying that the addition of an index to the table would dramatically increase the time it took to load the table. Without begin_work / commit, it took 23 seconds to load the table with an index, and 20 seconds without one. However, running it as a single transaction as you suggest, those numbers drop to 0.77 and 0.72.

I haven't run any comparisons to see how much the index benefits retrieval, however...

  • Comment on Re^3: SQLite: INSERT into a unique column and retrieve rowid

Replies are listed 'Best First'.
Re^4: SQLite: INSERT into a unique column and retrieve rowid
by Marshall (Canon) on May 06, 2024 at 17:05 UTC
    I meant that indexing a column(s) will cause inserts into the table to take longer because the index structure needs to be updated as extra work. The goal would be to decrease retrieval times. I don't think that indexing will matter much in your case, but some performance tests would tell the story. Your performance numbers sound about right to me. Your code was running one transaction per insert. Making one transaction for 60,000 inserts saves a lot of time!!