12

I have been spending quite some time looking at some performance issues on our device, and noticed that we have quite a few apps all doing db reads/writes..

I started by using the Contacts API to insert new contacts & data rows, and it was painfully slow. 1 minute 18 seconds to insert about 1500 rows (250 raw contacts & 1250 data rows)..

I had used the insert helper in another app for performance inserts, and decided to write a test app which would write to separate db's w/ separate insert methods.

Each db has one table, each w/ 4 columns : _ID, Name, Time, and Blob (all of type 'string') - just like contact provider defines the data columns.

_ID is auto increment pk, Name just inserts the same thing '1234567890', time is just the current system time in milis, and BLob is a string w/ length 6400 full of the letter 'A'...

I first checked the bulk insert, but all it does is loops through all the inserts you have defined, and is just as slow as doing the inserts individually (or negligible performance impact)..

I tested 3 different methods to do the inserts : ContentValues w/ db.insert method : SQLiteStatement w/ statement.execute() (done inside a transaction). SqliteInsertHelper w/ transaction.

I can provide some code, but I got the best performance out of the InsertHelper, and wondering why it was deprecated :

Time to insert 100 records ContentValues : 7.778 seconds ( 82 bytes written / ms ) SQLiteStatement : 1.311 seconds ( 489 bytes written / ms ) SqliteInsertHElper : 0.292 seconds (2197 bytes written / ms)

Any ideas?

BenMorel
  • 34,448
  • 50
  • 182
  • 322
Chrispix
  • 17,941
  • 20
  • 62
  • 70
  • I did some additional testing, and did a regular insert, but made it part of a transaction, this improved the performance greatly. Looks like some code is probably necessary to yield (looking at contacts provider). – Chrispix Mar 12 '13 at 13:19
  • Given the performance problem you mentioned, transactions would have been my first suggestion (each transaction requires round-trip IO waits, each insert outside of a transaction is implicitly wrapped in one). Past that, this might be helpful reading: http://stackoverflow.com/questions/14344172/android-bulk-insert-when-inserthelper-is-deprecated – rutter Oct 17 '13 at 20:11
  • I would have suggested transactions as well. The inserts I've tested with transactions have been quite fast. – Luis Oct 30 '13 at 04:43

3 Answers3

7

It's hard to come by any information on why InsertHelper was deprecated without going to the actual commit that does deprecate it. The engineer that deprecated InsertHelper gave the following reason:

This class does not offer any advantages over SQLiteStatement and just makes code more complex and error-prone.

After refactoring from InsertHelper to SQLiteStatement I agree. One exception are for null-safe binding functions. Whereas InsertHelper automatically calls bindNull() for you, SQLiteStatement crashes if you pass, for example, a null String and you have to do your own null check before calling bindString().

See: https://android.googlesource.com/platform/frameworks/base/+/b33eb4e%5E!/

Fabian Frank
  • 1,115
  • 13
  • 14
0

InsertHelper allows users to do multiple inserts into a table using the same statement.But it is not a good way to insert as such as it is not thread-safe.

Pihu
  • 1,041
  • 11
  • 35
  • 5
    according to http://developer.android.com/reference/android/database/sqlite/SQLiteStatement.html, the alternative recommendation is not thread safe either – Evan R. Nov 19 '13 at 07:32
0

You should use transactions.. If you don't explicitly create a transaction for a database operation the framework creates one for each. Group your object together and insert them all at once. This will greatly increase performance.

interlude
  • 843
  • 8
  • 29