Post reply

Is table compression suitable to reduce I/O ?

  • February 11, 2015 at 5:23 am

    #304190

    Hi There,

    I have huge table, often I need to query the table on the whole; ie., data may required at any part of the table. So I think of to compress the table with the aim of reducing I/O.

    I just learning about the compression technique. I wonder about,

    1. is that really suitable for this requirement? If So which compression is good (Row or page )?

    2. Or is there any other alternate to reduce the I/O (Except Indexing)?

    3. Pros and cons ?

    Thanks in advance

  • February 11, 2015 at 9:12 am

    #1776981

    So I think of to compress the table with the aim of reducing I/O.

    Compression will help reduce I/O

    1. is that really suitable for this requirement?

    Yes

    If So which compression is good (Row or page )?

    Short Answer: it depends; you would have to test and see for yourself.

    2. Or is there any other alternate to reduce the I/O (Except Indexing)?

    Good data modeling and writing better queries, avoiding rbar is a fantastic way to reduce I/O.

    (Except Indexing)?

    Are you saying this because you often need to return all the rows? Correctly placed Indexes help reduce I/O. If you are really returning all the rows then consider filtered indexes.

    3. Pros and cons ?

    The pros include reduced I/O and less disk space used. In addition to the obvious administration overhead, the biggest con is how uncompressing data adds CPU overhead.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • February 11, 2015 at 10:01 am

    #1777001

    and compressing adds CPU overhead.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply