CODEWITHSUNDEEP
sql-server
Sean U
Sean U

Reputation: 6850

Is it worthwhile to compress BLOBs?

I'm looking at altering a table with an IMAGE column. I'll be changing it to VARBINARY(MAX), but I'm also considering compressing the data in this column in the hope that it will improve performance.

Here are the things I'm considering:

  1. Individual values are generally small but can be very large, up to several MB.
  2. The table sees relatively high query volume.
  3. Individual queries may retrieve hundreds of rows' worth of data.
  4. Round-trip time on this query sometimes dominates application performance.

It's more about reducing the amount of time it takes to get results back from a query than reducing table size, so compression would be done in the application rather than on the server. So, is it worth a shot, or will I be wasting my time?

Upvotes: 2

Views: 2878

Answers (2)

Oleg Dok
Oleg Dok

Reputation: 21776

I have such a table with compressed xmls sized from 1k to 30m - the compression really saves the space (bzip2). Also I have an archive tables which stores thousanrs of this xmls concatenated together and then compressed - this saves valuably too. The only thing worse for the archive - that I store for each xml its position and sizein concatenated blob , so you have to uncompress much more data to get the little xml. So, or concatenate things that probably will be queried together, or very rarely accessed

Upvotes: 1

Tevo D
Tevo D

Reputation: 3381

If the content is something already compressed (images, videos, some PDF's) then there will be little value in another compression layer on top of that.

If it is large bulk text, XML, etc., or something else which is very compressible, then there can be some real savings.

Upvotes: 4

Related Questions