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

Question

I am writing an Android application that sometimes needs to load a json string of about 1 MB and contain about 1000 elements and analyze each of them in the SQLite database, which I use to populate ListActivity.

Despite the fact that loading and parsing are not something that needs to be done each time you interact with the application (only when you first start it or when the user selects to update the data), I'm still worried that the parsing part is taking too long for about two to three minutes - it seems like an eternity in terms of the phone!

I am currently using Gson to parse every json object in a user object that I defined, and then using SQLiteOpenHelper to enter it into the database.

My question is: is there a faster way to implement this? Would it be noticeably faster to directly interact with json without using Gson? Or am I doing something stupid in the code below, which slows down?

Here is the method I use in my AsyncTask to parse json for 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 that 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 each json element contains before being added to SQLite (getters and setters are omitted 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 answers.

Answer 1

First you need to identify the part (s) of the process that travel the most. From your comment above it sounds like parsing JSON is the culprit.

If JSON parsing is a problem:
Explore and review the faster JSON parser. Maybe something like json-smart .

If the problem is with bulk SQLite / DB inserts:
See my answer here

General tips:

• Reinstall objects as much as possible (minimize new )
• Always use transactions in bulk DB inserts at least
• Do not open or close the database. Do this once at the beginning / end of processing.
• Use precompiled instructions!