That's a nice trick with the indexed column. I thought of something like that with a column I use for ordering. That column only has a few distinct values so I could split the query up on each of those values.
The 30 secs is an absolute worst-case scenario. Typical deep searches take less than 10 seconds which I think is acceptable especially when the user will be intentionally performing a "deep" search. But, yes, splitting the search sounds like a good approach. I'm yet to be convinced at the speed improvements from an alternative storage mechanism. Could someone please suggest such an alternative storage mechanism I could investigate further? On Sep 27, 10:14 am, Marco Nelissen <[email protected]> wrote: > Another trick you could try is to use a separate indexed column (the > rowid column for example) to limit your search to some smaller number > of rows, and then perform multiple searches over consecutive parts of > your table. That way each query takes a short amount of time, so you > can stop pretty much whenever you want, at the expense of having to > aggregate the results yourself. > > > > On Sat, Sep 26, 2009 at 6:56 PM, Marco Nelissen <[email protected]> wrote: > > The specific search you mention should still benefit from an index, > > since it's matching on prefix. If you also wanted it to match > > "unthinkable", that's a different story. In that case you might want > > to consider a storage mechanism other than a relational database. > > > On Sat, Sep 26, 2009 at 5:58 PM, mjc147 <[email protected]> wrote: > > >> Most queries take a fraction of a second. However, I want to allow the > >> user to do "deep" searches. Think of a dictionary lookup that is > >> searching the description of an entry. I want to allow the user to > >> search for something like "think" and match against something like > >> "thinking". This means using LIKE on a 100,000 row table. I don't > >> think there is any other way but am happy to be proved wrong. > > >> Is there only a single thread for the SQLite engine? For example, can > >> I start a long-running query and, before that has finished, start a > >> quick one? It appears to just queue... > > >> On Sep 27, 2:41 am, Dianne Hackborn <[email protected]> wrote: > >>> You can't, the thread is down in the SQLite engine doing its work. > > >>> I would really look at why your queries are taking so long -- 30 seconds > >>> is > >>> just insane. For example, have you created indexes on the appropriate > >>> columns for the query? > > >>> On Sat, Sep 26, 2009 at 9:16 AM, mjc147 <[email protected]> wrote: > > >>> > How to programmatically stop a long-running query? > > >>> > The long-running step is, for example, the first call to > >>> > Cursor.moveToFirst(). This can sometimes take up to 30 seconds so I > >>> > would like the user to be able to cancel. > > >>> > However, how should this be done? I've tried closing the Cursor (in > >>> > another thread) but all that happens is that the close() call takes as > >>> > long as moveToFirst() and then terminates with the exception: > > >>> > java.lang.IllegalStateException: attempt to acquire a reference on a > >>> > close SQLiteClosable > > >>> -- > >>> 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 -~----------~----~----~----~------~----~------~--~---
