Anyway to compress results of SELECT statement to improve transfer speed over network?

Question

Anyway to compress results of SELECT statement to improve transfer speed over network?

Maxer

SSCrazy Eights

Points: 8964
More actions
June 11, 2018 at 11:37 am

#350898

When transfering a large resultset is there anyway to compress the data that gets sent over the network?
An example is when we load data into SSAS Tabular, it returns hundreds of millions of rows that take a good amount of time to transfer to the SSAS server. However, the data once it is transferred compressed down substantially once stored.
Is there anyway to send data over the wire compressed?
Seems like there would be some sort of Native SQL client command to "send this as compressed data"? Probably a pipe dream, but figured I would ask.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

Not native - but have a look at third party products - one of which is http://nitrosphere.com/nitroaccelerator/
Never used but have heard of it.
Other options are setting up VPN/Tunnels with compression on - but results may vary.

another option if you using something like EMC XtremeIO for SAN storage is to use snapshots attached to the SSAS server - no network trafic on this case.

Maxer SSCrazy Eights Points: 8964 More actions · Answer 2

frederico_fonseca - Monday, June 11, 2018 12:21 PM
Not native - but have a look at third party products - one of which is http://nitrosphere.com/nitroaccelerator/
Never used but have heard of it.
Other options are setting up VPN/Tunnels with compression on - but results may vary.
another option if you using something like EMC XtremeIO for SAN storage is to use snapshots attached to the SSAS server - no network trafic on this case.

I was curious about Nitrosphere as well... hmm

Thanks!

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

A brute force method would be to BCP the results out in "Native" format and import it at the receiver. Numerically based columns such as INT, BIGINT, and DATETIME (etc, etc) would be "compressed" to their "behind the scenes" sizes of 4, 8, and 8 bytes respectively.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)