Optimizing OrmLite many-to-many code

Question

I am parsing a JSON file to generate rows and relationships in a database with OrmLite. Using the following code, it takes about 20+ minutes to parse all of the content. Is there any way I can optimize this to take less time?

I have 3 tables making up a many-to-many relationship.

public class FirstTable {
    int id;
    ForeignCollection<IntermediateTable> intermediateTables;
}

public class IntermediateTable {
    int id;
    FirstTable firstTable;
    SecondTable secondTable;
}

public class SecondTable {
    int id;
    ForeignCollection<IntermediateTable> intermediateTables;
}

After creating and populating the first and second tables, I am parsing a JSON file to create the relations between the FirstTable and SecondTable. The JSON file stores a collection of FirstTable objects and the IDs of the relating SecondTable entries.

My code looks something like this:

setForeignRelations(JSONObject jsonObject, FirstTable firstTable) {

    JSONArray secondTables = jsonObject.getJSONArray(SECOND_TABLE_KEY);

    for (int i = 0; i < secondTables.length(); i++) {
        int secondTableId = ((Integer)secondTables.get(i)).intValue();

        SecondTable secondTable = DbManager.getInstance().getHelper().getSecondTableDao().queryForId(secondTableId);

        IntermediateTable intermediateTable = new IntermediateTable();

        intermediateTable.setFirstTable(firstTable);
        intermediateTable.setSecondTable(secondTable);

        DbManager.getInstance().getHelper().getIntermediateTableDao().create(intermediateTable);
    }
}

score 0 · Accepted Answer · edited May 23 '17 at 12:12

I never try it but this could be faster and should work :

setForeignRelations(JSONObject jsonObject, FirstTable firstTable) {

JSONArray secondTables = jsonObject.getJSONArray(SECOND_TABLE_KEY);

for (int i = 0; i < secondTables.length(); i++) {
    int secondTableId = ((Integer)secondTables.get(i)).intValue();

    IntermediateTable intermediateTable = new IntermediateTable();

    intermediateTable.setFirstTable(firstTable);
    intermediateTable.setSecondTable(new SecondTable(secondTableId));

    DbManager.getInstance().getHelper().getIntermediateTableDao().create(intermediateTable);
}

}

This should work because it will only save the id of the secondTable in the intermediateTable. So you don't have to request the database to fill all the field of your second table.

If you want to make it faster again you can have a look here to accelerate your object insertion : ORMLite's createOrUpdate seems slow - what is normal speed?

So you will use the callBatchTasks method which permit to increase speed in ORMLite.

With this 2 things you increase the speed of the SELECT (because you don't do it anymore) and of the INSERT with the callBatchTasks method.

If you want an example of the callBatchTasks method. you can have a look here : Android ORMLite slow create object

Optimizing OrmLite many-to-many code

1 Answers1