if AppDB.Connect then
AppDB.SQLExecute(“PRAGMA auto_vacuum = 1”) // I hope that this works as long the database is connected
if DebugBuild then
MsgBox “Database Connected” + EndOfLine + AppDB.DatabaseFile.NativePath
MsgBox “Database NOT Connected”
Auto Vacuum (post v3.1 feature) must be set BEFORE you create any table. Tables created without it set will not be auto vacuumed even if you set this pragma afterwards. Every commit will start the free space relocation routines, so it will consume extra CPU too. An auto vacuumed DB recovers space from DBs with large use of DELETEs but it produces fragmentation during it’s process with performance penalties in the long run (fixed by a manual VACUUM, i.e. an organized DB rebuild). It should be great a manual operation in your app, something like asking AFTER the backup routine “Do you want to optimize the DB? It can take some time” and run a VACUUM and ANALYSE (shrink DB and optimize indexed operations). If something goes wrong during this process you have a backup.
I would prefer a BACKUP button with an option for OPTIMIZE the DB after. The user will get a NEW highly manipulated DB and during the rebuild process something bad could occur, so, it could be great to guarantee a backup first. Users always postpone backups and love optimizations.
I already have a database backup button, so basically when the button is clicked - backup the database as normal, and then analyse, and then vacuum?
Im still not sure exactly where to put the pragma code. So I create the actual database file, but then before I perform the SQLExecute to create the table and columns - I should run another SQLExecute containing the SQLite pragma code?
Again, I would prefer make the OPTIMIZE (vacuum+analyze [this order, analyzing the organized and shrunk db is potentially faster]) part OPTIONAL after the backup, it’s not really necessary to run every time. Using this way you don’t need to use the auto vacuum full. But if you wish so, send the pragma after the connection, as Horst said. It keeps active during all the section and tables created under this mode will be reorganized (if full mode) at every commit.
The pragma must be send BEFORE creating or using the tables. If you intend to use the incremental feature you MUST send a “PRAGMA incremental_vacuum(9);” (remove from 0 to 9 free pages, or a PRAGMA incremental_vacuum(); to remove all free pages) every time you need to apply the vacuum routine instead of automatically at every commit.
What I said was that you could just IGNORE the Pragma auto_vacuum. As you would be handling it manually asking to the user after the backup.
[quote=146424:@Norman Palardy]do it “once in a while” and how “once in a while” really depends on how busy your database is esp with deletes
if you never delete then you mostly don’t have to worry about doing it a lot[/quote]
Absolutely if you don’t have many deletes and/or database transactions that are in the many per second/minute than I wouldn’t worry about this extra effort Richard especially knowing the size of your database as a user of your software that you are asking this question for.
I wasted time last week on this problem and do not found a reason why this does not worked (on Xojo).
I found the 0, 1, 2 values (and text explanations) on a web site page, but nothing / nowhere I found the part that states one have to set this PRAGMA before adding a Table to the db (more precisely, just after creating the db file).
This is an everyday trouble: how can I keep more than 20% of boot disk space while working with my computer ?
Why 20%: because the whole OS performances will be slowing down drastically otherwise.
One solution I found (before buying these ~ß~?~ß? laptop with either 64GB / 256 GB internal SSD) is to set two partitions to the boot disk: one for the OS to boot on / large enough, and the second for Data.
Other requirements are to set some storage default folder on the Data partition instead of boot partition: Firefox, Safari, (your-browser-name) downloads folder is an example; another is the temporary storage folder of your DVT software (THC, EyeTV, etc.).