Sqlite insert performance for smartphone

Question

I'm trying to optimize the insertion speed for sqlite in android. I've tried numerous methods. Like setting the pragma settings, journal mode and etc.

Besides that, i've also tried individual inserts, prepared statement and bulk insert. Keep in mind that all of those has already been wrapped in a transaction.

The insertion speed in my smartphone(Samsung Galaxy SIII) is only up to about 250 rows per second for a 20 column table. According to Sqlite faq, sqlite can easily insert 50,000 inserts per second. Therefore, i'm really optimistic about the insert to have at least up to the thousands rather than hundreds. However, none of the above mentioned works. Or 250 rows of inserts is already optimized?

Please help me. Thanks.

Regards, Dexter

possible duplicate of Improve INSERT-per-second performance of SQLite? — Simon
sqlite can easily insert 50,000 inserts per second ... on which hardware? Maybe on a 2000 MHz Octacore. — Phantômaxx

danny117 danny117 · Accepted Answer · 2014-12-28T18:36:46

One method I use on big company sized databases to increase bulk insert speed is to turn off indexing while doing bulk inserts. The drawback is you need to have exclusive access to the database and you have to turn indexing back on when your done.

In a mobile app you could easily get exclusive access to the database by putting up an "Updating the app" screen.

SQLITE you can drop any non unique indexes before performing the bulk insert. If your 20 column table has 20 non unique indexes (one for each column). Drop the 20 indexes. Perform the bulk inserts. Create the 20 indexes you previously dropped.

Also make sure your code is compact and doesn't do anything inside the insert loop that could be done before the loop. For example look up column indexes before your insert loop.

If this doesn't apply to your app just catalog it away as something you may need to do in the future on your back end database.

Sqlite insert performance for smartphone

2 Answers