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
