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 <dun...@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+unsubscr...@googlegroups.com
>>> For more options, visit this group at
>>> http://groups.google.com/group/android-developers?hl=en
>>
>>
>>
>>
>> --
>> Dianne Hackborn
>> Android framework engineer
>> hack...@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+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/android-developers?hl=en
