asked on

MSSQL query result compression

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.

danblake

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.

Lowfatspread

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?

martap

ASKER

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?

danblake

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:
http://www.alliedtelesyn.co.nz/products/software.html

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

There are many alternatives from the likes of Cisco/...:
http://www.cisco.com/en/US/products/hw/routers/ps341/products_data_sheet09186a008017dc0a.html

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

wael_tahon

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:

http://www.windowsitpro.com/SQLServer/Article/ArticleID/40505/40505.html

Best regards

martap

ASKER

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.

ASKER CERTIFIED SOLUTION

danblake

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

danblake

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.

martap

ASKER

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