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.
 
LVL 5
martapAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

danblakeCommented:
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.
0
LowfatspreadCommented:
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?
 
0
martapAuthor Commented:

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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

danblakeCommented:
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.
0
wael_tahonCommented:
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


0
martapAuthor Commented:

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.
0
danblakeCommented:
Have a look at:
http://openvpn.sourceforge.net/

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
danblakeCommented:
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.
0
martapAuthor Commented:

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...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.