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