Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 3 of 3 total
Thread Index Compression worthit?
Fri, Feb 19 2021 5:42 PMPermanent Link

David

I currently don't use any index compression and wondering if this is a mistake of mine and am I likely to see any performance improvement/degradation by using it.

I have a few text based fields that are not unique or primary eg 'TI-21.0001\SP-M.1234' that might have a couple of records linked to it, usually no more than 4 using that index key, otherwise they are all different but have a similar format,  They are not all the same size(max 30 chars).  I think in this case I would need to use Full compression.

I am noticing the .idx files on some tables with ~37k records in it is larger than the dat file and I know compression would sort this, but just want other peoples opinion on using compression.

Thanks
David.
Sun, Feb 21 2021 7:17 PMPermanent Link

Raul

Team Elevate Team Elevate

On 2/19/2021 5:42 PM, David wrote:
> I currently don't use any index compression and wondering if this is a mistake of mine and am I likely to see any performance improvement/degradation by using it.
>
> I have a few text based fields that are not unique or primary eg 'TI-21.0001\SP-M.1234' that might have a couple of records linked to it, usually no more than 4 using that index key, otherwise they are all different but have a similar format,  They are not all the same size(max 30 chars).  I think in this case I would need to use Full compression.
>
> I am noticing the .idx files on some tables with ~37k records in it is larger than the dat file and I know compression would sort this, but just want other peoples opinion on using compression.

IMHO index compression is a trade-off between reading/writing less data
vs spending CPU cycles on compression. In theory it helps with caching
(less info needed to cache in theory so might remain in cache longer).

I suggest run some tests to see if it would make any difference both for
performance and/or index table sizes (which alone might be worthwhile).

Without knowing more full compression should be first choice to test if
they are just string fields.

Raul

Mon, Feb 22 2021 11:16 AMPermanent Link

David

Thanks Raul.

I know the manual says there really is no good reason not to use index compressions and I suppose I should have paid more attention to this, I think the reduced index file size in itself might be helpful meaning less data to write to disk as my server has a slow RAID write on it.  

I normally stay away from compression for a fear that it might make things worse if something goes wrong as recovery is not quite so easy, but that was manly from file system compression, not in a DB.  I have scouted around the forum and I have not see anything that indicates there being issues with using compression and I was just looking for an opinion of others who have used/not used it.

Regards
David.

Raul wrote:

On 2/19/2021 5:42 PM, David wrote:


IMHO index compression is a trade-off between reading/writing less data
vs spending CPU cycles on compression. In theory it helps with caching
(less info needed to cache in theory so might remain in cache longer).

I suggest run some tests to see if it would make any difference both for
performance and/or index table sizes (which alone might be worthwhile).

Without knowing more full compression should be first choice to test if
they are just string fields.

Raul
Image