>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
https://sqlite.org/pragma.html#pragma_optimize ...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> wrote: > 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 matters. 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. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&data=02%7C01%7Choward.kapustein%40microsoft.com%7C26b587f8ebb3420dbc5508d50ac8ed06%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636426780457125699&sdata=5De2RQH4HM%2F%2BzisjcjwXA42VzKTVymtArf%2BP0ZIOk5g%3D&reserved=0 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
