Re: Core Data troubles - not removing objects

FROM : Benjámin Salánki
DATE : Sat Nov 03 21:09:57 2007

On Nov 2, 2007, at 3:04 AM, Chris Hanson wrote:

> On Nov 1, 2007, at 3:53 PM, Benjámin Salánki wrote:
>

>>> The sqlite database file format won't grow and shrink in response 
>>> to every change you make, for performance reasons data is mapped 
>>> to pages which may contain many individual data records (or a 
>>> single data record can span many pages).

>>
>> So are you saying removing an item from the store won't reduce its 
>> size on the disk? How can I tell my users that even if they do 
>> remove all their data they won't regain any of their precious hard 
>> disk space? Surely there has to be a way to reduce the size of the 
>> file if I remove data from it. PLease tell me it's possible.

>
> You asked about removing *an* item.  If enough items are removed to 
> free up a page in the database file, SQLite's automatic database 
> vacuuming will reduce the size of the file as it rearranges the data 
> to remove that page.  Similarly, the file size may be reduced if you 
> remove an item that itself occupies multiple pages (such as a 
> thumbnail image).


My problem is, that if I include a huge amount of data like an 
uncompressed image that will swell the SQLite file to 72MB, removing 
the item does not reduce the file size. I found that if I add another 
item later that should be around the same size the file size does not 
change so what you wrote makes sense in that the "holes" inside the 
file are filled up with new data but I can't get the file size to 
reduce even by a few bytes.

I was thinking of maybe implementing a "consolidate database" function 
that would load the data from the file to memory, remove the file and 
recreate the items which would reduce the file size if I am correct. 
Or does anyone have a better solution?

Ben

>
>
> An SQLite file is arranged as a collection of pages and the data 
> within those pages is managed via B-trees, not as simple fixed-
> length records (which is what it sounds like you're expecting). 
> This is much more efficient for searching and for overall storage, 
> since it allows SQLite to optimize how it stores both data and 
> indexes in a single file, and is also the foundation of its data 
> integrity (transaction & journaling) mechanism.
>
> However, the cost of this is that some delete operations may leave 
> holes in the file.  If you delete some data and add other data, the 
> holes left by the deleted data may be filled by the added data, or 
> the file may be vacuumed to compact its data, whichever SQLite 
> considers most appropriate based on the operations you're performing.
>
> Does that make sense?
>
>  -- Chris
>