[Last Call] Learn how to a build a cloud-first strategyRegister Now


MSSQL query result compression

Posted on 2004-11-09
Medium Priority
Last Modified: 2012-05-05
Guru's, hope you guys can help me with this...

A customer sends queries from a remote location to my MSSQL server. Some, or most of these queries have results of more than 25000 rows. The bandwidth between him and me is only 1.5 Mbit and he complains about slow response.

Is there a way to compress the results from the query in any way? If there's no SQL way (ODBC) any other compression ideas would also be welcome.
Question by:martap
LVL 13

Expert Comment

ID: 12534400
Can't you setup a ISA Server or web-caching firewall at the customers remote location?

There is no compression on ODBC, you would have to write some custom routine at this time to hook into the two sites.

Alternativly have a second machine at the customers site for the reporting (read-only) with a delay from the primary machine.
Another alternative is to generate the reports as files, then just send the files to limit the hdd/proc latencys that you will experience.

If he is returning more than 25,000 rows this may take a little while and will probably be a combination of hdd/proc/bandwidth latencys all adding up to the slow response.
LVL 50

Expert Comment

ID: 12534655
is the slow response the transmission or the client app actually processing a 25000 row result set?

does he need all 25K rows?

is he does need all 25k, then you'll only help by either speeding up his client processing, and/or
putting the information local to him...

have you considered replication/log shipping/ and/or MSDE?

Author Comment

ID: 12535316

customers complained about application slowness, so we tried running a heavy query directly from the application server through Query Analyzer. This took about 4 min. Running that same query locally on the sql server took about 30 sec.

The application is a reporting tool (Cognos) and he needs all rows. Replication and log shipping is not an option. I really need some kind of compression technique to solve this. Isn't there some kind of general tcp compression tool that you can install on both sides?

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 13

Expert Comment

ID: 12535562
Isn't there some kind of general tcp compression tool that you can install on both sides?

Only if you look at the hardware layer such as:

This would enable you to setup a VPN and run compression between the two sites.

There are many alternatives from the likes of Cisco/...:

But it may require a change of hardware, something your client may not appreciate.

Expert Comment

ID: 12535975
I think you can use a stored procedure on server side to:

1- generat the result set as xml
2- comprees it
3- send it

and on client side do:

1- decompress
2- view

Or use server side paging:


Best regards


Author Comment

ID: 12536985

Hardware solution will be very difficult (read impossible) to realize. I had a thought of installing a SSH service on the MSSQL server and PuTTY on the Appl Server. Then using port forwarding from within PuTTY. This way the traffic would be send through the SSH tunnel compressed.

I'm looking for a similar software solution but not as difficult.
LVL 13

Accepted Solution

danblake earned 1500 total points
ID: 12542188
Have a look at:

This is at least open source, and seems to suit your needs without adding any extra hardware.

This way you would not need to reroute the traffic, just setup the VPN between the two sites and away you go -- auto compression -- no port rerouting required.
LVL 13

Expert Comment

ID: 12542210
The big benefit of hardware, is that it is easily replacable and then does not place any load on your servers that should not be there -- software solutions will all require some overhead.

As PuTTY is a telnet client -- this is not suitable for sending traffic to <-> from servers, a VPN woud be better in this instance.

Author Comment

ID: 12542451

VPN is an option that's less of a hackle to put up.. I'd however have to look at the compression/overhead encryption ratio to see if it gives the desired effect.

But thanks danblake...

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question