The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

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.

asked 23 May '11, 08:23

Justin%20Willey's gravatar image

Justin Willey
accept rate: 20%

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:)

(23 May '11, 09:54) Volker Barth

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

(23 May '11, 10:21) Thomas Dueme...

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:

  filename VARCHAR(255), 

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).

permanent link

answered 23 May '11, 10:27

Mark%20Culp's gravatar image

Mark Culp
accept rate: 40%

edited 23 May '11, 11:11

Comment Text Removed

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.

(24 May '11, 07:49) Justin Willey
Replies hidden

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...)

(24 May '11, 08:27) Volker Barth

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

(24 May '11, 08:58) Graeme Perrow

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 :-)

(24 May '11, 09:35) Justin Willey

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.

(27 May '11, 16:39) Ian McHardy
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 23 May '11, 08:23

question was seen: 1,630 times

last updated: 27 May '11, 16:39