Solved

Query Performance with remote SQL Server - direct connection

Posted on 2013-01-25
2
457 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 39

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
After creating this article (http://www.experts-exchange.com/articles/23699/Setup-Mikrotik-routers-with-OSPF.html), I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…

820 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