Hi,
could someone please advise me if I need to do an error check between Vacuuming and Analysing a database - as in my code below.
[code] // --------------------------------------- DEFINE THE DATABASE PATH
Dim source As FolderItem = SpecialFolder.ApplicationData.Child(“my Data”).Child(“my.db”)
// --------------------------------------- DATABASE EXISTS, SO PROCEED
If source <> Nil Then
db.SQLExecute(“VACUUM”)
// ---------------------------------------- ERROR VACUUMING DATABASE, SO DISPLAY THE ERROR MSGBOX
if db.error then
MsgBox("Error: " + Str(db.ErrorCode) + " - " + db.ErrorMessage)
exit
Else
db.SQLExecute(“ANALYZE”)
// ---------------------------------------- ERROR ANALYSING DATABASE, SO DISPLAY THE ERROR MSGBOX
if db.error then
MsgBox("Error: " + Str(db.ErrorCode) + " - " + db.ErrorMessage)
exit
Else
myWindow.Close
End if
End If
// --------------------------------------- DATABASE NO LONGER EXISTS, SO DISPLAY ERROR
Else
MsgBox(“No database file.”)
Well, it’s always a good thing to check for a database error on SQLExecute.
Why are you doing this via code? Most databases don’t need the vacuum command used very often and analyze is even more rare. What are you trying to accomplish?
Bob,
in a recent discussion here on these forums - it was advised that if an SQLite database has data which gets deleted / edited often - it may be worthwhile offering the user an option to vacuum and analyse the database, in order to keep it running at 100% efficiency.
This is never done unless the user specifically requests it - hence why I was just wondering if my code looked correct
[quote=147412:@Richard Summers]Bob,
in a recent discussion here on these forums - it was advised that if an SQLite database has data which gets deleted / edited often - it may be worthwhile offering the user an option to vacuum and analyse the database, in order to keep it running at 100% efficiency.
This is never done unless the user specifically requests it - hence why I was just wondering if my code looked correct ;)[/quote]
Richard how large of a DB do you expect to have that would require a vacuum for SQLite? I would suggest that you would not need to bother with this unless you were expecting your database to have a HEAVY amount of deleted records and/or the size to increase dramatically over 200MB +. How large is your DB currently?
Also a couple things to note that you must ensure that your software will not allow transactions while vacuum is running and the amount of disk space that your database uses may increase 2X while using vacuum also. FYI.
[quote=147421:@Richard Summers]Bob / Mike,
I never realised this should only be done for LARGE databases - in that case, I will forget the idea.
Thank you.[/quote]
Its just not worth the effort to lock out your users and then possibly doubling your DB File size for really no gain. I know this because I use your app and like Bob pointed out millions and/or records with transactions many transactions per second/minute.
[quote=147421:@Richard Summers]Bob / Mike,
I never realised this should only be done for LARGE databases - in that case, I will forget the idea.
Thank you.[/quote]
Bob nor I are telling you to forget about it… We are just giving you the Pros/Cons in context from our experiences for you to decide if you want to proceed or not.
Mike,
yes, thanks - I appreciate you and Bob pointing that out for me - I never realised it is only really beneficial for large, heavily used databases.
Oh, you’ve done the work. Keep it in the project and maybe make a super special backdoor to get to it. It doesn’t hurt to have it in times of emergency.
If you are using rowID for the record primary key rather than a user defined field then vacuum is a very dangerous thing since there is no guarantee that rowID won’t change. Personally, I never recommend using rowID but I know some people still use it.
Hello peeps. As any DB admin knows, any growing relational DB that supports transactions and indexing (humm maybe all? :)) must have something called Maintenance Plan. It includes routines like backup, guarantee integrity, recover space and optimize operations. As I told before, the optimization should be an optional tool. And yes, makes sense for DB with some thousand records not hundreds. Fragmentation and not useful data (large transaction logs) must be shrunk from time to time. This is true for MS-SQL, PostGreSQL, MySQL and even SQLite (maybe makes even more sense for CubeSQL due to multiple user access). Yes, optimizations can be ignored for small write once, read many, tables.
