Moved to Different Forum - SQL Server Query Execution Time Based on Latency and Internet Download Speed

Question

4.00/5 (1 vote)

See more:

I am wondering if anyone knows how internet connection latency and download speed affect query execution time in SQL Server and outside, say using ADO .NET DataAdapter instead of a Data Reader. I am trying to get better performance on a remote office, especially since Disaster Recovery can be better handled from the headquarters.

I ran a test in SQL Server Management against the same database across the country. One internet connection had 4x the download speed, but a larger latency in ms. The difference is that one connection was a slow ethernet connection and the other a faster wireless connection.

Somewhat surprisingly, the connection with the faster download speed took 50% longer to return a recordset in the 5-20 MB range. Is this because querying in SQL Server Management Studio is similar to executing a query with a Data Reader instead of a DataAdapter? If not, what is the issue?

Posted 1-Apr-11 3:39am

T2102

Updated 1-Apr-11 3:58am

v2

Add a Solution

Comments

Albin Abel 1-Apr-11 9:48am

Query execution time or transportation time? I think query execution not takes place on the wire. If transportation then it depends on various parameters, the load, the speed etc.,

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Henry Minute · Answer 1 · 2011-04-01T03:55:00

I do not have a solution to your question but one thing that occurs to me is, can you be certain that the various connections that you used were under the same load when you did your tests.

Also, there is a Database Forum[^] here on The Code Project and you might attract more members with knowledge of this area if you were to ask your question there. If you do that, mention that you have asked here and were advised to ask there.

Dave Kreskowiak · Answer 2 · 2011-04-01T03:56:00

Solution 2

A forward only reader will always be faster than a DataAdapter. The time "on the wire" has nothing to do with the query execution speed. SQL Server only counts the time from the query request was received to the time the last result is FOUND, not the time it takes to get it on the wire.

Posted 1-Apr-11 3:56am

Dave Kreskowiak

Comments

T2102 1-Apr-11 10:06am

The query at the headquarters takes less than a second, but it can take 20-45 seconds for SQL Server Management Studio to finish executing the query and receiving the data at the remote office.

As an analogy, I've found xcopy to be very slow when using a remote network versus getting a file list of both directories and then using C++ commands to copy manual. I thought using a Data Reader might have the same performance implication.

Albin Abel · Answer 3 · 2011-04-01T03:57:00

Solution 3

This may help you with some hints on how data reader call transports data over network

http://msdn.microsoft.com/en-us/library/haa3afyz%28v=vs.71%29.aspx[^]

Here are some performance comparisions.

http://msdn.microsoft.com/en-us/library/ms978388.aspx[^]

Posted 1-Apr-11 3:57am

Albin Abel

Updated 1-Apr-11 4:07am

v2