Android: Using DatabaseUtils.InsertHelper for faster insertions into SQLite database
AndroidOS includes the DatabaseUtils.InsertHelper class for speeding up insertions into an SQLite database. However, very little documentation or examples seem to be available to show how to use this class. I hope this post will help to make InsertHelper a little less mysterious than it apparently has been.
It’s often the case that bulk insertions are performed in the onCreate method of an SQLiteOpenHelper, so I’ll use that context for this example. (This has implications on transaction management, which in turn affects performance, as described below.)
Suppose the onCreate method currently looks something like this:
private class DatabaseHelper extends SQLiteOpenHelper {
@Override
public void onCreate(SQLiteDatabase db) {
ContentValues values = new ContentValues();
while (moreRowsToInsert) {
// ... create the data for this row (not shown) ...
// Add the data for each column
values.put("Greek", greekData);
values.put("Ionic", ionicData);
// ...
values.put("Roman", romanData);
// Insert the row into the database.
db.insert("columnTable", null, values);
}
}
//...
}
Using DatabaseUtils.InsertHelper, this would be re-written as:
import android.database.DatabaseUtils.InsertHelper;
//...
private class DatabaseHelper extends SQLiteOpenHelper {
@Override
public void onCreate(SQLiteDatabase db) {
// Create a single InsertHelper to handle this set of insertions.
InsertHelper ih = new InsertHelper(db, "columnTable");
// Get the numeric indexes for each of the columns that we're updating
final int greekColumn = ih.getColumnIndex("Greek");
final int ionicColumn = ih.getColumnIndex("Ionic");
//...
final int romanColumn = ih.getColumnIndex("Roman");
try {
while (moreRowsToInsert) {
// ... Create the data for this row (not shown) ...
// Get the InsertHelper ready to insert a single row
ih.prepareForInsert();
// Add the data for each column
ih.bind(greekColumn, greekData);
ih.bind(ionicColumn, ionicData);
//...
ih.bind(romanColumn, romanData);
// Insert the row into the database.
ih.execute();
}
}
finally {
ih.close(); // See comment below from Stefan Anca
}
}
//...
}
As this shows, using InsertHelper is barely more complicated than using SQLiteDatabase.insert. The major differences are that you need to call ih.prepareForInsert() before adding (“binding”) the column data; and you need to obtain each column’s numeric index, which we get by calling ih.getColumnIndex() prior to the loop.
After replacing SQLiteDatabase.insert with DatabaseUtils.InsertHelper, the database insertion speed went from the equivalent of about 95 rows per second to about 525 rows per second. (“Equivalent of”, because the app also spends cycles creating the data to insert. Here, the performance timings are measured using constant data, eliminating that overhead.)
InsertHelper isn’t really doing anything magical here. It’s essentially a wrapper around compiled statements, which you can create yourself using SQLiteDatabase.compileStatement. Most people will probably find InsertHelper easier to use, though.Other ways to speed up insertions
In addition to that gain, two more changes then brought the insertion speed to well over 900 rows per second. Whether these tricks techniques work for you will depend on your application.
Don’t bind empty columns
In my app, the data for at least 50% of the columns is empty. By skipping the call to ih.bind() when the column data is a null or empty string, I saw a roughly 30% performance boost.
Temporarily disable database thread locking
I’m loading the database during the onCreate method of my app’s SQLiteOpenHelper. During this time, it seems safe to assume that only one thread is accessing the database, so I use SQLiteDatabase.setLockingEnabled() to temporarily (let me emphasize that: temporarily) disable thread locks within the database API. This yielded about a 35% performance gain:
public void onCreate(SQLiteDatabase db) {
//...
try {
// *Temporarily* (have I emphasized that enough?) disable
// thread locking in the database. Be sure to re-enable locking
// within a finally block.
db.setLockingEnabled(false);
// ... load the database ...
}
finally {
db.setLockingEnabled(true);
}
Transactions and performance
A number of people have cited performance gains through use of explicit transactions in SQLite. However, SQLiteOpenHelper creates a transaction before invoking its callback methods (onCreate, onUpgrade, and onOpen), so explicit transaction control is unnecessary within those methods. (SQLiteOpenHelper will assume that the transaction was successful unless your method throws an exception.)
You would need to manage your own transactions if your insertion code is running outside of one of SQLiteOpenHelper‘s callback methods. The main APIs for this are SQLiteDatabase.beginTransaction, SQLiteDatabase.setTransactionSuccessful, and SQLiteDatabase.endTransaction.
It is possible to nest transactions, but, not surprisingly, this doesn’t seem to help performance. In fact, I saw a very slight performance degradation when using nested transactions (approximately 1%, probably below the accuracy of the measurements.) I also tried periodically closing the current transaction — the first of these being the transaction that was opened by SQLiteOpenHelper — then opening a new one. This didn’t yield much improvement, if any.
In my hands nested insertions inside a single transaction was hugely faster than your approach. I’m still using InsertHelper, but using .insert() with ContentValues rather than the bind and execute pattern. I’m not sure what you mean by a transaction lock. Isn’t that was startTransaction() is doing explicitly? How else do you obtain a lock
Hi, Jonathan,
I confess, I hadn’t tried
insert(ContentValues)before I wrote this. I’ve now tried it, and at least in my case I found it to be much slower than usingprepareForInsert…bind…execute. (Excluding non-database code, it’s roughly twice as slow.) Of course, I’m not going to claim that my results mean thatprepareForInsert…bind…executewill always be faster.I’m curious about why my results are different from yours, though. Would it be possible for you to send me your code, or traceview trace files (ideally for both test cases)?
For what it’s worth, the table that I’m initializing has six columns, most of which are sparsely populated. The traceview log for the modified version of my program (using
ContentValues) indicates that it’s spending most of the additional time iterating through theContentValuesand looking up the column indexes for each of the columns. If you have only two columns (for example), perhaps that overhead is much lower.By the way, this most recent test was on the emulator, running Android OS 2.2. What version are you running?
In referring to a “transaction lock”, all I meant was that
SQLiteOpenHelperstarts a transaction before calling theonCreatemethod, and ends the transaction afteronCreatereturns. I’ve modified the post to try to clarify this.Jonathan explained in emails that his performance gain was achieved through creating transactions in his code, not from the use of
ContentValues(which is actually somewhat slower than using theprepareForInsert…bind…executesequence.)My post hadn’t made it clear that the example code was running inside of
onCreate, where a transaction has already been created by theSQLiteOpenHelper. I’ve updated the post to try to clarify this.I’ve been fighting with a bulk insert of at least 2400 records into a db. The insert is taking place outside of the SQLiteOpenHelper so I wrapped the insert with the transact methods and it sped it up for sure. I’ve also moved away from .insert() to creating my own insert sql and escaping the proper values.
Have you tested/noticed if raw sql is faster than prepared statements? My last round of testing brought the execute time down from 175,000ms using .insert() without the transact methods to 107,000ms using raw escaped inserts and transact methods.
Hi, Ernest,
I’d be very surprised if you didn’t gain a further performance boost from using prepared statements in place of raw SQL. As I noted in the article, you’ll probably see about the same effect whether you use
InsertHelperor lower-level APIs for prepared statements. (I think usingInsertHelperis simpler, though.)If you find that using prepared statements doesn’t seem to help performance, and you’re willing to send me your code (before and after the change to use prepared statements), I’d really appreciate it.
I’m going to try and used prepared statements again. When I tried before kept getting an error when it tried running the exec a second time.
Also wish there was a way to subscribe to comments :/
UPDATE
I tested my code on a Nexus S and it runs really fast. Importing 2400+ records takes about ~6sec. The emulator runs really slow. Testing on a G1 to get a feel for a low end device
Progress update
Prepared inserts are so freaking fast! Finally solved all my issues by straightening out my JSON input. Thank you for this article.
I also gained some more speed by executing this command
“PRAGMA synchronous=OFF”
I set it back to on when the bulk insert is done.
This works even better!
using the following, i was able to go from ~3000ms inserts for 68 rows and ~330ms for 13 rows, down to ~39ms for 68 rows and ~6ms for 13 rows
final long startTime = System.currentTimeMillis(); try { database.execSQL("PRAGMA synchronous=OFF"); database.setLockingEnabled(false); database.beginTransaction(); for (int i = 0; i < Members.size(); i++) { ih.prepareForInsert(); ih.bind(nameColumn, Members.get(i)); ih.execute(); } database.setTransactionSuccessful(); } finally { database.endTransaction(); database.setLockingEnabled(true); database.execSQL("PRAGMA synchronous=NORMAL"); ih.close(); if (Globals.ENABLE_LOGGING) { final long endtime = System.currentTimeMillis(); Log.i("Time to insert Members: ", String.valueOf(endtime - startTime)); } }PRAGMA synchronous=OFFcan speed up the insertion process quite a lot, but it leaves you vulnerable to database corruption if the application crashes during the bulk insertion. You might want to consider setting some out-of-band flag (i.e., setting a hidden Preference) when you first begin the insertion, and again when you’ve successfully calledPRAGMA synchronous=NORMAL. This would allow you to detect and recover from such corruption.good point Dan, however in cases like this, with inserts taking on average of say, 8-20ms, that is almost fast enough that the likelyhood of anything going wrong (power failure, phone reboot, etc) is almost none. Not zero, but close enough. If you were trying to insert hundreds of thousands of rows however, then I would probably be more inclined to do that, or not even turn off Syncronous as the risk for corruption might be too high.
Thank you for this post. It is very helpful.
Out of interest, how are you reading in the data that goes into the bind statements? I have about 1000 rows (taken from a sqlite .dump) to insert into a table when onCreate is called. The two questions are:
1) How to store the data. In the assets folder as a csv file?
2) In the onCreate method, would you read one row at a time from the csv file into variables and then insert that row, or would you read all rows into an array and then loop over the array to insert the rows?
My data is going into one table with 6 columns. The approx. 1000 rows as a csv file is about 0.5MB to give you an idea of the amount of data.
Thank you.
1) I think storing the data as a CSV file in the assets folder makes sense.
2) If the data is going straight into the database, with no significant changes, then it’s probably easier to read and insert one row at a time. Intuitively, that also seems like it should be faster than using an array, which would add about 0.5MB of GC overhead.
But if performance were critical, I’d want to base my decision on measurements, because intuition could be wrong. Suppose the transaction commit requires writing to three files — the data file, the index, and the log. With disk-based systems, it may well be faster to buffer the data in an array before doing the writes, as that could optimize the disk performance. That issue probably doesn’t arise with flash memory, which most Android environments would be using; but this still illustrates that intuition isn’t necessarily a reliable guide.
Thanks for this.
I had huge performance improvment (2min to 3 secs) by using:
try { mDb.beginTransaction(); for (Value value : values) { ih.prepareForInsert(); ih.bind(colIdx, value.getSomeValue()); // ... ih.execute(); } mDb.setTransactionSuccessful(); } finally { mDb.endTransaction(); }I verify that Francis’s method works amazingly good.
Huge performance improvement when inserting multiple entries across many tables.
Definitely recommended 🙂
I still wonder why Google doesn’t have samples of use at their online documentation…
Francis, you’re a genius. With the OP code, I got similar results (given, at the moment I’m only inserting 13 rows into a DB), but using a time function, it was taking between 400-600ms on average to insert the rows, regarless if I did individual inserts, or the OP’s method. Using your code, it now takes 13-20ms. I’ve got a number of other inserts that have more data, so I’ll do some time tests on those, but i think this will be the answer
A very interesting blog post here that I stumbled upon when looking to speed up my xml -> database processing. I have a couple of questions though (unable to try anything out myself at the moment as I’m at work). Firstly are the ih.insert(ContentValues)/ih.replace(ContentValues) synonimous with a normal db.insert()/replace() or with avoiding doing multiple ih.bind() calls? Secondly, if using ih.bind() what is the behavour for existing data? I.e. does it do an insert() – exception on duplicate key, or replace? (the function prepareForInsert() would seem to suggest the former, but you never know with these things!)
Hi, Tom,
DatabaseUtils.InsertHelperoperates on a single row at a time, much asSQLiteDatabase.insertandSQLiteDatabase.replacedo. The difference is that under the hood,DatabaseUtils.InsertHelperusesSQLiteDatabase.CompileStatement, which improves performance.I have little experience using
DatabaseUtils.InsertHelper.replace. However the documentation for it says:This implies that existing values in each row would be lost via the
replacemethod; the only way to preserve them would be to read them from the DB first, and bind them as you would any updated values before executing the statement.Hi,
Do you know any alternative to InsertHelper for updating rows, without the overhead of first querying the Database and then binding the values we want to keep into a InsertHelper.replace?
Stefan
Strictly speaking (and to clarify what I wrote in the comment above),
InsertHelper.replacedoesn’t require you to query the database before replacing rows. However, it does require that you supply the values for all columns in the row, unless the default value for that column is acceptable. In other words, you need to know what the column values should be, whether by first querying the database or through some other means. There’s no way to selectively update specific columns in the database usingDatabaseUtils.InsertHelper.By the way, I’d suggest thinking of
InsertHelper.replaceas being equivalent toInsertHelper.insert, with two exceptions:1) You start by calling
prepareForReplaceinstead ofprepareForInsert2) If your
ContentValuesincludes the ID column, and a row with the specified ID already exists, then the existing row is deleted before the insertion happens.Thank you for the post. This has helped me a lot. My only question is what do you think is the best way to create the data that we are going to insert? I thought about making a 2d array with the values that I want to be inserted into the table, but for larger projects with 100+ entries it seems inefficient.
Hi, Nick,
It really depends on the nature of the data and your application. If the data is mainly alphanumeric and relatively “flat”, then a simple CSV-style text file may be good enough. However, the CSV format is inherently limited regarding what kinds of data it can handle. Think carefully about your choice of delimiters, and consider the use of an external library such as opencsv to do the parsing.
For somewhat more complicated cases, I’ve been tempted to use YAML a number of times. But as I’ve yet to succumb to that temptation, I can’t really say how well it’d work.
There’s always XML. If you already have reason to be concerned about performance, then XML — especially with a DOM parser — is probably not the best choice. On the other hand, you probably know what they say about premature optimization. If the data seems complex enough to require XML, then give it a try and measure the performance before writing it off.
(But wait: Isn’t this entire post about a possibly premature optimization? No, not really. If you know you need to do bulk insertions, then
InsertHelpercan provide a relatively simple way to implement them — one that just happens to be fast.)[…] ì°¸ê³ http://www.outofwhatbox.com/blog/2010/12/android-using-databaseutils-inserthelper-for-faster-inserti… Like this:LikeBe the first to like this […]
[…] you might want to check out the DatabaseUtils.InsertHelper class. Out Of What Box? has a blog post, Android: Using DatabaseUtils.InsertHelper for faster insertions into SQLite database, that covers this in more detail. Developmentandroid, contentprovider, java, mobile, sqlite […]
[…] Android: Using DatabaseUtils.InsertHelper for faster insertions into SQLite database […]
[…] Android: Using DatabaseUtils.InsertHelper for faster insertions into SQLite database […]
Thanks for this it helped me go from 2000 rows taking 2minutes to about 3 seconds! Big help.
Thanks for the post, Dan. I have a minor problem though: I want to show a progress dialog so I put the beginTransaction into doInBackground of an AsyncTask. Unfortunately, it appears that mDatabase.beginTransaction() runs in EXCLUSIVE mode and the progress dialog (inside onPreExecute) is not shown. Do you know of any way around this?
Hi, Gerrard,
I’d be very surprised if the transaction mode has anything to do with the progress dialog showing (or not.) SQLite’s EXCLUSIVE mode affects how other threads or applications may connect to the database, but it doesn’t affect how other threads execute within your app, especially if those threads aren’t making calls to the database.
There is a
beginTransactionNonExclusivemethod in theSQLiteDatabaseclass, which would use IMMEDIATE mode rather than EXCLUSIVE mode; but again, I doubt that this would make a difference.There are a couple of questions on StackOverflow which might be helpful; see here and here.
Note: In that first link, beware of the line which reads:
ProgressDialog dialog = ProgressDialog.show(shoppingClass.this, "",You probably don’t want to use a local variable for the dialog! It would make it hard, to say the least :-), to dismiss the dialog when you’re done.
Hey Dan, I found the problem with my code. Apparently if you define the InsertHelper and the colums onPreExecute, the dialog does not get shown. However, if you define the InsertHelper and columns in doInBackground, everything works well! I’m not sure why this happens though. Thanks again for your post. It really helped me reduce the insertion time drastically!
Thanks Dan. Tried doing that but the dialog only appears after the inserts are done. I have posted the question on SO
http://stackoverflow.com/questions/9622228/progress-dialog-not-showing-in-asynctask
as well but with no responses. Curiously, if I replace the beiginTransaction idiom with regular execSQL statements, everything works perfectly but the inserts are painfully slow. Its an interesting dilemma. What do you think?
Hi Dan,
Thanks a lot for your tips, it really helps to know some tweaks around the android implementation of sqlite.
I noticed that in all your examples, you don’t close the InserterHelper (ih.close()) at the end of the transactions. Should that not be done manually or will the SQLOpenHelper take care of it? I perform a reset of my database outside the Helper and I get an
IllegalStateExceptio: database sa_db already closedif I try to do the same operation a second time.
Thank you, Stefan. It looks like you’re right that we need to call
InsertHelper.close(). And unlike some other Android database objects,InsertHelperdoes not have afinalize()method that warns you if it’s being GC’d without having been closed first. So this could lead to problems that are difficult to find.I will update the example. Thanks again for catching this!
Hi Dan Breslau, thanks for the tip, i have successfully implemented it in my app, can u tell me how to force stop the insertion process and delete the database.
Hi, Kishore,
If you want to stop the insertion process from within the program, just throw an exception from your
onCreatemethod. (SQLiteOpenHelper.onCreateis called with a transaction already opened. The transaction is committed if you exitonCreatenormally; it’s rolled back if you exit by throwing an exception.) I’m not sure if this would delete the database entirely, but at a minimum it’s left empty and uninitialized;onCreatewould be called again the next time you run your program.If you want to stop it from outside the program, just use the Settings/Apps panel to force stop it. If you do this while
onCreateis running, it would leave the database as I described above — either deleted entirely, or at least uninitialized.I’m getting an error when I try to use this method:
Error: android.database.sqlite.SQLiteException: near “VALUES”: syntax error:, while compiling: INSERT INTO Scores (VALUES (
MyDBHelper helper = new MyDBHelper(this._context, “data.db”);
SQLiteDatabase db = helper.getWritableDatabase();
DatabaseUtils.InsertHelper inserter = new DatabaseUtils.InsertHelper(db, “scores”);
final int id = inserter.getColumnIndex(“id”); //Crashes here.
No idea what I’m doing wrong, any ideas?
I don’t see anything obvious. I’m wondering how you created the database, and whether there might be a column that has an illegal name (such as an SQLite reserved word.) Even then, I’m not sure if that would be causing the problem.
You’ll probably get an answer more quickly by asking on stackoverflow.com. You should probably post a larger code sample, perhaps including the implementation of your
MyDBHelperclass.This is a really great tip, I went from nearly 60 seconds importing from a CSV, to just 1-2 seconds.
Does anyone know the performance difference between this and SQLstatement.?
insertStatement = db.compileStatement(INSERTCOAMMND
insertStatement.clearBindings();
insertStatement.bindString(1, data1);
…
insertStatement.executeInsert();
Thanks
Hey Dan,
Great post. I found your answer on StackOverflow and then when searching for InsertHelper I ran across your post by coincidence. This technique is amazing. It cut my first time app initialization down from 35 seconds to about 2 seconds. First impressions are everything…and this technique made a huge difference. Thanks for taking the time to post the technique along with the detailed evidence and comparisons. Great job!
It does not worth,
here is my code
Vector v = contentLoader.loadRecipes(DishesRecipesOnlinev_MainActivity.requestProType, otherProcedValues); if (v != null) { int vectorSize = v.size() - 1; if (vectorSize >= 6) { LoadDataFromServer loadDataFromServer = new LoadDataFromServer(getBaseContext()); String xc; RecipeListModel rModel = null; byte[] b = null; String[] temp = new String[5]; InsertHelper recipListInsertHelper= new InsertHelper(contentLoader.getDBQueries().getDatabse(), "RECIPES_LIST_TEMP"); int RecipeID= recipListInsertHelper.getColumnIndex("RecipeID"); int RecipeName= recipListInsertHelper.getColumnIndex("RecipeName"); int RecipNoPers= recipListInsertHelper.getColumnIndex("RecipNoPers"); int RecipPrepTime= recipListInsertHelper.getColumnIndex("RecipPrepTime"); int RecipesRate= recipListInsertHelper.getColumnIndex("RecipesRate"); int RecipeIcon= recipListInsertHelper.getColumnIndex("RecipeIcon"); contentLoader.getDBQueries().getDatabse().beginTransaction(); try { for (int i = 0; i < vectorSize;) { recipListInsertHelper.prepareForInsert(); recipListInsertHelper.bind(RecipeID, v.get(i++)); recipListInsertHelper.bind(RecipeName ,v.get(i++)); recipListInsertHelper.bind(RecipNoPers ,v.get(i++)); recipListInsertHelper.bind(RecipPrepTime ,v.get(i++)); recipListInsertHelper.bind( RecipesRate,v.get(i++)); xc = url + v.get(i++) + ".png"; // Log.i("Full url ", xc); try { // b=Util.getRoundedBitmapDrawableFromDrawable(loadDataFromServer // .loadImageFromWeb(xc),20); b = loadDataFromServer.loadBytes(xc); } catch (Exception e) { // TODO: handle exception b = null; } finally { try { recipListInsertHelper.bind(RecipeIcon,b); } catch (Exception e2) { // TODO: handle exception b = null; }finally{ recipListInsertHelper.execute(); } } // recipesList.add(rModel); } contentLoader.getDBQueries().getDatabse().setTransactionSuccessful(); } catch (Exception e) { // TODO: handle exception }finally{ contentLoader.getDBQueries().getDatabse().endTransaction(); recipListInsertHelper.close(); }Hi, Mohammad,
stackoverflow.com is a far better place to ask questions like this, because your question will be seen by many more people than will see it here.
But you also need to give some help to people who want to help you:
You need to describe what kind of error you get. Is an exception being thrown? If so, show the error message and the traceback (or at least show which line in the code is throwing the exception.) Or is the problem that the database is not being updated correctly, or at all?
Also remember that in many cases, people will want to try to run the code themselves to see what’s going wrong. To help them do that, you need to reduce the example to the simplest possible program that still produces the error. For example, remove all of the references to classes that are defined by your application (
contentLoader,.LoadDataFromServer, etc.). Where values are needed, just use made-up values instead. And for database-related questions, you might also want to include the part of your DatabaseHelper (or other class) that initially creates the database table(s).With all of that said, I noticed that some of the column names in the code spell out the word Recipe, while others leave out the second ‘e’ (
RecipeNamevs.RecipNoPers.) I suggest you double-check to make sure that the column names in the code here are exactly the same as the column names that you use to create the database. (Perhaps you should define string constants for these column names.)Hi Dan Breslau ,
I did some testing on my own on an xml file, basically parsing and inserting rows. The xml file content was a tables data with 30 columns and 12k rows of data. I just wanted to share my observations.
Without using TRANSACTIONS and using the normal db.insert function, the process(parsing + insertion) used to take 12-15 mins. After implementing TRASACT, it reduced to a whooping 60s.
Then I used InsertHelper the way u mentioned. But it was always 4-5 seconds higher than the normal db.insert function.
And then I tried using precompiled statements, but it took between 80-90 seconds.
PS: Though I was parsing 30 rows, i inserted only 7 rows.
Without seeing your code, it’s hard to know what’s going on there; but since
InsertHelperis really a pretty thin wrapper aroundSQLiteDatabase.compileStatement, it shouldn’t be that much slower when you switch to usingSQLiteDatabase.compileStatement. (In fact, it really shouldn’t be slower at all.)This is just a guess, but I’m thinking that if using precompiled statements added significantly more time compared to using
InsertHelper, than perhaps you’re compiling the statement every time you iterate through the loop?Great stuff; a very helpful tutorial. Much appreciated.
Why not use ih.insert(contentValues) ?
currently i’m use db.insert(“table”, null, contentValues), then modified by ih.insert(contentValues) and use transaction.
InsertHelper is deprecated since API 17.