[yunqa.de] Re: Regular cleanup needed for SQLite databases?
- From: Delphi Inspiration <delphi@xxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx
- Date: Fri, 28 Jan 2011 15:20:06 +0100
On 28.01.2011 12:02, Tobias Rapp wrote:
> My main concern is not directly the file size of the database but
> more if data structures inside the SQLite database file (table rows,
> indexes, ...) are prone to fragmentation over time.
This kind of data fragmentation may happen to all b-tree database
engines, and DISQLite3 is no exception. However, since you wrote that
the "amount of rows is low but amount of insert/update/deletes is high"
you should see quite a small database file which does not suffer much
from fragmentation.
Please take into consideration that DISQLite3's internal page cache (see
PRAGMA cache_size) greatly reduces the effects of b-tree page
fragmentation. Database slowdowns are usually noticeable only if the
majority of pages need to be re-read from disk instead of the cache.
Since your database is rather small, this should not happen too often.
If it does, consider increasing the page cache using PRAGMA cache_size.
> As the VACUUM command rebuilds the entire database I guess I will add
> some call triggered every few hours to my application.
VACUUM will re-organize all database structures to their optimum, but
this can take considerable time and block your application in the
meantime. You may see better overall performance with PRAGMA
auto_vacuum=full or no vacuuming at all, possibly at the expense of some
RAM for a greater page cache (above).
Running some tests on your actual data will quickly reveal which
approach works best for you. I am quite positive that you will see far
better results than with your old MS Access database.
Ralf
_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa
Other related posts:
