Android SQLite database: slow insertion

Question

I need to parse a fairly large XML file (varying between about a hundred kilobytes and several hundred kilobytes), which I'm doing using Xml#parse(String, ContentHandler). I'm currently testing this with a 152KB file.

During parsing, I also insert the data in an SQLite database using calls similar to the following: getWritableDatabase().insert(TABLE_NAME, "_id", values). All of this together takes about 80 seconds for the 152KB test file (which comes down to inserting roughly 200 rows).

When I comment out all insert statements (but leave in everything else, such as creating ContentValues etc.) the same file takes only 23 seconds.

Is it normal for the database operations to have such a big overhead? Can I do anything about that?

Solution 1

You should do batch inserts.

Pseudocode:

db.beginTransaction();
for (entry : listOfEntries) {
    db.insert(entry);
}
db.setTransactionSuccessful();
db.endTransaction();

That increased the speed of inserts in my apps extremely.

Update:
@Yuku provided a very interesting blog post: Android using inserthelper for faster insertions into sqlite database

Solution 2

Since the InsertHelper mentioned by Yuku and Brett is deprecated now (API level 17), it seems the right alternative recommended by Google is using SQLiteStatement.

I used the database insert method like this:

database.insert(table, null, values);

After I also experienced some serious performance issues, the following code speeded my 500 inserts up from 14.5 sec to only 270 ms, amazing!

Here is how I used SQLiteStatement:

private void insertTestData() {
    String sql = "insert into producttable (name, description, price, stock_available) values (?, ?, ?, ?);";

    dbHandler.getWritableDatabase();
    database.beginTransaction();
    SQLiteStatement stmt = database.compileStatement(sql);

    for (int i = 0; i < NUMBER_OF_ROWS; i++) {
        //generate some values

        stmt.bindString(1, randomName);
        stmt.bindString(2, randomDescription);
        stmt.bindDouble(3, randomPrice);
        stmt.bindLong(4, randomNumber);

        long entryID = stmt.executeInsert();
        stmt.clearBindings();
    }

    database.setTransactionSuccessful();
    database.endTransaction();

    dbHandler.close();
}

Solution 3

Compiling the sql insert statement helps speed things up. It can also require more effort to shore everything up and prevent possible injection since it's now all on your shoulders.

Another approach which can also speed things up is the under-documented android.database.DatabaseUtils.InsertHelper class. My understanding is that it actually wraps compiled insert statements. Going from non-compiled transacted inserts to compiled transacted inserts was about a 3x gain in speed (2ms per insert to .6ms per insert) for my large (200K+ entries) but simple SQLite inserts.

Sample code:

SQLiteDatabse db = getWriteableDatabase();

//use the db you would normally use for db.insert, and the "table_name"
//is the same one you would use in db.insert()
InsertHelper iHelp = new InsertHelper(db, "table_name");

//Get the indices you need to bind data to
//Similar to Cursor.getColumnIndex("col_name");                 
int first_index = iHelp.getColumnIndex("first");
int last_index = iHelp.getColumnIndex("last");

try
{
   db.beginTransaction();
   for(int i=0 ; i<num_things ; ++i)
   {
       //need to tell the helper you are inserting (rather than replacing)
       iHelp.prepareForInsert();

       //do the equivalent of ContentValues.put("field","value") here
       iHelp.bind(first_index, thing_1);
       iHelp.bind(last_index, thing_2);

       //the db.insert() equilvalent
       iHelp.execute();
   }
   db.setTransactionSuccessful();
}
finally
{
    db.endTransaction();
}
db.close();

Solution 4

If the table has an index on it, consider dropping it prior to inserting the records and then adding it back after you've commited your records.

Solution 5

If using a ContentProvider:

@Override
public int bulkInsert(Uri uri, ContentValues[] bulkinsertvalues) {

    int QueryType = sUriMatcher.match(uri);
    int returnValue=0;
    SQLiteDatabase db = mOpenHelper.getWritableDatabase();

     switch (QueryType) {

         case SOME_URI_IM_LOOKING_FOR: //replace this with your real URI

            db.beginTransaction();

            for (int i = 0; i < bulkinsertvalues.length; i++) {
                //get an individual result from the array of ContentValues
                ContentValues values = bulkinsertvalues[i];
                //insert this record into the local SQLite database using a private function you create, "insertIndividualRecord" (replace with a better function name)
                insertIndividualRecord(uri, values);    
            }

            db.setTransactionSuccessful();
            db.endTransaction();                 

            break;  

         default:
             throw new IllegalArgumentException("Unknown URI " + uri);

     }    

    return returnValue;

}

Then the private function to perform the insert (still inside your content provider):

       private Uri insertIndividualRecord(Uri uri, ContentValues values){

            //see content provider documentation if this is confusing
            if (sUriMatcher.match(uri) != THE_CONSTANT_IM_LOOKING_FOR) {
                throw new IllegalArgumentException("Unknown URI " + uri);
            }

            //example validation if you have a field called "name" in your database
            if (values.containsKey(YOUR_CONSTANT_FOR_NAME) == false) {
                values.put(YOUR_CONSTANT_FOR_NAME, "");
            }

            //******add all your other validations

            //**********

           //time to insert records into your local SQLite database
           SQLiteDatabase db = mOpenHelper.getWritableDatabase();
           long rowId = db.insert(YOUR_TABLE_NAME, null, values);           

           if (rowId > 0) {
               Uri myUri = ContentUris.withAppendedId(MY_INSERT_URI, rowId);
               getContext().getContentResolver().notifyChange(myUri, null);

               return myUri;
           }


           throw new SQLException("Failed to insert row into " + uri);


    }

View more solutions

Comments

[benvd]

Inserting all ContentValues in this way only takes a second or two. Thanks a lot!

[Graham Borland]

Is it safe to have a long-running transaction open, covering the whole duration of the XML parsing operation, and then committing it at the end? Or should the list of insertions be locally cached inside the XML parser and then have a short-lived transaction opened and committed once parsing is complete?

[stefs]

wrapping my 60 inserts with a transaction increased the performance 10x. wrapping it with a transaction and using a prepared statement (SQLiteStatement) increased it 20x!

[Bear]

benvd thanks for the comment. I was inserting 20k records, it took about 8mins but after using a transaction it takes only 20 seconds :-)

[stealthcopter]

I just did this and it was taking 53s before and now only 0.05s to insert ~200 fields. Shockingly awesome!

[Austyn Mahoney]

I brought 2300 inserts from 60s to 1.4s using compiled statements and a transaction for every 1000 inserts. If I used one transaction for everything, Android would throw an error saying the HeapWorker is wedged (the finalizer() in SQLiteCursor is taking too long apparently). Also I fixed the code error in the answer now that we can edit.

[Russell Ladd]

This blog entry discusses another optimization using the almost-hidden InsertHelper outofwhatbox.com/blog/2010/12/…

[Zitrax]

I had 618 inserts taking 6s, with transactions they took 3s. InsertHelper made it slightly slower for some reason.

[befzz]

+1 for me works beauty! (using sqlite3 as child process throught stdin) ^_^ thank u

[Jacob Ras]

Works great. 20000 inserts (dummy data) took half an hour, now only a couple of seconds.

[Display name]

One catch to avoid here: the index in bindString is 1-based and not 0 based

[Daniel Bejan]

@qefzec Thanks you for this solution..This just dropped the insertion time in my app from 78 seconds to 4 for 900 rows added..

[Vasil Valchev]

and how to add ContentValue in iHelp.bind(first_index, thing_1); ?

[TomeeNS]

Thank You Sir. 20000 records with 6 fields of data each, including VARCHAR(80) from 4 minutes to 8 seconds. Actually this should be marked as best answer, IMHO.

[Mohamd Ali]

to store 2790 records take 24307ms, with this it's take 1071ms thanks :D

[Frank]

Great! Our benchmark on 200 test inserts at a time with 15 columns per insert generated between 4100% and 10400% improvement depending on the device and internal/external memory. Prior performance would have doomed our project before it got off the ground.

[DoruChidean]

from 15min down to 45s for 13600 rows

[Tarek El-Mallah]

This work for me and incredibly increase the speed for large number of db.replace

[live-love]

About how long would this take for one million rows?