SQL performance often relies on indexing and quantity of data. Generally
fields you join on are going to need to be indexed (as they appear to be
pk's). Without a where criteria, you're effectively going to end up with a
table scan on english words, and compounded by the joins.
I dont know details about the devices specifically, but it is possible
sqlite isnt optimized for the nexus.
If you arent indexing your tables, do so.
Weston
On Tuesday, March 20, 2012 3:22:57 PM UTC-7, momo wrote:
>
> 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
