Sqlite insert performance for smartphone
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
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.
You can use Object/Relational Mapping (ORM) tool for better performance. ORMLite, GreenDao etc.
Greendao is better one, have a look
http://greendao-orm.com/
Features :-
Benefits of ORM tools :-
下一篇: Sqlite为智能手机插入性能