CODEWITHSUNDEEP
javaandroidsqlite
jskidd3
jskidd3

Reputation: 4783

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

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?

Upvotes: 1

Views: 1674

Answers (2)

Lie Ryan
Lie Ryan

Reputation: 64847

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.

Upvotes: 3

CommonsWare
CommonsWare

Reputation: 1006744

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.

Upvotes: 7

Related Questions