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 <zvasvari@gmail.com> 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 <dunn74@gmail.com> 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 android-developers@googlegroups.com
>>>> To unsubscribe from this group, send email to
>>>> android-developers+unsubscribe@googlegroups.com
>>>> For more options, visit this group at
>>>> http://groups.google.com/group/android-developers?hl=en
>>>
>>>
>>>
>>>
>>> --
>>> Dianne Hackborn
>>> Android framework engineer
>>> hackbod@android.com
>>>
>>> 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 android-developers@googlegroups.com
> To unsubscribe from this group, send email to
> android-developers+unsubscribe@googlegroups.com
> 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/commonsguyAndroid 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 android-developers@googlegroups.com
To unsubscribe from this group, send email to
android-developers+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/android-developers?hl=en
No comments:
Post a Comment