SQLite on Android

In the past week I started working with SQLite databases on Android.

It can be quite cumbersome to explore the data stored in the DB on an actual device. What has helped me a lot is grabbing the DB from the device and loading the SQLite DB in a third party SQLite browser / manager. This will give you a lot more flexibility on exploring the data that is stored and will also be a great help for testing your queries.
You can retrieve your database from the device using ADB:

  1. adb pull /data/data/[app_package]/databases/[db_file] .

I’ve been using SQLiteBrowser to explore the DB and test my queries.

One thing I noticed is that inserting around a hundred rows took quite some time, about 350ms. I was inserting one row at a time and after some Googling on how to insert multiple rows in one query, I found an answer on StackOverflow. The suggestion was to do something like the following:

  1. INSERT INTO 'tablename'
  2.     SELECT 'data1' AS 'column1', 'data2' AS 'column2'
  3.     UNION SELECT 'data3', 'data4'
  4.     UNION SELECT 'data5', 'data6'
  5.     UNION SELECT 'data7', 'data8'

After trying this approach it cut the insertion time in half near the 170ms mark, still quite slow.
Some more searching brought me to an article about “Speeding up SQLite insert operations“. What this article advises is to use transactions, an example:

  1. database.beginTransaction();
  2. for (...) {
  3.     database.insert(...);
  4. }
  5. database.setTransactionSuccessful();
  6. database.endTransaction();

Using this technique the insertion was sped up to roughly 25ms, much better!

As an alternative to using a database, you could just store the data on disk. This works fine especially when you just use a database to store data and don’t need to sort it or do more complex queries. Even if you needed sorting, you could use the SortedMap class.

Leave a Reply

Your email address will not be published. Required fields are marked *