Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 477
  • Last Modified:

Query Performance with remote SQL Server - direct connection

Hi,

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.
0
suroma
Asked:
suroma
2 Solutions
 
Aaron TomoskyTechnology ConsultantCommented:
here are some interesting ways to redirect the output. It may save a few min
http://stackoverflow.com/questions/6798567/is-there-a-way-to-directly-compres-zips-the-result-from-a-sql-query
0
 
Ryan McCauleyDatabase and Reporting 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.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now