CODEWITHSUNDEEP
androidsqlite
Sunil Parmar
Sunil Parmar

Reputation: 1233

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

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();

Upvotes: 2

Views: 811

Answers (2)

Vlad
Vlad

Reputation: 860

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.

Upvotes: 0

waqaslam
waqaslam

Reputation: 68167

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.

Upvotes: 1

Related Questions