title: Android - Populating sqlite table takes long
body: <p>I had this problem a couple of months before and now it is time to get back to it.</p>
  
  <p>I query the phone's calllog into a database, but it takes around 30 seconds to populate the table. It looks like querying takes around 1 sec, but the population takes forever, although the phone stores only the last 500 calls. Why is it so slow? Am I doing something wrong?</p>
  
  <p>I test it only on my phone, since I have only 8 items in the emulator's calllog.</p>
  
  <pre><code>  final String[] projection = null;
  
              HotOrNot infoA = new HotOrNot(Charts.this);
              infoA.open();
              infoA.createtable_Calls();
              infoA.deleteAllEntries_Calls();
              infoA.close();
  
              final Context context = getApplicationContext();
              final String selection = null;
              final String sortOrder = android.provider.CallLog.Calls.DATE + " DESC";
  
              Cursor c = context.getContentResolver().query(android.provider.CallLog.Calls.CONTENT_URI, projection, selection, null, sortOrder);
              while (c.moveToNext()) { 
                  String callLogID = c.getString(c.getColumnIndex(android.provider.CallLog.Calls._ID));
  
                  int numberColumn = c.getColumnIndex(android.provider.CallLog.Calls.NUMBER);
                  int dateColumn = c.getColumnIndex(android.provider.CallLog.Calls.DATE);
                  int typeColumn = c.getColumnIndex(android.provider.CallLog.Calls.TYPE);
                  int durationColumn = c.getColumnIndex(android.provider.CallLog.Calls.DURATION);
                  int person = c.getColumnIndex(android.provider.CallLog.Calls.CACHED_NAME);
  
  
  
                  String number = c.getString(numberColumn);
                  int duration = c.getInt(durationColumn);
                  String personname = c.getString(person);
                  long callDate = c.getLong(dateColumn);
                  int callType = c.getInt(typeColumn);
  
                  if (duration &gt;= 0)
                  {
                      switch (callType) {
                      case 1:
                          duration_in = duration;
                          duration_out = 0;
                          break;
                      case 2:
                          duration_out = duration;
                          duration_in = 0;
                          break;
                      case 3:
                          duration_in = 0;
                          duration_out = 0;
                          break;
  
  
                      }
                      }
  
      //Here comes the slow part
  
                      HotOrNot info = new HotOrNot(Charts.this);
                      info.open();
                      info.pop   
  ulate_Calls(personname, number, String.valueOf(callType), Integer.toString(duration), Long.toString(callDate), callLogID);
                  info.close();   
               }
  </code></pre>
  
  <p>This is the populating function:</p>
  
  <pre><code>public long populate_Calls(String name, String phone, String type, String duration, String date, String contactid) {
          ContentValues cv = new ContentValues();
          cv.put(KEY_NAME, name);
          cv.put(KEY_PHONE, phone);
          cv.put(KEY_TYPE, type);
          cv.put(KEY_DURATION, duration);
          cv.put(KEY_DATE, date);
          cv.put(KEY_CONTACTID, contactid);
          return ourDatabase.insert(DATABASE_TABLE, null, cv);        
      }
  </code></pre>
  
  <p><strong>EDIT</strong>:</p>
  
  <p>To Andreas Ka's and twaddington's answers I modified the population method in the SQLiteOpenHelper class, but unfortunately it did not make a difference:</p>
  
  <pre><code>public long populate_Calls(String name, String phone, String type, String duration, String date, String contactid) {
      ContentValues cv = new ContentValues();
       try {
           ourDatabase.beginTransaction();
  
           cv.put(KEY_NAME, name);
           cv.put(KEY_PHONE, phone);
           cv.put(KEY_TYPE, type);
           cv.put(KEY_DURATION, duration);
           cv.put(KEY_DATE, date);
           cv.put(KEY_CONTACTID, contactid);
  
           ourDatabase.yieldIfContendedSafely();
  
           ourDatabase.setTransactionSuccessful();
       } finally {
           ourDatabase.endTransaction();
       }
  
      return ourDatabase.insert(DATABASE_TABLE, null, cv);        
  }
  </code></pre>
  
  <p><strong>EDIT2:</strong>
  Posting the whole code based on Babibu and twaddington's answers. By the way the temp_ arrays are now LinkedLists, but that does not make a difference in time.</p>
  
  <pre><code> final String[] projection = null;
          final Context context = getApplicationContext();
          final String selection = null;
          final String sortOrder = android.provider.CallLog.Calls.DATE + " DESC";
          lv1 = (ListView) findViewById(R.id.ListView02); 
  
  
          HotOrNot infoA = new HotOrNot(Calllogs.this);
          infoA.open();
          infoA.createtable_Calls();
          infoA.deleteAllEntries_Calls();
          infoA.close();
  
            pd = ProgressDialog.show(Calllogs.this, "Please wait..", "Loading data, it may take a few" +
                  " seconds based on the number of data.", false, true);
  
          Cursor c = context.getContentResolver().query(android.provider.CallLog.Calls.CONTENT_URI, projection, selection, null, sortOrder);
          while (c.moveToNext()) { 
              String callLogID = c.getString(c.getColumnIndex(android.provider.CallLog.Calls._ID));
  
              int numberColumn = c.getColumnIndex(android.provider.CallLog.Calls.NUMBER);
              int dateColumn = c.getColumnIndex(android.provider.CallLog.Calls.DATE);
              int typeColumn = c.getColumnIndex(android.provider.CallLog.Calls.TYPE);
              int durationColumn = c.getColumnIndex(android.provider.CallLog.Calls.DURATION);
              int person = c.getColumnIndex(android.provider.CallLog.Calls.CACHED_NAME);
  
  
  
              String number = c.getString(numberColumn);
              int duration = c.getInt(durationColumn);
              String personname = c.getString(person);
              long callDate = c.getLong(dateColumn);
              int callType = c.getInt(typeColumn);
  
              if (duration &gt;= 0)
              {
                  switch (callType) {
                  case 1:
                      duration_in = duration;
                      duration_out = 0;
                      break;
                  case 2:
                      duration_out = duration;
                      duration_in = 0;
                      break;
                  case 3:
                      duration_in = 0;
                      duration_out = 0;
                      break;
                  }
              }
  
              temp_name.add(personname);
              temp_num.add(number);
              temp_type.add(String.valueOf(callType));
              temp_dur.add(Integer.toString(duration));
              temp_date.add(String.valueOf(callDate));
              temp_id.add(callLogID);
            } //end of while loop
  
  
          HotOrNot infotemp = new HotOrNot(Calllogs.this);
          infotemp.open();
  
  
              for (int i=0; i&lt;temp_name.size(); i++)
              {
                  infotemp.populate_Calls(temp_name.get(i), temp_num.get(i), temp_type.get(i), temp_dur.get(i), temp_date.get(i), temp_type.get(i));
              }
   infotemp.close();
  </code></pre>
  
  <p><strong>SOLUTION</strong></p>
  
  <p>I am posting twaddington's solution, which reduced the time from 8 seconds to less than 2:</p>
  
  <pre><code> HotOrNot infotemp = new HotOrNot(Calllogs.this);
          infotemp.open();
  
          // Get our database. You can do this however you wish, but
          // it seems like since the database is contained in your `HotOrNot`
          // object, it would be best to simply add a getter method to
          // the class.
          SQLiteDatabase db = infotemp.getDatabase();
  
          try {
              // Begin our transaction
              db.beginTransaction();
  
              // Loop over the array of calls and
              // perform a db insert for each.
              for (int i=0; i&lt;temp_name.size(); i++) {
                  // Yield the database lock if requested. This will
                  // temporarily suspend our loop, but it should
                  // continue when the lock is opened.
                  db.yieldIfContendedSafely();
  
                  infotemp.populate_Calls(temp_name.get(i), temp_num.get(i),
                          temp_type.get(i), temp_dur.get(i), temp_date.get(i), temp_type.get(i));
              }
  
              // Mark our transaction as successful!
              db.setTransactionSuccessful();
          } finally {
              // Always end the transaction!
              db.endTransaction();
          }
  
          infotemp.close();
  </code></pre>