GSoC 2018 Stories 05: SQLite multiple insertions (batch insertions)
In my gsoc story series with Score Lab organization, I’m writing a bit different article today. This would be a very short article comparing 2 scenarios of insertions. In order to store data in sqlite I had to find a way to execute insertion queries. In my head there are 2 ways.
Scenario 1 : Creating a string with large set of insert statements and execute the query once.
Scenario 2 : Use transactions.
At first I thought 1st option would give more performance, but I was completely wrong when I implemented and run sample program.
Following is an abstraction of the code which I ran to test the 1st scenario. When I use 100000 as numberOfInsertions,
above code segment gave a execution time of 148.909 seconds.
Below code is to test the insertions using transactions. For 100000 as numberOfInsertions,
above code segment gave a execution time of 0.415 seconds!
Using transactions is the option!
Look at the performance gain. My initial thoughts were an utter failure. Seems like I have missed my database lectures! I would post a separate article on how transactions work if time permits.
Hope above comparison would help someone.
Hint : Use sqlite3 transactions with prepared statements (sqlite3_prepare) which will boost the performance even higher.
Click here for the Previous article
