But that should effect both the UI thread query and the worker thread query. But only one runs slow.
On Wednesday, March 21, 2012 7:11:43 PM UTC+8, Mark Murphy (a Commons Guy) wrote: > > Also, bear in mind that on 2.2 and below (and some 2.3 devices), we > are dealing with the YAFFS2 filesystem, so if some other app is doing > a bunch of disk I/O, you may be blocked by their work. > > On Wed, Mar 21, 2012 at 3:22 AM, Zsolt Vasvari <[email protected]> wrote: > > Actually, just to respond to myself -- I don't believe this a SQLite > issue. > > > > For this only happens on the one query that runs in the AsyncTask. > > > > I run the exact same query twice in parallel: once in the UI thread (I > know > > I shouldn't, but it's fast) and then I run it again in the AsyncTask, > this > > time reading through the entire cursor to find a particular row index. > (Is > > there a faster way to do that in SQL, btw?). > > > > Anyway, the first query runs quick, the second one has this problem the > OP > > saw for a couple of users. Since the query is the same in both cases, I > > think this is a thread starvation issue instead of a SQLite query > problem. > > That's just my hunch based on 20 years of development experience. > > > > > > > > > > On Wednesday, March 21, 2012 2:28:46 PM UTC+8, Zsolt Vasvari wrote: > >> > >> I have 2 users (out of thousands) reporting this problem. I am at a > loss > >> as to why this is happening, also. It doesn't have anything to the > data as > >> the user claims they few rows. I even had them recreate their database > (I > >> have a backup facility that that backs up/restores from JSON) and the > >> problem still occurs. > >> > >> One guy took the phone back because he suspected a hardware issue. The > >> other person never followed up, so I don't know if he solved it or what. > >> > >> > >> > >> On Wednesday, March 21, 2012 6:54:30 AM UTC+8, Dianne Hackborn wrote: > >>> > >>> 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 > > -- > Mark Murphy (a Commons Guy) > http://commonsware.com | http://github.com/commonsguy > http://commonsware.com/blog | http://twitter.com/commonsguy > > Android App Developer Books: http://commonsware.com/books > > -- 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
