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.

Fitton

Have you tried to isolate the reason for delay - whether it is the downloading that is taking time or the parsing? – Whited

Do you want to add bulk records in database? – Rotative

@Whited - it's definitely the parsing that's taking the most time. I'm using the publishProgress() method to show what stage of downloading / parsing / inflating it's at. – Fitton

@PareshMayani - I'm afraid I'm not sure what you mean by adding bulk records? – Fitton

One thing to try would be to keep the db opened till all the records are inserted. What I mean is, move the SQLiteDatabase db = this.getWritableDatabase(); and db.close(); statements outside of the addCompany method. – Whited

@Whited - that's great, thank you very much! This has at least halved the time it takes. I was sure there'd be something simple that I was doing wrong. I've changed my DataHandler to open the database in the constructor and then added another dbClose() method to it, which I call after I've added all the records. If there are any other time/resource saving tips that anyone has, I'm all ears... – Fitton

@Fitton check NuSkooler's answer – Rotative

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!

Hydroelectric

This is great, thanks very much for the tips. I'll look into both json-smart and DB bulk inserts to see if I can shave more valuable seconds off! – Fitton