Efficient way to handle big nested for loops to insert in db in android

Question

I have a huge data which i need to insert in sqlite database.

public synchronized void insert(ResponseModel[] response) {

    for (int i = 0; i < response.length; i++) { // Here response length is 100
        for (int j = 0; j < response[i].getPings().size(); j++) { // Here per response, ping size is around 300
            ContentValues cv = new ContentValues();
            if (response[i].getPings().get(j).getId() != null)
                cv.put(Constants.ID, response[i].getPings().get(j).getId().toString().trim());
            // More insertion code goes here
        }
    }
}

Now in this situation, the application takes too much time. The app doesn't hangs because it happens in a background thread. Is there any way to efficiently handle this huge looping?

At least you can move 'ContentValues cv = new ContentValues();' outside the loops and reuse it. — Ridcully, Oct 09 '14 at 09:32
Use a transaction. http://stackoverflow.com/questions/8147440/android-database-transaction — Pedro Oliveira, Oct 09 '14 at 09:33
http://developer.android.com/training/articles/perf-tips.html — Nirmal, Oct 09 '14 at 10:12
@RahulGupta, may I ask you to mark the answer as correct if it was useful for you and answers your question :) — Kiril Aleksandrov, Oct 30 '14 at 07:37
@RahulGupta were you able to overcome this challenge? you have any hint — display name, Nov 05 '16 at 04:37

score 0 · Accepted Answer · answered Oct 09 '14 at 09:51

Unfortunately Android SQLite API does not support bulk insertion. Although you can optimize the execution time by using a single transaction. By default Android uses a separate transaction for each insert() call. So in your scenario you will have 30000 transactions. Beginning and committing transactions is a time consuming operation. So you can use a single transaction for all the insertions:

try{
  db.beginTransaction();

  for (int i = 0; i < response.length; i++) { // Here response length is 100
    for (int j = 0; j < response[i].getPings().size(); j++) { // Here per response, ping size is around 300
      ContentValues cv = new ContentValues();
      if (response[i].getPings().get(j).getId() != null) {
        cv.put(Constants.ID, response[i].getPings().get(j).getId().toString().trim());
      }
      // More insertion code goes here
    }
  }

  db.setTransactionSuccessful();
} catch (SQLException e) {
  // Handle the exception...
} finally {
  db.endTranscation();
}

Efficient way to handle big nested for loops to insert in db in android

1 Answers1