Ah, the difference between theory and practice is that in theory, there IS no difference, but in practice, there IS.
What platform did you do this on? I was afraid getCount() was going to turn out to be slow. But I'm puzzled that it's slow, but SELECT COUNT(*) FROM <TABLE> is relatively quick. Clearly, the database is capable of better behavior. It's pretty typical for reverse cursor motion to be more expensive, but this is rather larger than I'd have expected. The discontinuity at 12000 rows, in each direction, makes me suspect it's using a max window size of 12,000, which is nuts on a phone, and really wants to be configurable. I'm also wondering if CursorWindow.freeLastRow() is ever being called -- and if not, what would happen if we were to call it at suitable points. You didn't indicate whether you are using PreparedStatements -- if not, some of the overhead may come from parsing and managing the queries. Or, given a weak implementation -- it might not matter anyway. I wonder if we could supply our own CursorWindow subclass that behaves better? The documentation is rather lacking... Another factor to consider here is the database's caching. If you use LIMIT to only query a small portion, you could make a derived cursor that (excluding transaction differences) behaves more reasonably in terms of how much it fetches. But then you'll be executing changing queries over and over, so you may be doing more work on the back end. Given the memory constraints, I suspect each subsequent query will cost about the same as the first... Maybe, to be scientific about this, I should write my own tests, we compare notes, trade programs, and get a bunch of people to try them on different platforms. I might have time if I don't answer any questions for a while... :=)! This is an important use case; it's worth understanding well. I wonder if anyone has already covered this ground? On Feb 3, 1:47 pm, THill <[email protected]> wrote: > What you say makes sense Bob, but testing seems to indicate the > Android SQLite implementation isn't so proper. > > I have a simple app that creates 20001 rows in a table, each with an > int _id & 2 varchar fields. > > Getting the count of rows via db.rawQuery("select count(*) from > table", null) and getting the value from the resulting cursor takes > 0.2s. > > Getting the count of rows using > cursor=db.rawQuery("select _id, field1, field2 from table", > null); //NOTE: no 'where' or 'order by' > count=cursor.getCount() > takes 4.5s. > > During this time, the log has messages: > E/CursorWindow( 695): need to grow: mSize = 1048576, size = 29, > freeSpace() = 19, numRows = 11832 > E/CursorWindow( 695): not growing since there are already 11832 > row(s), max size 1048576 > E/Cursor ( 695): Failed allocating 29 bytes for text/blob at > 11831,1 > D/Cursor ( 695): finish_program_and_get_row_count row 8169 > > So, getCount() is certainly not optimal, and appears to be allocating > something based on result set size. The list view requests the count > of items up front, so a slow getCount() can impact the UI if you just > trust SimpleCursorAdapter on large, simple queries. > > When scrolling down the list, each bindView results in a > cursor.moveToPosition() call. Doing this in a test app shows that > moving to 1000, 2000, ..., 20000 each takes 1ms -- except when going > from 11000 to 12000, which takes >2s. No additional log messages. > > Where it gets u.g.l.y. is when moving from position 20000 backward to > 0. 20000, ..., 12000 take 1ms, but moving from 12000 to 11000, > 10000, ..., 0 *each* take 4.5s (!). This is really fun to observe > when scrolling backward in the list from 12000. Once you hit some > point, every row takes 4.5s before it is shown. > > When this starts happening, every move results in log messages like: > D/dalvikvm( 320): GC freed 251 objects / 13256 bytes in 116ms > E/CursorWindow( 763): need to grow: mSize = 1048576, size = 29, > freeSpace() = 21, numRows = 11631 > E/CursorWindow( 763): not growing since there are already 11631 > row(s), max size 1048576 > E/Cursor ( 763): Failed allocating 29 bytes for text/blob at > 19630,1 > D/Cursor ( 763): finish_program_and_get_row_count row 370 > > This would again indicate the cursor is not agnostic to the result set > size. I'll be doing more testing with many smaller query cursors > behind a single adapter, but there appears to be a clear threshold. -- You received this message because you are subscribed to the Google Groups "Android Developers" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/android-developers?hl=en
