SQLite - Speed up 4,000 row insert in Android app

Question

public synchronized void updateStop(ArrayList<StopBean> beanList){
    SQLiteDatabase db = getWritableDatabase();
    String query = "DELETE FROM 'stop'";
    db.execSQL(query);
    for(StopBean bean : beanList){
        String atco_code = bean.getAtco_code();
        String name = bean.getName();
        String locality = bean.getLocality();
        String bearing = bean.getBearing();
        String latitude = bean.getLatitude()+"";
        String longitude = bean.getLongitude()+"";

        ContentValues values = new ContentValues();
        values.put("atco_code", atco_code);
        values.put("name", name);
        values.put("locality", locality);
        values.put("bearing", bearing);
        values.put("latitude", latitude);
        values.put("longitude", longitude);
        db.insert("stop", null, values);
    }

    db.close();
}

Currently our application inserts 4,000 rows via the method above. The problem with this method is that it takes 10-15 seconds to actually execute. Clearly this is far too long for a simple 4,000 row insert.

How can I change this method so that it vastly speeds up the execution time of these inserts?

@iRaviiVooda I am new to Java but I do believe this code is already run on another thread, the class `implements Runnable` and the method is invoked in `override run`. — jskidd3, Apr 16 '15 at 22:55
Maybe, this can help you http://stackoverflow.com/questions/7090243/sql-insert-speed-up — Dunes Buggy, Apr 16 '15 at 22:59

score 7 · Accepted Answer · answered Apr 16 '15 at 22:58

Wrap the whole thing in a transaction, using beginTransaction(), endTransaction(), and markTransactionAsSuccessful(). Right now, you are doing one transaction per insert, which means one pulse of disk I/O per insert, and that will be very slow.

Beyond that:

Don't allocate a fresh ContentValues in each pass of the loop
Get rid of the local variables (e.g., atco_code), in case they are not being optimized away
Use more efficient ways of converting a number to a string than "" (e.g., Double.toString()
Experiment with compiling a plain SQL INSERT statement to a SQLiteStatement via compileStatement(), and see if that is better than just calling insert()

If you are still not happy, use Traceview and determine where the rest of your time is being spent.

score 3 · Answer 2 · edited May 23 '17 at 12:32

In recent versions of sqlite (which is Kit Kat, Jelly Bean, and Lollipop, according to the emulators) you can use multi-insert statement:

INSERT INTO table (col1, col2) VALUES 
  ('row1col1', 'row1col2'), ('row2col1', 'row2col2');

If you're importing static trusted data, then include the sqlite script with the data in a file with your app and just run it at first startup. Note: never concatenate SQL values into SQL queries as that opens you up for database injection (and it's not going to be faster either as you and sqlite will be parsing the data twice and do an unnecessary reserialization).

If you need to insert data that are generated dynamically or if you need to support older versions, then you should precompile the SQL query with prepared statements and use bind variables. Example:

db.beginTransaction();
String sql = "INSERT INTO stop(atco_code, name, locality, bearing, latitude, longitude) VALUES (?,?,?,?,?,?)";
SQLiteStatement stmt = db.compileStatement(sql);

for(StopBean stop : stopList){
    stmt.bindString(1, stop.getAtco_code());
    stmt.bindString(2, stop.getName());
    stmt.bindString(3, stop.getLocality();
    stmt.bindString(4, stop.getBearing());
    stmt.bindDouble(5, stop.getLatitude());
    stmt.bindDouble(6, stop.getLongitude());

    stmt.execute();
    stmt.clearBindings();
}

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

In all cases, you should wrap all the inserts in a single transaction. Using a single transaction allows the database to cache the values in the fast RAM and flush the data only when either the cache is full or when you finish rather than flushing the data to persistent storage for every insert statements.

SQLite - Speed up 4,000 row insert in Android app

2 Answers2