My tables are starting to grow, in some cases the idx file is larger than the table.  Most of these are full of dates and currently I only have compression = full on text based indexes.  Would it make sense to compress the date indexes as well, dupbyte I would guess.

Doing so does makes a difference to the index size significantly, just want to know if there is any good reason why I should not do this?  The manual does say there is no good reason not to have compression on an index, but experience of others would be helpful.

If you are doing a date based search, how would compression affect this, would each record have to be de-compressed first on a record by record basis, or does dbisam de-compress the column as a whole.  Will using compression have any affect on memory usage?

Thanks in advance.
David.

David,

<< My tables are starting to grow, in some cases the idx file is larger than the table.  Most of these are full of dates and currently I only have compression = full on text based indexes.  Would it make sense to compress the date indexes as well, dupbyte I would guess. >>

There is a good guide for this in the manual:

https://www.elevatesoft.com/manual?action=viewtopic&id=dbisam4&product=rsdelphiwin32&version=10.4&topic=Index_Compression

under "Compression Recommendations" at the bottom.

<< Doing so does makes a difference to the index size significantly, just want to know if there is any good reason why I should not do this? >>

If adding compression reduces the index size dramatically, then you should definitely use it on the indexes that apply.

<< If you are doing a date based search, how would compression affect this, would each record have to be de-compressed first on a record by record basis, or does dbisam de-compress the column as a whole.  Will using compression have any affect on memory usage? >>

Index pages are de-compressed when they are read from disk into the buffer manager for the table and compressed when an index page is modified and needs to be written back to disk.  You can cut down on the amount of de-compression during reads by increasing the amount of index pages buffered:

https://www.elevatesoft.com/manual?action=viewtopic&id=dbisam4&product=rsdelphiwin32&version=10.4&topic=Customizing_Engine

(under "Memory Buffer Customizations").

Tim Young
Elevate Software
www.elevatesoft.com