?
Solved

Query Performance with remote SQL Server - direct connection

Posted on 2013-01-25
2
Medium Priority
?
466 Views
Last Modified: 2013-02-11
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
Comment
Question by:suroma
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 39

Assisted Solution

by:Aaron Tomosky
Aaron Tomosky earned 300 total points
ID: 38820742
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
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 1200 total points
ID: 38825708
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

7 Extremely Useful Linux Commands for Beginners

Just getting started with Linux? Here's a quick start guide that has 7 commands that we believe will come in handy.

Question has a verified solution.

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

WARNING:   If you follow the instructions here, you will wipe out your VTP and VLAN configurations.  Make sure you have backed up your switch!!! I recently had some issues with a few low-end Cisco routers (RV325) and I opened a case with Cisco TA…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

777 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