Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 6 of 6 total
Thread Index Compression
Wed, Oct 8 2008 6:24 AMPermanent Link

Malcolm
Hi,

Can anyone please explain the difference and advantages / disadvantages of having the index compression set to Full / None.

Regards
Malcolm
Wed, Oct 8 2008 7:03 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Malcolm


>Can anyone please explain the difference and advantages / disadvantages of having the index compression set to Full / None.

Performance & disk space. Its a trade off between the time taken to read from disk (and possibly transfer over a network) vs the time taken to compress/decompress the index. Obviously when an index is compressed its faster to read it from disk so if the saving there is greater than the time taken to decompress it the app's performance will go up a bit.

The amount of compression you get depends on the nature of the indexed field. If you have lots of repeating strings in the field it could be worth using compression, if you don't its best to stay away from it.

Unless you are looking to save the last byte of disk space or have a nasty performance problem I'd just ignore it.

Roy Lambert [Team Elevate]
Wed, Oct 8 2008 7:37 AMPermanent Link

Malcolm
Roy Lambert <roy.lambert@skynet.co.uk> wrote:

Malcolm


>Can anyone please explain the difference and advantages / disadvantages of having the index compression set to Full / None.

Performance & disk space. Its a trade off between the time taken to read from disk (and possibly transfer over a network) vs the time taken to
compress/decompress the index. Obviously when an index is compressed its faster to read it from disk so if the saving there is greater than the
time taken to decompress it the app's performance will go up a bit.

The amount of compression you get depends on the nature of the indexed field. If you have lots of repeating strings in the field it could be worth
using compression, if you don't its best to stay away from it.

Unless you are looking to save the last byte of disk space or have a nasty performance problem I'd just ignore it.

Roy Lambert [Team Elevate]

Hi Roy
Thanks again for your assistance,
I have been looking at the manual where is says that "In most cases it is not very useful to specify no compression"
One of the indecies on the biggest table is a date field which could potentialy have have 700,000 entries for a given day (realistically less 70,000),
and this has no compression, would some sort of compression help speed  up SQL's ?

Many thanks
Malcolm
Wed, Oct 8 2008 8:05 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Malcolm

A request before I answer your question - please cut the irrelevant text from your replies. It will make it much easier to spot your response. If you can also mark the original text (eg > at the start of each line or << >> wrapped around it as Tim does) that also would be helpful.

>I have been looking at the manual where is says that "In most cases it is not very useful to specify no compression"
>One of the indecies on the biggest table is a date field which could potentialy have have 700,000 entries for a given day (realistically less 70,000),
>and this has no compression, would some sort of compression help speed up SQL's ?

You can certainly try duplicate-byte as recommended in the manual. Wether or how much it would speed things up will depend on the level of duplication which I suspect, with dates and 70k+ records, should be fairly high. Its certainly worth trying especially on the basis of if it makes things worse it can always be removed.

Roy Lambert [Team Elevate]
Wed, Oct 8 2008 8:31 AMPermanent Link

Malcolm
Hi Roy.

Point taken Smile

Appricate your time, will try it and see.

Many thanks
Malcolm
Wed, Oct 8 2008 9:18 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Malcolm


1. Much better
2. Don't forget to report back

Roy Lambert [Team Elevate]
Image