Re^2: SQLite: INSERT into a unique column and retrieve rowid

I don't know what "it's pretty fast adding 60,000 songs" means. I would add the line $dbh->begin_work; before the loop that puts the 60,000 things into the DB. And the line $dbh->comitt; after the loop is over. This will run all 60,000 inserts as a single transaction. There should be a very noticeable decrease in execution time.

Comment on Re^2: SQLite: INSERT into a unique column and retrieve rowid Select or Download Code

Replies are listed 'Best First'.
Re^3: SQLite: INSERT into a unique column and retrieve rowid by ibm1620 (Hermit) on May 06, 2024 at 15:10 UTC
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...	[reply]
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!!	[reply]