Compressed Columns - where's the squeeze - client or server?

[justin_willey]

I've been reading the docs (!) but can't nail this down for sure. If you create a compressed column, is compression / decompression carried out on the server or the client?

I'm looking at storing a lot of base64 text and want to limit the amount of stuff I'm pushing up and down the network (I'm much less fussed about disk space). Obviously, if the SQLA client does the (de)compression that's great, otherwise I should probably do my own (un)zipping first.

[VolkerBarth]

My assumption: As compression/decompression is done when storing/loading data, it must be on the server-side. Furthermore, as query processing generally takes part in the engine, you wouldn't be able to query against a compressed column if the decompression would happen on the client-side.

But I'm still guessing:)

[thomas_duemesnil]

You can user Compress=YES in your connection string to limit the transferred amount on the Network.

[MarkCulp · Accepted solution]

The term "compressed columns" means that the column data is compressed when it is stored in the database. You can tell the database server that you want the column data to be compressed (when stored in the database file) by adding the COMPRESSED keyword to the char, varchar, or long binary/varchar column in your table definition. Here is an example from the docs:

CREATE TABLE t ( 
  filename VARCHAR(255), 
  contents LONG BINARY COMPRESSED 
);

Using compressed columns will have no effect on the quantity of data which is exchanged between client and server. To decrease the amount of client-server traffic you need to enable communication encryption by adding the COMPRESS=YES connection parameter to your connection string.

You may also want to read about the compression threshold connection parameter - the COMPTH parameter controls when compression will be performed in order to get the best mix of uncompressed and compressed packets - e.g. there is no need to take the time to compress short packets (packets smaller than the size of a TCP packet).

[justin_willey · Accepted solution]

Thanks for confirming what's happening, Mark. We already use the network compression switch, but still find considerable extra benefit when (pre) compressing data we know is going to be large (and significantly compressible) eg Word docs, big text files etc.

[VolkerBarth · Accepted solution]

So I conclude that the usage of the builtin COMPRESS function would not help, either, as their arguments would have to be sent to the server, too, in order to get the compressed result?

(Assuming there's no local database engine to COMRESS again...)

[graeme_perrow · Accepted solution]

@Volker: Yes, if you are using the COMPRESS function, the compression is done by the server.

[justin_willey · Accepted solution]

Exactly - I was hoping that the compressed column functionality was client side (ie implemented by the various client drivers etc), but as it isn't - revert to plan A 🙂

[ian_mchardy · Accepted solution]

In my experience, client compression gives decent compression rates for compressible data (although not the highest possible).

If you think you have the COMPRESS=YES connection parameter and are still getting poor communication performance, you can check the output from sa_conn_compression_info to confirm compression is enabled and see the overall compression rates you are getting.