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

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(Bran

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.

StackExchange Code Review Q#9766, answer score: 7