I'm rewriting a simple translation app with a SQLite db. There is an
extreme hit to performance between two queries, but only on certain devices.
One query lists the english words in a ListView, the other lists the
secondary language in a list view. The data is structured differently, and
is from a remote server.
Both are single SQL statements, run via db.rawQuery. Both use AsyncTask to
keep heavy lifting in another thread.
On both devices, the "english" query returns almost instantly (less than 1
second, every time):
return db.rawQuery("select _id as id, english as label from english_words
order by english collate nocase", null);
On one device, the "secondary_langauge" query returns almost instantly as
well. No problem there, ever. This is a Samsung Galaxy SII. On another
device (Samsung Nexus S), this query takes around 30 seconds. This query
has some joins, as follows:
return db.rawQuery("select definitions._id as id, secondary_language as
label from english_words join definition_bridge on
english_words._id=definition_bridge.word_id join definitions on
definitions._id=definition_bridge.definition_id order by
secondary_language", null);
I ran it in the emulator once, and got the same result as the Nexus S (the
30 second hang). It took a little 1.5 hours to download and parse the
returns from the server on the emulator (which takes a few seconds on
either device), so I gave up on further debug with the emulator at that
point.
This is the only difference between the two operations. The listView is
the same, the adapter is the same, the AsyncTask is the same. The number
of rows returned is different - there are about 2000 english words, and a
little over 3000 words in the other language. I don't think this explains
the vast difference in performance.
I took the query out of the AsyncTask to see if I could get some more debug
info, and did get an ANR:
at android.database.sqlite.SQLiteQuery.native_fill_window(Native Method)
at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:73)
at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:287)
at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:268)
at com.whatever.adapters.WordListAdapter.getCount(WordListAdapter.java:39)
I rewrote the adapter's getCount method to return a cached count
(determined during instantiation). After, I didn't get an ANR again, but
otherwise the performance was not improved and the query still took around
30 seconds.
I'm totally at a loss. As mentioned, everything but the queries is
identical. And on the Galaxy SII, there is no problem at all - less than a
second to populate the ListView, even under abuse (touching the button that
launches the request as fast as I could).
At this point, I'm wondering if it'd be better to abandon SQLite entirely,
and using a Java model to manage data. Assuming I can't get the SQLite
version to perform reasonably, what could I expect in terms of performance
using Collections (with the number of entries mentioned above), when I need
to do a search for example (which I imaging would require iterating over
the whole thing on each key event).
Any advice?
TYIA.
--
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
