2017-04-04, 15:05
FYI; SQLite version 3.18 was just released with a PRAGMA optimize command to improve long-term query performance
https://www.sqlite.org/pragma.html#pragma_optimize
"Attempt to optimize the database. All schemas are optimized in the first two forms, and only the specified schema is optimized in the latter two.
To achieve the best long-term query performance without the need to do a detailed engineering analysis of the application schema and SQL, it is recommended that applications run "PRAGMA optimize" (with no arguments) just before closing each database connection. Long-running applications might also benefit from setting a timer to run "PRAGMA optimize" every few hours.
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.
The details of optimizations performed by this pragma are expected to change and improve over time. Applications should anticipate that this pragma will perform new optimizations in future releases."
https://www.sqlite.org/releaselog/3_18_0.html
Note that the SQLite version identifier returned by the sqlite_source_id() SQL function and the sqlite3_sourceid() C API and found in the SQLITE_SOURCE_ID macro has changed in SQLite version 3.18 to a new 64-digit SHA3-256 hash instead of a 40-digit SHA1 hash.
https://www.sqlite.org/pragma.html#pragma_optimize
"Attempt to optimize the database. All schemas are optimized in the first two forms, and only the specified schema is optimized in the latter two.
To achieve the best long-term query performance without the need to do a detailed engineering analysis of the application schema and SQL, it is recommended that applications run "PRAGMA optimize" (with no arguments) just before closing each database connection. Long-running applications might also benefit from setting a timer to run "PRAGMA optimize" every few hours.
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.
The details of optimizations performed by this pragma are expected to change and improve over time. Applications should anticipate that this pragma will perform new optimizations in future releases."
https://www.sqlite.org/releaselog/3_18_0.html
Note that the SQLite version identifier returned by the sqlite_source_id() SQL function and the sqlite3_sourceid() C API and found in the SQLITE_SOURCE_ID macro has changed in SQLite version 3.18 to a new 64-digit SHA3-256 hash instead of a 40-digit SHA1 hash.
