How can i reduce time in fetching of data from SQLite cursor?

Question

I am using following code to get data from SQlite, I got perfect data very quickly in cursor.
When I am iterating the cursor, it takes >10 seconds to fetch 31 records from cursor.
My Query takes time 0.016150 second(s) to execute.

How can i reduce this time to <1 second ?

Cursor cursor = dbHelper.getFollowedValuesForCalendar();

    if(cursor!=null && cursor.getCount()>0)
    {
        cursor.moveToFirst();

        int followedDateIndex   = cursor.getColumnIndex(Info.FOLLOWED_DATE);
        int followedCountIndex  = cursor.getColumnIndex(Info.FOLLOWED_COUNT);
        int successValueIndex   = cursor.getColumnIndex(Info.SUCCESS_VALUE);
        int isEnableIndex       = cursor.getColumnIndex(Info.IS_ENABLE);
        do
        {       
            Info.previousFollowedDaysList.add(cursor.getString(followedDateIndex)); 
            Info.previousFollowedValuesList.add(cursor.getInt(followedCountIndex));
            Info.successValuesList.add(cursor.getInt(successValueIndex));
            Info.isEnableList.add(cursor.getInt(isEnableIndex));
        }while(cursor.moveToNext());
    }
    else
    {
        //System.out.println(" Records between dates ==============>>>> 0");
    }

    if(cursor!=null)
        cursor.close();

Hi Paresh thanks for reply...My Query takes time 0.016150 second(s) to execute. — Sunil Parmar, Oct 07 '13 at 10:10
@ParmarS : not exactly. The query method returns quickly because it only computes a part of the query, while the rest is done during the cursor operations. please post your query and your database create query. — njzk2, Oct 07 '13 at 10:15

score 1 · Answer 1 · edited May 23 '17 at 11:59

You should not call getCount() on your cursor unnecessarily, because its an expensive call. Read this.

Instead, I would suggest you to alter your code as below:

Cursor cursor = dbHelper.getFollowedValuesForCalendar();

while(cursor != null && cursor.moveToNext()) {
    int followedDateIndex   = cursor.getColumnIndex(Info.FOLLOWED_DATE);
    int followedCountIndex  = cursor.getColumnIndex(Info.FOLLOWED_COUNT);
    int successValueIndex   = cursor.getColumnIndex(Info.SUCCESS_VALUE);
    int isEnableIndex       = cursor.getColumnIndex(Info.IS_ENABLE);

    Info.previousFollowedDaysList.add(cursor.getString(followedDateIndex)); 
    Info.previousFollowedValuesList.add(cursor.getInt(followedCountIndex));
    Info.successValuesList.add(cursor.getInt(successValueIndex));
    Info.isEnableList.add(cursor.getInt(isEnableIndex));
}

if(cursor!=null)
    cursor.close();

Moreover, if you already know the indexes' of your columns, then you may simply take the cursor.getColumnIndex out from while for further optimization.

score 0 · Answer 2 · answered Jun 24 '17 at 01:49

Take the retrieving column indices outside of the while loop:

Cursor cursor = dbHelper.getFollowedValuesForCalendar();

if (cursor !=null) {

    if (!cursor.moveToFirst()) { cursor.close(); return; }

    int followedDateIndex   = cursor.getColumnIndex(Info.FOLLOWED_DATE);
    int followedCountIndex  = cursor.getColumnIndex(Info.FOLLOWED_COUNT);
    int successValueIndex   = cursor.getColumnIndex(Info.SUCCESS_VALUE);
    int isEnableIndex       = cursor.getColumnIndex(Info.IS_ENABLE);

    Info.previousFollowedDaysList.add(cursor.getString(followedDateIndex)); 
    Info.previousFollowedValuesList.add(cursor.getInt(followedCountIndex));
    Info.successValuesList.add(cursor.getInt(successValueIndex));
    Info.isEnableList.add(cursor.getInt(isEnableIndex));

    while(cursor.moveToNext()) {
      Info.previousFollowedDaysList.add(cursor.getString(followedDateIndex)); 
      Info.previousFollowedValuesList.add(cursor.getInt(followedCountIndex));
      Info.successValuesList.add(cursor.getInt(successValueIndex));
      Info.isEnableList.add(cursor.getInt(isEnableIndex));
    }

    cursor.close();

}

As a general rule of thumb you should always try to remove variable/object creating outside of loops. This increases performance as well as reduces the memory usage.

How can i reduce time in fetching of data from SQLite cursor?

2 Answers2