Home Forums Article Discussions Article Discussions by Author Discuss Content Posted by Yoel Martinez Using CLR integration to compress BLOBs/CLOBs in SQL Server 2005 RE: Using CLR integration to compress BLOBs/CLOBs in SQL Server 2005

  • July 16, 2009 at 11:31 am

    #1025811

    JoeA (3/5/2007)

    Hi Yoel - I enjoyed your article on blob compression - but I believe there is a bug in the compression function...

    .....I determined that the cause of the problem is that the blobData.Length (the length of the byte array) value is not reliable - it didn't actually match the length of the blob data. It was often 6-8K when the actual data was much smaller, even zero length. If I use blob.Length (the length of the SqlBytes parameter) when writing to the compression stream, I got the results I expected.

    Just wanted to let you and the readers know what I found... please validate as you see fit

    Oh, BTW - We are thinking about using blob compression to reduce our SAN chargeback costs (which are insanely high) - the blob data we're compressing needs to be online with the active data in our application, but isn't updated after it's inserted, and isn't selected very often relative to the rest of the data; so the compute costs are manageble.

    MANY MANY THANKS, JoeA!!!

    I was also hit by this exact bug.

    Compress/Decompress simply corrupted the data... Decompression actually blew it even bigger.

    So this one change fix my issue.

    (SQL 2005 SP2 build 2005.090.3073.00, btw.)

    Also THANKS to Yoel!!

    This solution should actually work well for document archiving (my exact case). The BLOB data is still available to the users, but actual usage of it is low. Thusly, the performance is non-issue at all. But the space savings and availability is a huge benefit.