Re: [sqlite] Vacuum needed?
D. Richard Hipp wrote: >> Though if your db has reached a steady-state size you might consider >> running a VACUUM just *before* deleting the records, to avoid peaks >> persisting ... and even then (say) every *other* week. >> Just a thought ! >> > VACUUM also defragments a database file which sometimes helps > subsequent operations to run faster. > Thanks Mike & Richard Your inputs are useful - the database won't grow that much and isn't really searched out of sequence much. It's mostly going to be used to just access things in sequence. But the inputs are useful towards other projects in the future!1 Cheers, Mohit. 10/28/2008 | 1:18 AM. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Vacuum needed?
On Oct 27, 2008, at 7:34 AM, MikeW wrote: > Mohit Sindhwani <[EMAIL PROTECTED]> writes: > >> >> Cory Nelson wrote: >>> vacuum shrinks the database size by removing empty pages. sqlite >>> will >>> normally reuse empty pages - so vacuum is only useful if you don't >>> plan to insert anything else, otherwise it will be slower. >>> >> >> Thanks Cory! That clears a major worry (regarding the speed of >> vacuuming a large database with a large number of records deleted) >> in my >> mind. Temporarily, letting the database take up extra space is not a >> worry, so we'll just leave it in the records deleted state and let >> SQLite reuse the memory as it goes along. >> >> Best regards >> Mohit. > > Though if your db has reached a steady-state size you might consider > running a VACUUM just *before* deleting the records, to avoid peaks > persisting ... and even then (say) every *other* week. > Just a thought ! > VACUUM also defragments a database file which sometimes helps subsequent operations to run faster. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Vacuum needed?
Mohit Sindhwani <[EMAIL PROTECTED]> writes: > > Cory Nelson wrote: > > vacuum shrinks the database size by removing empty pages. sqlite will > > normally reuse empty pages - so vacuum is only useful if you don't > > plan to insert anything else, otherwise it will be slower. > > > > Thanks Cory! That clears a major worry (regarding the speed of > vacuuming a large database with a large number of records deleted) in my > mind. Temporarily, letting the database take up extra space is not a > worry, so we'll just leave it in the records deleted state and let > SQLite reuse the memory as it goes along. > > Best regards > Mohit. Though if your db has reached a steady-state size you might consider running a VACUUM just *before* deleting the records, to avoid peaks persisting ... and even then (say) every *other* week. Just a thought ! Regards, MikeW dow ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Vacuum needed?
Cory Nelson wrote: > On Sun, Oct 26, 2008 at 8:17 PM, Mohit Sindhwani <[EMAIL PROTECTED]> wrote: > >> I'm setting to to delete a bunch of old records ever 2 weeks in a cron >> job and initially I just wanted to do delete * from table where >> datetime(created_on, 'localtime') < some_date. >> >> Then, I remembered about vacuum - do I need to vacuum the database >> whenever I delete the records? Should I just 'exec' the statement by >> setting the sql to "delete something ... ; vacuum;" and execute >> that? Is that the recommended approach? >> >> > > vacuum shrinks the database size by removing empty pages. sqlite will > normally reuse empty pages - so vacuum is only useful if you don't > plan to insert anything else, otherwise it will be slower. > Thanks Cory! That clears a major worry (regarding the speed of vacuuming a large database with a large number of records deleted) in my mind. Temporarily, letting the database take up extra space is not a worry, so we'll just leave it in the records deleted state and let SQLite reuse the memory as it goes along. Best regards Mohit. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Vacuum needed?
On Sun, Oct 26, 2008 at 8:17 PM, Mohit Sindhwani <[EMAIL PROTECTED]> wrote: > I'm setting to to delete a bunch of old records ever 2 weeks in a cron > job and initially I just wanted to do delete * from table where > datetime(created_on, 'localtime') < some_date. > > Then, I remembered about vacuum - do I need to vacuum the database > whenever I delete the records? Should I just 'exec' the statement by > setting the sql to "delete something ... ; vacuum;" and execute > that? Is that the recommended approach? > vacuum shrinks the database size by removing empty pages. sqlite will normally reuse empty pages - so vacuum is only useful if you don't plan to insert anything else, otherwise it will be slower. -- Cory Nelson http://www.int64.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
