Android Cursor in SQLite

Question

I have problem as it follows:

try {
  long time1 = System.currentTimeMillis();
  long time2, time3 = 0;
  mydb = openOrCreateDatabase(DBNAME, Context.MODE_PRIVATE, null);
  Cursor select = mydb.rawQuery("SELECT DATE, PAYEE, CATEGORY, AMOUNT, _ID, "+
   "(select count(*) from TRANSACTIONS b where a._ID <= b._ID and ACCOUNT='4') as row "+
   "from TRANSACTIONS a "+ 
   "where row >= " + nRow + " and row <= " + (nRow+Integer.valueOf(9)) +
   " order by date desc, _id desc", null);

  time2 = System.currentTimeMillis();

  if (select.moveToFirst()) {
  do {
    // ...some code...
  } while (select.moveToNext());

Log.i("time","time query : "+(time2-time1));
Log.i("time","time move cursor : "+(time3-time2));

I want to show 10 by 10 records. When user scrolls to bottom then add 10 more in list. The problem is time consumption for cursor. On the emulator time query returns 4, and time move cursor returns 264, which is excelent. But on real device (not the best one. CPU at 800MHz) time query takes 7 and cursor takes 1872 which is too slow. It is for 10 rows, but the truth is that table has over 1000 rows (and growing).

How to speed up cursor? What am I doing wrong?

Thank you!

laalto · Accepted Answer · 2014-03-17 10:47:30Z

1

Remove the paging from your query. Instead, do a simple query without your where limits and just retrieve the amount of entries from the cursor you're currently interested in. (The better SQL way for such paging would be to use LIMIT and OFFSET instead of WHERE like this.)
With the above change, you should probably do the counting in your code instead of SQL.
Add indexes to your ORDER BY columns such as date.
Measure again to see if there was a change for the better.

answered Mar 17, 2014 at 10:47

laalto

153k68 gold badges293 silver badges306 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Arnes Over a year ago

Excellent!!! Thank you very much. LIMIT helped! Will add indexes also for future size of the table.

Collectives™ on Stack Overflow

Android Cursor in SQLite

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related