{"id":680,"date":"2010-12-15T15:34:33","date_gmt":"2010-12-15T20:34:33","guid":{"rendered":"http:\/\/www.outofwhatbox.com\/blog\/?p=680"},"modified":"2012-03-19T21:26:55","modified_gmt":"2012-03-20T02:26:55","slug":"android-using-databaseutils-inserthelper-for-faster-insertions-into-sqlite-database","status":"publish","type":"post","link":"https:\/\/www.outofwhatbox.com\/blog\/2010\/12\/android-using-databaseutils-inserthelper-for-faster-insertions-into-sqlite-database\/","title":{"rendered":"Android: Using DatabaseUtils.InsertHelper for faster insertions into SQLite database"},"content":{"rendered":"

AndroidOS includes the DatabaseUtils.InsertHelper<\/code><\/a> 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<\/code> a little less mysterious than it apparently has been.<\/p>\n

It’s often the case that bulk insertions are performed in the onCreate<\/code><\/a> method of an SQLiteOpenHelper<\/code><\/a>, so I’ll use that context for this example. (This has implications on transaction management, which in turn affects performance, as described below.)<\/p>\n

Suppose the onCreate<\/code> method currently looks something like this:<\/p>\n

\r\nprivate class DatabaseHelper extends SQLiteOpenHelper {\r\n    @Override\r\n    public void onCreate(SQLiteDatabase db) {\r\n        ContentValues values = new ContentValues();\r\n        while (moreRowsToInsert) {\r\n            \/\/ ... create the data for this row (not shown) ...\r\n\r\n            \/\/ Add the data for each column\r\n            values.put("Greek", greekData);\r\n            values.put("Ionic", ionicData);\r\n            \/\/ ...\r\n            values.put("Roman", romanData);\r\n\r\n            \/\/ Insert the row into the database.\r\n            db.insert("columnTable", null, values);\r\n        }\r\n    }\r\n    \/\/...\r\n}\r\n<\/pre>\n
Using DatabaseUtils.InsertHelper<\/code>, this would be re-written as:<\/p>\n
\r\nimport android.database.DatabaseUtils.InsertHelper;\r\n\/\/...\r\nprivate class DatabaseHelper extends SQLiteOpenHelper {\r\n    @Override\r\n    public void onCreate(SQLiteDatabase db) {\r\n        \/\/ Create a single InsertHelper to handle this set of insertions.\r\n        InsertHelper ih = new InsertHelper(db, "columnTable");\r\n\r\n        \/\/ Get the numeric indexes for each of the columns that we're updating\r\n        final int greekColumn = ih.getColumnIndex("Greek");\r\n        final int ionicColumn = ih.getColumnIndex("Ionic");\r\n        \/\/...\r\n        final int romanColumn = ih.getColumnIndex("Roman");\r\n\r\n        try {\r\n            while (moreRowsToInsert) {\r\n                \/\/ ... Create the data for this row (not shown) ...\r\n\r\n                \/\/ Get the InsertHelper ready to insert a single row\r\n                ih.prepareForInsert();\r\n\r\n                \/\/ Add the data for each column\r\n                ih.bind(greekColumn, greekData);\r\n                ih.bind(ionicColumn, ionicData);\r\n                \/\/...\r\n                ih.bind(romanColumn, romanData);\r\n\r\n                \/\/ Insert the row into the database.\r\n                ih.execute();\r\n            }\r\n        }\r\n        finally {\r\n            ih.close();  \/\/ See comment below from Stefan Anca\r\n        }\r\n    }\r\n    \/\/...\r\n}\r\n<\/pre>\n
As this shows, using InsertHelper<\/code> is barely more complicated than using SQLiteDatabase.insert<\/code><\/a>. The major differences are that you need to call     ih.prepareForInsert()<\/code> before adding (“binding”) the column data; and you need to obtain each column’s numeric index, which we get by calling ih.getColumnIndex()<\/code> prior to the loop.<\/p>\n
After replacing SQLiteDatabase.insert<\/code> with DatabaseUtils.InsertHelper<\/code>, 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.)<\/p>\n
InsertHelper <\/code>isn’t really doing anything magical here. It’s essentially a wrapper around compiled statements, which you can create yourself using SQLiteDatabase.compileStatement<\/code><\/a>. Most people will probably find InsertHelper<\/code> easier to use, though.<\/div>\n
Other ways to speed up insertions<\/h3>\n
In addition to that gain, two more changes then brought the insertion speed to well over 900 rows per second. Whether these tricks<\/s> techniques work for you will depend on your application.<\/p>\n
Don’t bind empty columns<\/h4>\n
In my app, the data for at least 50% of the columns is empty. By skipping the call to ih.bind()<\/code> when the column data is a null<\/code> or empty string, I saw a roughly 30% performance boost.<\/p>\n
Temporarily disable database thread locking<\/h4>\n
I’m loading the database during the onCreate<\/code><\/a> method of my app’s SQLiteOpenHelper<\/code>. During this time, it seems safe to assume that only one thread is accessing the database, so I use SQLiteDatabase.setLockingEnabled()<\/code><\/a> to temporarily (let me emphasize that: temporarily<\/strong><\/em>) disable thread locks within the database API. This yielded about a 35% performance gain:<\/p>\n
\r\n    public void onCreate(SQLiteDatabase db) {\r\n    \/\/...\r\n    try {\r\n        \/\/ *Temporarily* (have I emphasized that enough?) disable\r\n        \/\/ thread locking in the database. Be sure to re-enable locking \r\n        \/\/ within a finally block.\r\n        db.setLockingEnabled(false);\r\n        \/\/ ... load the database ...\r\n    }\r\n    finally {\r\n        db.setLockingEnabled(true);\r\n    }\r\n<\/pre>\n
Transactions and performance<\/h3>\n
A number<\/a> of<\/a> people<\/a> have cited performance gains through use of explicit transactions in SQLite. However, SQLiteOpenHelper<\/code><\/a> creates a transaction before invoking its callback methods  (onCreate<\/code>, onUpgrade<\/code>, and onOpen<\/code>), so explicit transaction control is unnecessary within those methods. (SQLiteOpenHelper<\/code> will assume that the transaction was successful unless your method throws an exception.)<\/p>\n
You would<\/strong> need to manage your own transactions if your insertion code is running outside of one of SQLiteOpenHelper<\/code>‘s callback methods. The main APIs for this are SQLiteDatabase.beginTransaction<\/code><\/a>, SQLiteDatabase.setTransactionSuccessful<\/code><\/a>, and SQLiteDatabase.endTransaction<\/code><\/a>.<\/p>\n
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 \u00e2\u20ac\u201d the first of these being the transaction that was opened by SQLiteOpenHelper<\/code> \u00e2\u20ac\u201d then opening a new one. This didn’t yield much improvement, if any.<\/p>\n","protected":false},"excerpt":{"rendered":"
Using DatabaseUtils.InsertHelper, plus a couple other performance tweaks, yielded a nearly factor of ten performance improvement for bulk database insertions on AndroidOS.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[33],"tags":[51,40,41,43,44,42],"_links":{"self":[{"href":"https:\/\/www.outofwhatbox.com\/blog\/wp-json\/wp\/v2\/posts\/680"}],"collection":[{"href":"https:\/\/www.outofwhatbox.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.outofwhatbox.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.outofwhatbox.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.outofwhatbox.com\/blog\/wp-json\/wp\/v2\/comments?post=680"}],"version-history":[{"count":14,"href":"https:\/\/www.outofwhatbox.com\/blog\/wp-json\/wp\/v2\/posts\/680\/revisions"}],"predecessor-version":[{"id":685,"href":"https:\/\/www.outofwhatbox.com\/blog\/wp-json\/wp\/v2\/posts\/680\/revisions\/685"}],"wp:attachment":[{"href":"https:\/\/www.outofwhatbox.com\/blog\/wp-json\/wp\/v2\/media?parent=680"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.outofwhatbox.com\/blog\/wp-json\/wp\/v2\/categories?post=680"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.outofwhatbox.com\/blog\/wp-json\/wp\/v2\/tags?post=680"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}