Re: [sqlite] Interrupting SQLite execution (mainly FTS)
On Thu, 2 May 2013 16:58:01 +0200 "Jan Slodicka" wrote: > Hi Dan > > > What are your settings for pragmas "cache_size", "journal_mode" and > > "synchronous"? > > cache_size/synchronous - default values > > Don't remember, which journal_mode was used for testing. Should be > WAL, but I might have been lazy to write needed code. The source code > was meanwhile modified, but I can write it again if necessary. Maybe the delay at the end of the operation was an automatic checkpoint. I think that would explain why sqlite3_interrupt() and the others did not help. Maybe you can run the checkpoint in a background thread after rebuilding the FTS index or something. If you don't mind locking the database, you could also try the rebuild in rollback mode. It might be a little slower overall, but it might also be more responsive as far as sqlite3_interrupt() goes. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Interrupting SQLite execution (mainly FTS)
Hi Dan > What are your settings for pragmas "cache_size", "journal_mode" and > "synchronous"? cache_size/synchronous - default values Don't remember, which journal_mode was used for testing. Should be WAL, but I might have been lazy to write needed code. The source code was meanwhile modified, but I can write it again if necessary. Jan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Interrupting SQLite execution (mainly FTS)
On 2 May 2013, at 2:57pm, Jan Slodicka wrote: > LIKE is used at the moment, but it has its perf limits as well. To prove it, > here is a desktop benchmark: > > Desktop: W7, x64, Intel i5, 2.4 GHz > 116 MB email table containing 1 html-formatted emails. > SELECT ... LIKE...'%xxx%'... command took 35.5 sec. (Producing 9 results) > SELECT ... MATCH ... 'xxx*' command took 0.00 sec, i.e. somewhere between > 0-7 msec. (By chance producing the same results) > > The funny thing was that the creation of the email FTS index took 22-23 sec, > i.e. was faster than a single LIKE statement. Thank you for this interesting and surprising information. The resource and time cost of doing any writing on a mobile device is usually far greater than simply doing processing in memory. It appears that this is not true in your case. Hmm. > My plan is to implement FTS search as an optional feature: > - Several FTS indexes grouped into multi-indexes (Example: people names may > be in one of 3 tables: accounts, contacts, leads. These tables would > contribute to PeopleSearch activity.) > - FTS index is built on demand (when the user tries to use it) > - The build procedure must be cancellable > - Once built, the index will be maintained using triggers (for small data > changes) > - For large data changes (happens during synchronization when the server > sends a lot of data) the FTS index is dropped > > Do you see any risks with this scheme? It appears that you are aware of the issues and have thought this through. Sorry for troubling you. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Interrupting SQLite execution (mainly FTS)
On Thu, 2 May 2013 11:16:06 +0200
"Jan Slodicka" wrote:
> I was testing various ways how to interrupt rebuilding of an FTS
> index. Ability to abort is vital as the app is running on a
> smartphone and has to meet responsivity requirements.
>
> Here is what SQLite offers (AFAIK):
> - Progress handler (with low nOps value): Relatively good with only
> occasional longer periods without progress handler invocation. High
> nOps values perform badly.
> - Authorizer: Does not help much in itself, but improves a bit the
> progress handler as it is called at different occasions.
> - sqlite3_interrupt(): Tested once per sqlite3VdbeExec() call (also
> during SQL parsing, sqlite3Step()...) => insufficient.
>
> Suggestion:
> The progress handler is called when {operation_counter=i*nOps; i>0},
> whereby the counter is reset at entry to sqlite3VdbeExec() and then
> incremented for each VDBE operation. This reset causes irregular
> behavior with occasional long periods without progress handler
> invocation. So the suggestion is: Make the counter global per DB
> connection and do not reset it.
>
> The real problem is that even if I use all above tools (progress
> handler with nOps=1), there are relatively long periods when the
> execution cannot be interrupted. For example for a 30MB table (the
> user data may be larger) there is roughly 0.5 sec pause at the end
> (measured on the desktop!), maybe caused by journal maintenance.
What are your settings for pragmas "cache_size", "journal_mode" and
"synchronous"?
Dan.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Interrupting SQLite execution (mainly FTS)
Hi Simon. > I have much love for FTS but it chews up storage space, processing power, and therefore battery life, something fierce. You may end up with a working app but your users will find it reduces their battery life to an hour. Sounds unbelievable. Can you bring some example, please? In the past we worked for example on smartphone backup solutions, i.e. long running apps taking full processor power and writing a lot to SD cards (which in my opinion drains battery more), but did not observe such dramatic effect. (Or even worse battery "drainer": playing mp3 streamed over BT.) > Do you really need to /build/ an FTS database on a phone app ? Can you not (A) do what you want to do with GLOB and LIKE or (B) build the database externally before it's moved to the phone ? The app serves as a CRM client with full editing abilities, synchronization etc. Hence the data is live and cannot be pre-built. LIKE is used at the moment, but it has its perf limits as well. To prove it, here is a desktop benchmark: Desktop: W7, x64, Intel i5, 2.4 GHz 116 MB email table containing 1 html-formatted emails. SELECT ... LIKE...'%xxx%'... command took 35.5 sec. (Producing 9 results) SELECT ... MATCH ... 'xxx*' command took 0.00 sec, i.e. somewhere between 0-7 msec. (By chance producing the same results) The funny thing was that the creation of the email FTS index took 22-23 sec, i.e. was faster than a single LIKE statement. My plan is to implement FTS search as an optional feature: - Several FTS indexes grouped into multi-indexes (Example: people names may be in one of 3 tables: accounts, contacts, leads. These tables would contribute to PeopleSearch activity.) - FTS index is built on demand (when the user tries to use it) - The build procedure must be cancellable - Once built, the index will be maintained using triggers (for small data changes) - For large data changes (happens during synchronization when the server sends a lot of data) the FTS index is dropped Do you see any risks with this scheme? Jan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Interrupting SQLite execution (mainly FTS)
On 2 May 2013, at 10:16am, "Jan Slodicka" wrote: > I was testing various ways how to interrupt rebuilding of an FTS index. > Ability to abort is vital as the app is running on a smartphone and has to > meet responsivity requirements. Sorry, I don't know an answer to your question, but I do have an observation. I have much love for FTS but it chews up storage space, processing power, and therefore battery life, something fierce. You may end up with a working app but your users will find it reduces their battery life to an hour. Do you really need to /build/ an FTS database on a phone app ? Can you not (A) do what you want to do with GLOB and LIKE or (B) build the database externally before it's moved to the phone ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Interrupting SQLite execution (mainly FTS)
I was testing various ways how to interrupt rebuilding of an FTS index.
Ability to abort is vital as the app is running on a smartphone and has to
meet responsivity requirements.
Here is what SQLite offers (AFAIK):
- Progress handler (with low nOps value): Relatively good with only
occasional longer periods without progress handler invocation. High nOps
values perform badly.
- Authorizer: Does not help much in itself, but improves a bit the progress
handler as it is called at different occasions.
- sqlite3_interrupt(): Tested once per sqlite3VdbeExec() call (also during
SQL parsing, sqlite3Step()...) => insufficient.
Suggestion:
The progress handler is called when {operation_counter=i*nOps; i>0}, whereby
the counter is reset at entry to sqlite3VdbeExec() and then incremented for
each VDBE operation. This reset causes irregular behavior with occasional
long periods without progress handler invocation.
So the suggestion is: Make the counter global per DB connection and do not
reset it.
The real problem is that even if I use all above tools (progress handler
with nOps=1), there are relatively long periods when the execution cannot be
interrupted. For example for a 30MB table (the user data may be larger)
there is roughly 0.5 sec pause at the end (measured on the desktop!), maybe
caused by journal maintenance.
In my opinion I would not have this problem if the (external content) FTS
index could be placed into an attached database. Then
a) The FTS DB could be set up for highest performance / lowest safety. (No
journal, for example)
b) The long actions could be performed in a thread that could be killed if
necessary.
However, FTS design doesn't seem to enable that. (Would deserve separate
discussion. Main problem for me is that the triggers cannot refer to an
attached DB.)
Any advice?
Thanks in advance,
Jan Slodicka
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Interrupting sqlite
Would forking another process as a worker process be acceptable, then
in your main message loop wait for some IPC signal saying it is done?
Unless you are doing this on some extremely lightweight OS / monitor
that doesn't implement the concept of time-sharing, I can't see how
this would be hard to do.
On Mon, Jun 22, 2009 at 8:23 PM, João Eiras wrote:
> On , Dan wrote:
>
>>> My doubt is the following: if from the progress callback (set with
>>> sqlite3_progress_handler) I return non 0 and therefore I get
>>> SQLITE_INTERRUPT from the call to sqlite3_step, is the sqlite3_stmt
>>> object still in a valid state and will the query resume normally if I
>>> pass the same sqlite3_stmt object back to sqlite3_step again ?
>>
>> No. Next call on the statement handle should be sqlite3_reset() or
>> sqlite3_finalize().
>>
>
> I have tested it and I clearly confirm your comment :)
>
> On , Roger Binns wrote:
>
>> Why don't you call the main message loop from within the progress
>> handler callback?
>>
>
> Unfortunately, it's not that simple. The message loop loops both UI messages
> and customs messages internal to the application. For instance, I keep track
> of a sql statement in a object. The statement evolves after that object being
> affected a few times by execution messages. This way everything has their
> share of CPU and the ui works, while not relying on threading, because
> threading would require architectural and design changes in the application.
> However, this is also one of the downfalls. I can have an arbitrary amount of
> sqlite databases open, and if I run the message loop inside the progress
> handler, I can theoretically have infinite reentrancy, where one progress
> handler executes something on a different database, being in practice limited
> by stack size, which would cause a crash.
> Also, requesting the message loop to run has its performance penalty, so it
> lags the query a bit more than what's ideal.
>
> Thank you for those suggestions, but I have though of many other options,
> those included.
>
>
> So, today I was looking around the sqlite code and try to make this a
> reality, which is after interrupting a statement with a progress callback,
> leaving the sqlite3_stmt object in a state that can be resumed with another
> sqlite3_step call.
> It turned out a few lines of code fix, but however it had a side effect.
> Internally, sqlite uses sqlite3_exec function and editing the code around
> those calls to store state to allow sqlite3_exec to be suspendable as well is
> not trivial nor simple. But I'm not using sqlite3_exec on my application, so
> I just editied this function on my local tree to set the progress handler to
> null and restore it in the end.
>
> I code I needed to change to allow sqlite3_step to resume was just the
> following in file vdbe.c around line 5285, in function sqlite3VdbeExec:
>
> vdbe_error_halt:
> assert( rc );
> p->rc = rc;
> + if (rc != SQLITE_INTERRUPT){
> sqlite3VdbeHalt(p);
> -
> + }
> + else
> + {
> + p->nCallback++;
> + p->pc = pc;
> + }
> if( rc==SQLITE_IOERR_NOMEM ) db->mallocFailed = 1;
> rc = SQLITE_ERROR;
>
>
> This is probably not enough, and could even had some side effects, none of
> which I encountered, because I have my own test suite for my own cases.
>
> The question then becomes, would sqlite be able to support such thing in the
> future (suspending/resuming) ?
>
> Thank you for your attention.
> ___
> 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] Interrupting sqlite
On , Dan wrote:
>> My doubt is the following: if from the progress callback (set with
>> sqlite3_progress_handler) I return non 0 and therefore I get
>> SQLITE_INTERRUPT from the call to sqlite3_step, is the sqlite3_stmt
>> object still in a valid state and will the query resume normally if I
>> pass the same sqlite3_stmt object back to sqlite3_step again ?
>
> No. Next call on the statement handle should be sqlite3_reset() or
> sqlite3_finalize().
>
I have tested it and I clearly confirm your comment :)
On , Roger Binns wrote:
> Why don't you call the main message loop from within the progress
> handler callback?
>
Unfortunately, it's not that simple. The message loop loops both UI messages
and customs messages internal to the application. For instance, I keep track of
a sql statement in a object. The statement evolves after that object being
affected a few times by execution messages. This way everything has their share
of CPU and the ui works, while not relying on threading, because threading
would require architectural and design changes in the application.
However, this is also one of the downfalls. I can have an arbitrary amount of
sqlite databases open, and if I run the message loop inside the progress
handler, I can theoretically have infinite reentrancy, where one progress
handler executes something on a different database, being in practice limited
by stack size, which would cause a crash.
Also, requesting the message loop to run has its performance penalty, so it
lags the query a bit more than what's ideal.
Thank you for those suggestions, but I have though of many other options, those
included.
So, today I was looking around the sqlite code and try to make this a reality,
which is after interrupting a statement with a progress callback, leaving the
sqlite3_stmt object in a state that can be resumed with another sqlite3_step
call.
It turned out a few lines of code fix, but however it had a side effect.
Internally, sqlite uses sqlite3_exec function and editing the code around those
calls to store state to allow sqlite3_exec to be suspendable as well is not
trivial nor simple. But I'm not using sqlite3_exec on my application, so I just
editied this function on my local tree to set the progress handler to null and
restore it in the end.
I code I needed to change to allow sqlite3_step to resume was just the
following in file vdbe.c around line 5285, in function sqlite3VdbeExec:
vdbe_error_halt:
assert( rc );
p->rc = rc;
+ if (rc != SQLITE_INTERRUPT){
sqlite3VdbeHalt(p);
-
+ }
+ else
+ {
+ p->nCallback++;
+ p->pc = pc;
+ }
if( rc==SQLITE_IOERR_NOMEM ) db->mallocFailed = 1;
rc = SQLITE_ERROR;
This is probably not enough, and could even had some side effects, none of
which I encountered, because I have my own test suite for my own cases.
The question then becomes, would sqlite be able to support such thing in the
future (suspending/resuming) ?
Thank you for your attention.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Interrupting sqlite
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 João Eiras wrote: > My doubt is the following: if from the progress callback (set with > sqlite3_progress_handler) Why don't you call the main message loop from within the progress handler callback? Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAko/1ygACgkQmOOfHg372QTYhQCfVclgRW0huXzkHanMyoNa0lts ShQAoOKPhA+En5cve77wrZAVjl/fVOhG =42Tg -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Interrupting sqlite
On Jun 22, 2009, at 6:10 PM, João Eiras wrote: > Hello! > > I developing an single threaded app (pre-established fact, can't > change this), and to keep the UI usable, when sqlite is executing, > between multiple calls to sqlite3_step, I just break away and let the > main message loop run a bit more. If the query i executing has an > "order by" clause, then sqlite3_step will not return until it has > processed the entire result set in memory, so it can be sorted. > > My doubt is the following: if from the progress callback (set with > sqlite3_progress_handler) I return non 0 and therefore I get > SQLITE_INTERRUPT from the call to sqlite3_step, is the sqlite3_stmt > object still in a valid state and will the query resume normally if I > pass the same sqlite3_stmt object back to sqlite3_step again ? No. Next call on the statement handle should be sqlite3_reset() or sqlite3_finalize(). Dan. > This > way I could try to interrupt the statement many times so I can > continue to process the main loop to get the UI going. > > Thanks. > ___ > 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
[sqlite] Interrupting sqlite
Hello! I developing an single threaded app (pre-established fact, can't change this), and to keep the UI usable, when sqlite is executing, between multiple calls to sqlite3_step, I just break away and let the main message loop run a bit more. If the query i executing has an "order by" clause, then sqlite3_step will not return until it has processed the entire result set in memory, so it can be sorted. My doubt is the following: if from the progress callback (set with sqlite3_progress_handler) I return non 0 and therefore I get SQLITE_INTERRUPT from the call to sqlite3_step, is the sqlite3_stmt object still in a valid state and will the query resume normally if I pass the same sqlite3_stmt object back to sqlite3_step again ? This way I could try to interrupt the statement many times so I can continue to process the main loop to get the UI going. Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
