Re: [sqlite] Interrupt first sqlite3_step in FTS3 query

[Hamish Allan]

On Sat, Feb 20, 2010 at 10:09 PM, Simon  wrote:

> I also can stop the process between any two calls to sqlite3_step, my issue 
> is with the first one that (to me) does not seem interruptible.
>
> I just tried adding a call to CHECK_FOR_INTERRUPT just after "for(pc=p->pc; 
> rc==SQLITE_OK; pc++){" (~line 52715 in the ammalgamation) in sqlite3VdbeExec.
>
> It now behaves to my satisfaction (I can interrupt my query in no time)
>
> So I guess my question now becomes: is it safe?

Can anyone answer this? I've just implemented something similar to
Simon's approach (but constrained to two threads), and trying to
interrupt FTS3 queries can bring my entire development machine to its
knees. So I suspect the code is not safe even as it stands!

Thanks,
Hamish
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Interrupt first sqlite3_step in FTS3 query

[Hamish Allan]

On Fri, Feb 19, 2010 at 12:18 AM, Simon  wrote:

> However, it seems that some process (that can take several tens of seconds) 
> in the first sqlite3_step does not test for interrupt (resulting in 
> simultaneous uninterrupted concurrent threads...)

Do you have a separate database connection for each thread?

Are the long-lived threads busy or blocking? (Press pause in XCode and
examine the stack traces of each thread.)

Best wishes,
Hamish
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Interrupt first sqlite3_step in FTS3 query

[Simon]

> Scott meant that most of the job fts is doing is actually calls to sqlite
> core that checks this state very often. I just did a windows desktop test
> with the fts3, my second thread is ordered to call sqlite_interrupt after 1
> second sleep and different tests confirms his explanation (including phrase
> search and mask search), everything works fine.

I'm not sure I should keep bombing the list with this (just tell me when you 
can't stand me anymore ;-) ) but as I said initially, I'm developing on a first 
gen iPod Touch and the same query that takes tens of seconds on this device is 
quite fast on the simulator that Apple provides.

I also can stop the process between any two calls to sqlite3_step, my issue is 
with the first one that (to me) does not seem interruptible.

I just tried adding a call to CHECK_FOR_INTERRUPT just after "for(pc=p->pc; 
rc==SQLITE_OK; pc++){" (~line 52715 in the ammalgamation) in sqlite3VdbeExec.

It now behaves to my satisfaction (I can interrupt my query in no time)

So I guess my question now becomes: is it safe?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Interrupt first sqlite3_step in FTS3 query

[Max Vlasov]

> The fts implementation does work in response to data gotten from

>  > SQLite calls, and feeds data back out via SQLite calls, which should
> > all start throwing errors and causing things to unwind.  Most
> > expensive fts operations involve lots of subsidiary queries into the
> > SQLite core
>
> But do any of these subsidiary queries involve a call to
> CHECK_FOR_INTERRUPT?
>

Simon,
Scott meant that most of the job fts is doing is actually calls to sqlite
core that checks this state very often. I just did a windows desktop test
with the fts3, my second thread is ordered to call sqlite_interrupt after 1
second sleep and different tests confirms his explanation (including phrase
search and mask search), everything works fine.

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Interrupt first sqlite3_step in FTS3 query

[Simon]

> The fts implementation does work in response to data gotten from
> SQLite calls, and feeds data back out via SQLite calls, which should
> all start throwing errors and causing things to unwind.  Most
> expensive fts operations involve lots of subsidiary queries into the
> SQLite core

But do any of these subsidiary queries involve a call to CHECK_FOR_INTERRUPT?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Interrupt first sqlite3_step in FTS3 query

[Scott Hess]

On Sat, Feb 20, 2010 at 4:28 AM, Max Vlasov  wrote:
> you mentioned full-text search.
> I just tried to search for mentioning of sqlite3_interrupt in the sqlite
> sources
> The main is the implemention of the function itself that just sets the
> isInterrupted variable:
>
> void sqlite3_interrupt(sqlite3 *db){
>  db->u1.isInterrupted = 1;
> }
>
> but all of of fts_* sources lacks using this variable at all. I heard that
> fts is implemented a little separately to the main core code. So I suppose
> any job involving working with internal fts structures (packed data inside
> blobs) currently can ignore checking this variable. Can someone more
> familiar with the fts correct or confirm this?

The fts implementation does work in response to data gotten from
SQLite calls, and feeds data back out via SQLite calls, which should
all start throwing errors and causing things to unwind.  Most
expensive fts operations involve lots of subsidiary queries into the
SQLite core, so I wouldn't expect it to take as long as is being
described for things to come back.

-scott
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Interrupt first sqlite3_step in FTS3 query

[Max Vlasov]

Simon,

you mentioned full-text search.
I just tried to search for mentioning of sqlite3_interrupt in the sqlite
sources
The main is the implemention of the function itself that just sets the
isInterrupted variable:

void sqlite3_interrupt(sqlite3 *db){
  db->u1.isInterrupted = 1;
}

but all of of fts_* sources lacks using this variable at all. I heard that
fts is implemented a little separately to the main core code. So I suppose
any job involving working with internal fts structures (packed data inside
blobs) currently can ignore checking this variable. Can someone more
familiar with the fts correct or confirm this?

Max


On Fri, Feb 19, 2010 at 3:18 AM, Simon  wrote:

> I am using sqlite3 with an FTS3 index for a software running on an iPod
> Touch.
>
> I have a text field that launches a full text search query at every key
> press.
>
> There is a huge speed difference between these devices and what I got
> accustomed to on desktop computers...
>
> To mitigate that, I use an asynchronous query and send a sqlite3_interrupt
> before each new query when a new key is pressed.
>
> However, it seems that some process (that can take several tens of seconds)
> in the first sqlite3_step does not test for interrupt (resulting in
> simultaneous uninterrupted concurrent threads...)
>
> Is there some way to change that?
>
> Thanks in advance for your help,
>
> Best regards,
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Interrupt first sqlite3_step in FTS3 query

[Simon]

> I am very surprise that your SQLite operation can take so
> long. Is it a very complicated search? Multiple writes? 

it is a 'SELECT docid FROM fulltext WHERE Content MATCH' on a table with 
~13 rows. The database (with only the fulltext tables is ~110MB)

>> I have a text field that launches a full text search query at every key 
>> press.
> 
> Can you rearrange your algorithm? How many records do you
> search?

Since I search as soon as two characters are input, some queries can 
potentially return nearly all the rows (that's why I'd like to be able to 
interrupt them as soon as new keystrokes come in) 

>  Do you use proper index keys?

I don't think that's applicable for this kind of query on a FTS3 table.

> Do you have to search on every key press?

I guess I will just have to fall back to decreasing the interactivity with the 
user and only send the query when the search button is pressed...

Thanks for your help

Best regards,

Simon.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Interrupt first sqlite3_step in FTS3 query

[a1rex]

Simon,
 
I am very surprise that your SQLite operation can take so
long. Is it a very complicated search? Multiple writes? 
 
>I have a text field that launches a full text search query at every key press.
 
Can you rearrange your algorithm? How many records do you
search? Do you have to search on every key press?  Do you use proper index keys?
 
If this takes so long no wonder that you are trying
desperate measures like aborting via qlite3_interrupt. But qlite3_interrupt may 
not be your savior. 
 
I had similar problem when I was trying to write every
keyboard stroke to the hard drive. It was painfully slow! I was forced to
rethink my approach, buffer characters and save them on the slow timer tick.
 
>Do you think there would be any proper places to add
CHECK_FOR_INTERRUPT >calls to the FTS3 code to improve the issue?
 
Sorry! I do not know. My knowledge of FTS3 code is very
limited.
 
Regards,
Samuel


  __
Looking for the perfect gift? Give the gift of Flickr! 

http://www.flickr.com/gift/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Interrupt first sqlite3_step in FTS3 query

[Simon]

> According to http://www.sqlite.org/c3ref/interrupt.html
> process but will not be aborted if it is quite advanced: 
> 
> “If an SQL operation is very nearly finished at the time
> when sqlite3_interrupt() is called, then it might not have an opportunity to 
> be
> interrupted and might continue to completion.”

I had read that but it seems we disagree on the definition of "nearly finished" 
;-) : it can take more than 20s (on an admittedly  slow device) between the 
call to interrupt and completion.

> Also:
> ”A call to sqlite3_interrupt(D) that occurs when there are no running SQL
> statements is a no-op and has no effect on SQL statements that are started
> after the sqlite3_interrupt() call returns.”

I have tried calling sqlite3_interrupt in a while loop for as long as the 
thread is running and that's not returning earlier so it is not a no-op thing

> The safest solution is to have only ONE threat to deal with
> the database.

Trust me I'd like that ;-) unfortunately that's not an option given the 
response times...

Do you think there would be any proper places to add CHECK_FOR_INTERRUPT calls 
to the FTS3 code to improve the issue?

Best regards,

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Interrupt first sqlite3_step in FTS3 query

[a1rex]

 
- Original Message 
From: Simon dbern...@noos.fr
 
>However, it seems that some process (that can take
>several tens of seconds) in the first >sqlite3_step does not test for
>interrupt (resulting in simultaneous uninterrupted 
>concurrent threads...)
 
According to http://www.sqlite.org/c3ref/interrupt.html
process but will not be aborted if it is quite advanced: 

“If an SQL operation is very nearly finished at the time
when sqlite3_interrupt() is called, then it might not have an opportunity to be
interrupted and might continue to completion.”
 
Also:
”A call to sqlite3_interrupt(D) that occurs when there are no running SQL
statements is a no-op and has no effect on SQL statements that are started
after the sqlite3_interrupt() call returns.”
 
The safest solution is to have only ONE threat to deal with
the database.
 
Let me quote famous words: “Threads are evil. Avoid them.” 
http://www.sqlite.org/faq.html#q6
 
Regards,
Samuel


  __
Looking for the perfect gift? Give the gift of Flickr! 

http://www.flickr.com/gift/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users