Query Performance with remote SQL Server - direct connection


We have a remote SQL server in Australia (we are in the UK). We are connected directly to it (not linked server) and I was wondering if there was any way of compressing the returned data sets or something similar? We have the same database running in both countries and a query that returns 7K records here takes under 1 sec to display but nearly a minute for the same query that returns 4K from Australia.
Who is Participating?
Ryan McCauleyConnect With a Mentor Data and Analytics ManagerCommented:
Are you sure the slowness is caused by the bandwidth required for the result set? To confirm, run the same query with a "SELECT TOP 1" at the top so that it does all the processing, but then returns a truncated result set - if it runs under 1 second locally and is still slow on the remote server, then something else is at play causing the slowness. If it runs in under 1 second also, then you know the query lag is caused by the size of your result set, not by the query itself running.

That said, it doesn't appear that there's any way to compress the resultset of your query natively - that would have to be done using something like SQL-CLR, and it's really tricky. The only way to speed up receipt/display of the result is to return less data - if there are any columns you can drop (or even trim - are you returning CHAR when you could be using VARCHAR, for example) - or any rows that aren't necessary in the results? If so, removing them will save the bandwidth.

I know that's not ideal, as you may need all the data you're asking for, but it seems to be the only easy way - otherwise, you're looking at trying to compress the data in-row at the query layer, return it to your app, and then uncompress it before it's displayed. Though there may be some gain, it's hard to imagine it's worth the administrative effort and issues you'll surely have to hunt down and solve, and the added CPU load and delay at both ends could consume a reasonable portion of the time you're looking to save in the first place.
Aaron TomoskyConnect With a Mentor Technology ConsultantCommented:
here are some interesting ways to redirect the output. It may save a few min
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.

All Courses

From novice to tech pro — start learning today.