I have an android app which involves login to the app and it performs many CRUD operations. When user logins first time, it logs him/her quickly if there is any data available for that user that will be downloaded. But later on when re-login again, theprocess is very slow, sometimes it takes around 20 to 30mins. So, I read about using VACUUM to avoid free space created when the record is deleted.
I am bit confused when and how to use VACUUM, is it while opening the database that is when user login ? or some other time.
Any suggestions on this would be helpful. Thanks
From: https://sqlite.org/lang_vacuum.html
Running VACUUM ensures that each table and index is largely stored contiguously
within the database file
About many CRUD operations: the db file usually becomes fragmented,
especially
when these operations involve say deleting large amount of data.
With Java code it's fairly simple:
db.execSQL("VACUUM");
although the process can take a while and it is not possible to monitor its progress.
Of course you don't need to execute this statement every time you run the app, but periodically.
Or you may let the user execute this process, if he/she experiences delays on loading the data, or even logging in, after you make clear that such an operation, just like any reconstructing operation is not 100% safe, so propose first a backup.