You can get the SQLite version to perform acceptably. You just need to use it correctly -- set up indices as appropriate as needed for joins and such. I am not a SQL (lite or otherwise) expect in any way so I can't help you with the particulars, but at the very least make sure you are actually setting indices on the columns that are involved in deciding what rows are included in the query result.
Also all you are doing by putting your query in the main thread of your process is causing your process to ANR when it takes a long time. The query all happens in native code down in SQLite, so you won't see anything in your java traces (nor typically anything interesting in native traces either since most likely, yes, you are executing the query in SQLite). On Tue, Mar 20, 2012 at 3:22 PM, momo <[email protected]> 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 -- Dianne Hackborn Android framework engineer [email protected] Note: please don't send private questions to me, as I don't have time to provide private support, and so won't reply to such e-mails. All such questions should be posted on public forums, where I and others can see and answer them. -- 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
