>For the OPTIMIZE the documentation states: "... run just before >closing each database connection ...". There isn't a reason to do it >instead after opening a connection each time?
The documentation tells you under what circumstances PRAGMA OPTIMIZE does anything, in the current implementation. It will ONLY do anything if ALL THE FOLLOWING CONDITIONS ARE TRUE: 1) MASK bit 0x02 is set. 2) The query planner used sqlite_stat1-style statistics for one or more indexes of the table at some point during the lifetime of the current connection. 3) One or more indexes of the table are currently unanalyzed or the number of rows in the table has increased by 25 times or more since the last time ANALYZE was run. Since bullet 2 is ALWAYS FALSE when opening a connection, PRAGMA OPTIMIZE will never do anything when run immediately after opening a new connection (in the current implementation). You have to actually DO SOMETHING for PRAGMA OPTIMIZE to have any effect. That is why you run it AFTER you have done something with the connection, right before you close it ... (or, if you keep the connection open for a long time -- long being defined as seconds / minutes / hours / weeks / months / years / decades / centuries / millenia / aeons -- you may want to run it "periodically" ... "periodically" being defined as every 1 or more seconds / minutes / hours / days / weeks / months / years / decades / centuries / millenia / aeons). https://sqlite.org/pragma.html#pragma_optimize --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
