What is the fastest way to parse a JSON string into an SQLite table?

Question

I'm writing an Android application which will occasionally need to download a json string of around 1MB and containing around 1000 elements, and parse each of these into an SQLite database, which I use to populate a ListActivity.

Even though the downloading and parsing isn't something that needs to be done on every interaction with the app (only on first run or when the user chooses to refresh the data), I'm still concerned that the parsing part is taking too long, at around two to three minutes - it seems like an eternity in phone app terms!

I'm currently using Gson to parse each json object into a custom object that I've defined, and then using an SQLiteOpenHelper to enter it into the database.

My question is - is there a faster way of implementing this? Would it be noticeably faster to interact with the json directly, without using Gson? Or am I doing something stupid in the code below that's slowing things down?

Here's the method I'm using in my AsyncTask to parse the json to SQLite:

protected Boolean doInBackground(Integer... bType) {

    InputStream source = getJsonInputStream(bTypeString);

    VegDataHandler db = new VegDataHandler(mainActivity, bTypeString);
    Gson gson = new Gson();
    Reader reader = new InputStreamReader(source);

    JsonParser jParser = new JsonParser();
    JsonArray jArray = jParser.parse(reader).getAsJsonArray();

    aLength = jArray.size();
    mCurrProgress = 1;
    publishProgress(mCurrProgress, 0, aLength);

    /* Each array element is of the form { company: {...} } */
    int i = 0;
    mCurrProgress = 2;
    for (JsonElement obj : jArray) {
        Company c = gson.fromJson(obj.getAsJsonObject().getAsJsonObject("company"), Company.class);
        db.addCompany(c);
        i++;
        publishProgress(mCurrProgress, i);
    }
}

This is the addCompany method from my VegDataHandler class, which extends SQLiteOpenHelper:

public void addCompany(Company c) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(KEY_ID, c.getCompanyId());
    values.put(KEY_NAME, c.getCompanyName());
    values.put(KEY_RYG, c.getCompanyRedYellowGreen());
    values.put(KEY_COUNTRY, c.getCompanyCountry());
    values.put(KEY_URL, c.getCompanyUrl());
    values.put(KEY_NOTES, c.getCompanyNotes());
    values.put(KEY_EMAIL, c.getCompanyEmail());
    db.insertWithOnConflict(TABLE_COMPANY, null, values, SQLiteDatabase.CONFLICT_REPLACE);
    db.close();
}

This is the class that holds each json element before adding to the SQLite (I've omitted the getters and setters for brevity).

public class Company {

    public Company() {
    }

    @SerializedName("id")
    public int companyId;

    @SerializedName("company_name")
    public String companyName;

    @SerializedName("red_yellow_green")
    public String companyRedYellowGreen;

    @SerializedName("country")
    public String companyCountry;

    @SerializedName("url")
    public String companyUrl;

    @SerializedName("notes")
    public String companyNotes;

    @SerializedName("email")
    public String companyEmail;

}

Thanks in advance for any replies.

Answer 1

First you need to determine the portion(s) of the process that are eating up the most time. From your comment above it sounds like the JSON parsing is the culprit.

If JSON parsing is the issue:
Research and consider a faster JSON parser. Perhaps something like json-smart.

If SQLite/DB bulk inserts are the issue:
See my answer here

General tips:

• Recycle objects as much as possible (keep new to a minimum)
• Always use transactions in DB bulk inserts at the very least
• Don't open/close the database. Do this once at the start/finish of your processing
• Use pre-compiled statements!