Solved

Query Performance with remote SQL Server - direct connection

Posted on 2013-01-25
2
453 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
2 Comments
 
LVL 38

Assisted Solution

by:Aaron Tomosky
Aaron Tomosky earned 100 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 400 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
"In order to have an organized way for empathy mapping, we rely on a psychological model and trying to model it in a simple way, so we will split the board to three section for each persona and a scenario and try to see what those personas would Do,…
The viewer will learn how to count occurrences of each item in an array.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

939 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now