Optimize JSON insertion to SQLite (insert ... on duplicate key ignore)

Question

In current task, I'm refactoring the code of converting JSON file into SQLite database on Android device. Code compliant with Java 6.

As a benchmark, grabbing the code from remote server takes ~1 second, but parsing it and saving to database takes up to 1 minute

In file there is approximately 100 branches, where is has 2 services, which is like 300 queries. Each query takes up to 100 ms

JSONObject response = new JSONObject(coupons);
JSONArray branches = response.getJSONArray("branches");
String lastTimestamp = "";
for(int i = 0; i < branches.length(); i++){
    JSONObject branch = branches.getJSONObject(i);
    JSONObject b = branch.getJSONObject("Branch");
    // add Branch
    lastTimestamp = b.getString("timestamp");
    addBranch(
            b.getInt("id"),
            b.getString("longitude"),
            b.getString("latitude"),
            b.getString("heading"),
            b.getString("address"),
            b.getString("opening_hours"),
            b.getString("region"),
            b.getString("country"),
            b.getString("phone"),
            b.getString("type"),
            lastTimestamp
            );

            // add Services
    JSONArray services = branch.getJSONArray("Service");
    for(int o = 0; o < services.length(); o++){
        JSONObject s = services.getJSONObject(o);
        addService(
            s.getInt("id"),
            s.getInt("branch_id"),
            s.getString("name")
            );
    }
    // end loop adding branches & services
}

where methods used to insert to database are:

private long addBranch(
        int remote_id,
        String longitude,
        String latitude,
        String heading,
        String address,
        String opening_hours,
        String region,
        String country,
        String phone,
        String type,
        String timestamp
        ){
    ContentValues values = new ContentValues();
    values.put(BranchSQL.COLUMN_REMOTE_ID, remote_id);
    values.put(BranchSQL.COLUMN_LONGITUDE, longitude);
    values.put(BranchSQL.COLUMN_LATITUDE, latitude);
    values.put(BranchSQL.COLUMN_HEADING, heading);
    values.put(BranchSQL.COLUMN_ADDRESS, address);
    values.put(BranchSQL.COLUMN_OPENING_HOURS, opening_hours);
    values.put(BranchSQL.COLUMN_REGION, region);
    values.put(BranchSQL.COLUMN_COUNTRY, country);
    values.put(BranchSQL.COLUMN_PHONE, phone);
    values.put(BranchSQL.COLUMN_TYPE, type);
    long insertId = getDatabase().insertWithOnConflict(BranchSQL.TABLE_BRANCHES, null, values, SQLiteDatabase.CONFLICT_IGNORE);
    return insertId;
}

private long addService(int remote_id, int branch_id, String name){
    ContentValues values = new ContentValues();
    values.put(BranchSQL.COLUMN_REMOTE_ID, remote_id);
    values.put(BranchSQL.COLUMN_BRANCH_ID, branch_id);
    values.put(BranchSQL.COLUMN_NAME, name);
    long insertId = getDatabase().insertWithOnConflict(BranchSQL.TABLE_SERVICES, null, values, SQLiteDatabase.CONFLICT_IGNORE);
    return insertId ;
}

code creating structure of tables:

private static final String BRANCHES_CREATE = "create table " + TABLE_BRANCHES
        + "( " + COLUMN_ID + " integer primary key autoincrement, "
        + COLUMN_REMOTE_ID + " integer unique, "
        + COLUMN_LONGITUDE + " text not null, "
        + COLUMN_LATITUDE + " text not null, "
        + COLUMN_HEADING + " integer, "
        + COLUMN_ADDRESS + " text not null, "
        + COLUMN_OPENING_HOURS + " text not null, "
        + COLUMN_REGION + " text not null, "
        + COLUMN_COUNTRY + " text not null, "
        + COLUMN_PHONE + " text not null, "
        + COLUMN_TYPE + " text not null, "
        + COLUMN_TIMESTAMP + " text null);";

private static final String SERVICES_CREATE = "create table " + TABLE_SERVICES
        + "( " + COLUMN_ID + " integer primary key autoincrement, "
        + COLUMN_REMOTE_ID + " integer unique, "
        + COLUMN_BRANCH_ID + " integer, "
        + COLUMN_NAME + " text not null );";

Note that almost all fields in BRANCH type are text, to not slowing down inserting rows by parsing Integer, Double, Date, ... values

This is probably the simplest way to do it. But it looks like it is also the most in-effective way.

Second way, I can think about, is using Gson or Jackson to parse whole branch&services object to Java Object, but this looks to me not very memory/cpu usage friendly.

Can I please kindly ask for your optimizing tips?

Answer 1

You're trying to do a bulk insert, which is notoriously slow on many databases if you're not careful. Indeed, each call to insertWithOnConflict() creates a new transaction, then inserts, then closes the transaction. Instead, use beginTransaction() and endTransaction() to have only a single transaction (the doc explains how to use them with a try/catch block).

Up to API 16, it was possible to use DatabaseUtils.InsertHelper, but this is now deprecated. This blog post uses DatabaseUtils.InsertHelper, which is not a good idea anymore. But it has other interesting performance tweaks you could try out:

• Don’t bind empty columns to avoid producing useless SQL. This will lead to a speedup if you often have empty columns. (half of the author columns are empty and he reports a 30% speedup).
• Disable thread locking temporarily: for various reasons, this is very dangerous, and should only be attempted if you know the implications, can prove it won't do any harm, and really need the ~30% speedup reported by the author.

Of course, there are probably other optimisations, like trying to do this in a background thread, doing this only once when the user doesn't mind. If the speedups provided by the above solutions really don't help, displaying a progress bar can make your app feel like it's doing something important, which will be less frustrating for the user.