Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

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?

Final solution

Due to project license, I'm posting only pseudo-code

function fillDatabase(String stringWithJson){

SQLiteDatabase databaseInstance = getDatabase();
MyWrapperObject mwo = new Gson().fromJson(stringWithJson, MyWrapperObject.class);

InsertHelper helper1 = new InsertHelper(databaseInstance, "firstTableName");
InsertHelper helper2 = new InsertHelper(databaseInstance, "otherTableName");

// here bind all column names to it's index
// showing only example:
final int SURNAME_COLUMN = helper1.bind("surnameColumnName");
// etc...

databaseInstance.beginTransaction();

try{

    // walk through array and use both InsertHelper instances
    // example
    Iterator<MyType> iterator = mwo.items.iterator();
    while(iterator.hasNext()){
        helper1.prepareForInsert();
        helper1.bind(SURNAME_COLUMN, "surname column value");
        helper1.execute();

        helper2.prepareForInsert();
        helper2.bind(ATNOHER_COLUMN, "another column value");
        helper2.execute();
    }
    // if everything went OK, we will confirm, whole transaction is successful
    databaseInstance.setTransactionSuccessful();
} finally {
    // execute or screw all inserted transactions
    databaseInstance.endTransaction();
}

}
share|improve this question

1 Answer 1

up vote 4 down vote accepted

You're trying to do a Bulk insert, which is notiorously slow on many databases if you're simply using INSERT. Two different methods you could try out (not at the same time) that could lead to a 5x speedup:

  • Single transaction : each call to insertWithOnConflict() creates a new transaction, then inserts, then closes it. Use beginTransaction() and endTransaction() (the doc explains how to use them with a try/catch block).
  • DatabaseUtils.InsertHelper : also uses a single transaction, but also compiles the SQL only once, probably using stored procedure. This blog post has example code you could inspire yourself from.

The blog post about DatabaseUtils.InsertHelper has other interesting performance tweaks you could try out:

  • Don’t bind empty columns, which doesn't produce useless SQL, and 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 quite dangerous, and should only be attempted if you know the implication, 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.

share|improve this answer
    
Oh gosh, sorry, see code posted under my question. I've took your suggestions and now it takes under 5 seconds whatever :)) Thanks –  Marek Sebera Mar 7 '12 at 15:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.