patternjavaMinor

Optimizing mass inserts into SQLite

Submitted by: @import:stackexchange-codereview·Mar 10, 2026·

Viewed 0 times

massintoinsertssqliteoptimizing

Problem

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 tables = new ArrayList();

Iterator 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 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(

Solution

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() > 0) {
        Iterator 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 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 < 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 && db.isOpen()) {
                db.endTransaction();
                db.close(); 
            };
        }
    }

    return true;
}

Code Snippets

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

    if(jsonObj.length() > 0) {
        Iterator<?> 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<?> 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 < 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 && db.isOpen()) {
                db.endTransaction();
                db.close(); 
            };
        }
    }

    return true;
}

Context

StackExchange Code Review Q#12085, answer score: 4

Revisions (0)

No revisions yet.