Optimizing mass inserts into SQLite I am trying to optimize inserts from a returned JSON which contains data from multiple tables on the remote server. My code is working, but I just want to figure out a way for it to work better. I need the structure to be very dynamic (i.e. fetching the table names from JSON then looping through to complete the inserts into SQLite). This is so if a table is added in the future, I will not have to change any code.

private boolean prepReturnedData(String newString) throws JSONException {
     long errCheck = 0; // tracks valid db inserts.

     JSONArray columns = new JSONArray();

     JSONObject jsonObj = new JSONObject(newString);
     ArrayList&lt;String&gt; tables = new ArrayList&lt;String&gt;();


     Iterator&lt;?&gt; tableKeys = jsonObj.keys();

     while (tableKeys.hasNext()) {
         tables.add(tableKeys.next().toString());
     }
     for (String table : tables) { // Loop through table names.
         columns = jsonObj.getJSONArray(table);
         for (int index = 0; index &lt; columns.length(); index++) { // Loop through Array of Entries

             ContentValues cv = new ContentValues(); // Figure out where to put this.

             Iterator&lt;?&gt; iter = columns.getJSONObject(index).keys(); // Creates an Iterator containing Column names.
             JSONObject newObj = columns.getJSONObject(index); // Creates a new JSONObject for retrieving column values.

             while (iter.hasNext()) { // Iterates to get Column names.
                 String tColumn = iter.next().toString(); // Stores column name, for retrieval of column value.

                 cv.put(tColumn, newObj.get(tColumn).toString());

             } // end column iterator loop.

             String status = null;
             if (table == "Seizures") {
                 status = "timestamp";
             } else {
                 status = "name";
             }
             try {
                 SQLiteDatabase db = this.getWritableDatabase();                
                 errCheck = db.replace(table, status, cv);

                 if (db.isOpen()); {db.close(); };
             } catch (Exception e) {
                 // TODO Auto-generated catch block
                 e.printStackTrace();
             }
         } // end entry loop
     } // end table loop
     if (errCheck &gt;= 0) {
         return true;
     } else {
         return false;
     }
}


This function is within a DatabaseHelper class.
I suggest some optimizations:


Do not initialize columns with empty JSONArray - it will always be overwritten.
Do not iterate twice and do not create overhead ArrayList object you can iterate json object once.
Due to Android specific garbage collection it's better to reuse object than create a new one in each iteration (see where is declared ContentValues cv = new ContentValues(); and cleared then later).
Open database once not in loop -> be sure to close it (finally block).
Your error reporting is leaky - the next db.replace would clear off the former errCheck value - I added some simple handling but I did not know the requirements in that area.

private boolean prepReturnedData(String newString) throws JSONException {
    JSONObject jsonObj = new JSONObject(newString);

    if(jsonObj.length() &gt; 0) {
        Iterator&lt;?&gt; tableKeys = jsonObj.keys();
        SQLiteDatabase db = null;

        try {
            db = getWritableDatabase();
            db.beginTransaction();

            ContentValues cv = new ContentValues();

            String table;
            JSONArray columns;

            String status;
            int columnsLength;
            JSONObject columnJson;
            Iterator&lt;?&gt; columnKeys;

            String tColumn;

            while (tableKeys.hasNext()) {
                table = tableKeys.next().toString();
                columns = jsonObj.getJSONArray(table);
                status = table == "Seizures" ? "timestamp" : "name";
                columnsLength = columns.length();

                for (int i = 0; i &lt; columnsLength; i++) { // Loop through Array of Entries
                    cv.clear();

                    columnJson = columns.getJSONObject(i); // Creates a new JSONObject for retrieving column values.
                    columnKeys = columnJson.keys(); // Creates an Iterator containing Column names.

                    while (columnKeys.hasNext()) {
                        tColumn = columnKeys.next().toString();
                        cv.put(tColumn, columnJson.optString(tColumn, ""));
                    }


                    if(db.replace(table, status, cv) == -1L) {
                        throw new IllegalStateException(
                                "Insert failed for table: " + table + 
                                ", contentValues: " + cv.toString());
                    }
                }
            }

            db.setTransactionSuccessful();
        }
        catch(Throwable e) {

            return false;
        }
        finally {
            if (db != null &amp;&amp; db.isOpen()) {
                db.endTransaction();
                db.close(); 
            };
        }
    }

    return true;
}