>On your development system
This is impractical for our case

>It’s intended for offline-maintenance.
The documentation doesn't say that. In fact the only related comment is

...This pragma is usually a no-op or nearly so and is very fast. However if 
SQLite feels that performing database optimizations (such as running ANALYZE or 
creating new indexes) will improve the performance of future queries, then some 
database I/O may be done. Applications that want to limit the amount of work 
performed can set a timer that will invoke sqlite3_interrupt() if the pragma 
goes on for too long...

I don't see it taking "too long". I see it not even getting to the point of 
doing i/o, apparently because it decided not to invoke the busyhandler but 
rather immediately return failure.

        - Howard

-----Original Message-----
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Tuesday, October 3, 2017 6:40 PM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] PRAGMA optimize; == no busy handler?

On 4 Oct 2017, at 2:23am, Howard Kapustein <howard.kapust...@microsoft.com> 

> What do you think of an option to only analyze tables that have grown from 0 
> records to >0 e.g. PRAGMA optimize(0x10002) so 2=ANALYZE if beneficial and 
> 0x10000=change the criteria from "increased by 25 times or more since the 
> last" to "increased from 0 records to 1+ record since the last?

ANALYZE builds a table of statistics which tells SQLite which search strategy 
will be optimal.  The table of statistics does not have to exactly match the 
data in your tables (in fact that’s very rare).  The big changes noted by 
ANALYZE are not to do with the number of rows in each table, they are to do 
with the 'chunkiness' of each indexed column: whether a column has only two 
values (indoor / outdoor) or thousands of different values (surname).  And 
'chunkiness' doesn’t change much once a database is big enough that search-time 

It is not expected that you’ll try to run ANALYZE while a database is in use.  
It’s intended for offline-maintenance.

So why not work around the whole process ?  On your development system, create 
a database with plausible data in.  Run ANALYZE.  Then copy all the tables 
named "sqlite_stat*" into a new database.

You can then copy those tables into the databases for your production system.  
They will 'tune' SQLite to pick strategies which work best for a typical 
dataset.  You never have to run ANALYZE or "PRAGMA optimize" on that system.

Yes, it won’t give absolutely the fastest possible operations on your 
production system(s).  But they will be very close to it, and you have the 
advantage of never having to lock the database up with a maintenance procedure.

sqlite-users mailing list
sqlite-users mailing list

Reply via email to