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
7.0k114148219
accept rate: 21%

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:

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

permanent link

answered 23 May '11, 10:27

Mark%20Culp's gravatar image

Mark Culp
23.4k9132275
accept rate: 40%

edited 23 May '11, 11:11

Comment Text Removed
1

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
1

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
3

@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
1

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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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:

×415
×248
×137
×114
×10

question asked: 23 May '11, 08:23

question was seen: 1,709 times

last updated: 27 May '11, 16:39