The Problem: Slow/Sticky UI
If you’ve ever build a SQLite-based application that inserting large sets of data, you may have come across certain performance issues. For example, if you happen to be creating a WebView application on iOS or Android where you are using javascript to insert your data into your database, your application may be feeling pretty slow and sticky as your UI gets bogged down.
This is because executing SQLite statements are being run synchronously, and will wait until each one is completed before starting the next insert. A couple of simple solutions which I’ll talk about can drastically speed up your application by changing the way that the data is inserted.
Turn off Synchronous Pragma
The first possible solution will get your inserts to run much faster, but I don’t recommend it for mobile apps due to the fact that you are risking the database to get corrupted by this method. This method involves simply telling your database to run the statements asynchronously by running the following line before looping through your insert statements.
PRAGMA synchronous=OFF
Like I said though, this isn’t the best solution because although it is super quick to implement, you might end up with another problem: corrupted data. If your application happens to lose power during the insert process, data can be lost or corrupted. While you might be able to argue that most of the time this won’t occur, it’s not terribly much more effort to implement the second solution, which doesn’t have the same drawbacks.
Batch Insert
The other method which can help you greatly speed up your SQLite inserts is to swap your for-loop of execute statements for batch insert method. To do this, you can wrap your for loop in a BEGIN IMMEDIATE TRANSACTION statement and end with a COMMIT TRANSACTION statement.
All in all, this is a much better solution. If you are using a lot of inserts in your app, you can even create a custom function in your native code (Swift or Java) to reuse the same process and change the fields/table name depending dynamically.
Why do batch inserts increase speed?
Batch inserts in SQLite increase speed because you are bundling multiple inserts into 1 transaction. The speed limitations are not on each insert, but each transaction. That being said, if you don’t use batch insert, each statement you execute will be treated as a separate transaction.
So next time you find yourself experiencing slow UI’s in your WebView and you are doing a heavy number of inserts to your SQLite database, it’s best to consider batch inserts to speed up your application, you won’t be sorry!
If you want to see a few examples of how to do this in Android and Swift, head over to my post with examples on how to do this in Java and Swift.