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
