android loop through database with cursor speed

Question

I have read several posts here on speed issues when looping through a cursor and tried the answers given in these posts such as e.g. do not use getcolumnindex in the loop call this once etc.

However with a database having around 2400 records it takes around 3 to 5 minutes to finish.

The loop is running in an async task method so that it does not hang up the device and the database is handled via a database adapter.

The loop code is as follows :

    while (!exportrec.isAfterLast()) {
                    if ( exportrec.moveToNext() ) {
                    fulldate =  exportnumberformatter(exportrec.getInt(daye))
                            +"/"+exportnumberformatter(exportrec.getInt(monthe))+"/"
                            +String.valueOf(exportrec.getInt(yeare));

                    fulltime =  exportnumberformatter(exportrec.getInt(houre))+":"
                            +exportnumberformatter(exportrec.getInt(mine))+":"
                            +exportnumberformatter(exportrec.getInt(sece));

                    noiseid = exportrec.getInt(typee);
                    exportedinfo += exporttypes[id] +","+exportrec.getString(notee)+","+
                            fulldate+","+fulltime+" \n" ;  
                    } 
 }

The exportnumberformatter does the following :

 public String exportnumberformatter(int i) {
            String result = Integer.toString(i);

            if (result.length() >1 ) {

             return Integer.toString(i);
             }

           String zeroprefix = "";
           zeroprefix = "0"+result;


              return zeroprefix ;
        }

The cursor is called as follows before the loop to get the data :

exportrec = MD.GetAllLogs(2, "date_sort");
exportrec.moveToFirst();

The MD is the database adapter and the GetAllLogs Method (this has been played with to try and speed things up and so the date_sort that is used is really ignored here):

public Cursor GetAllLogs(Integer i,String sortfield)
        {
            String sorted = "";
            if (i == 1 ) {
                sorted = "DESC";
            } else if (i == 2) {
                sorted = "ASC";
            } 


            return mDB.query(DB_TABLE, new String[] {COL_ID, COL_TYPE,COL_IMAGE, COL_INFO,COL_IMAGE,COL_HOUR,COL_SEC,COL_MIN,COL_DAY,COL_MON,COL_YEAR,COL_SORT_DATE},
                            null, null, null, null, COL_ID+" "+sorted);
        }

When I created the table in the database it had no indexes so I created these via the upgrade method. However they did not error or appear to fail when I did this but what I do not know is A) does the database/table need rebuilding after an index is created and B) how to tell if they have been created ? the two indexes were based on the ID as the first and a field that holds the year month day hour minute second all in on Long Integer.

I am concerned that the loop appears to be taking this long to read through that many records.

Update:

rtsai2000's and the suggestion from CL answer has improved the speed from minutes to seconds

Answer 1

Your exportedInfo String is growing and growing. Save the results in an array and Stringify later (such as with StringBuilder).

You are not closing your cursor after reading the records.

List<String> exportedInfo = new ArrayList<String>();
Cursor exportrec = GetAllLogs();
try {
  while (exportrec.moveToNext()) {
    String info = String.format("%s, %s, %02d/%02d/%02d, %02d:%02d:%02d",
      exporttypes[id],
      exportrec.getString(notee),
      exportrec.getInt(daye),
      exportrec.getInt(monthe),
      exportrec.getInt(yeare),
      exportrec.getInt(houre),
      exportrec.getInt(mine),
      exportrec.getInt(sece));  
    exportedInfo.add(info);
  }
} finally {
  exportrec.close();
}
return exportedInfo;