Re: [sqlite] Interrupting SQLite execution (mainly FTS)

[Dan Kennedy]

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)

[Jan Slodicka]

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)

[Simon Slavin]

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)

[Dan Kennedy]

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)

[Jan Slodicka]

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)

[Simon Slavin]

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