Post reply

query runs faster when connected locally using IP as opposed to hostname

  • May 9, 2011 at 10:41 am

    #145274

    Hi

    I have a strange problem that a query is returning in 11 sec when query analyser is connected locally using the hostname, and in 1 second when it is connected locally using the IP address.

    This is a new server being built, by a different team... and im trying to get them to investigate networking issues. but im at a loss to explain this reality.

    the query actually is a select, joining a few tables, and is also returning an ntext column. there are only about 800 rows returned. noticeably, the issue disappears when the ntext column is removed from the query, but im assuming that will be due to less data having to be returned to the screen masking the slowness of the query.

    the new box is setup to use a dns, although im sure the dns is not being configured correctly. A test to add the hostname to resolve to the local IP address in the HOSTS file, also did not resolve the issue.

    pinging and tracert to the IP and hostname are identical, and don't reveal any slowness.

    any ideas, or suggestions where i can investigate further?

  • May 9, 2011 at 10:44 am

    #1321842

    what's the rowlength / setsize of the returned set ?

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • May 9, 2011 at 10:52 am

    #1321849

    Hi

    The query is being returned in query analyser using all defaults of the client. the max chars per col is set to 256 as default.

    however, the issue is that it will return in the same client in 1 sec when using IP to logon locally (i.e. query analyser using local ip and windows auth). And the same client with the same setup will take 11 seconds to return when using the hostname to connect locally (i.e. query analyser using local hostname and windows auth).

    essentially the only change between them has been the IP address being used to connect. nothing else has changed.

  • May 9, 2011 at 10:57 am

    #1321854

    sounds like the delay might be related to WINS/ name resolution on a network;

    is the server a permanent server, or is it a laptop that joins the network occasionally? I've seen times where my laptop's not visible to other machines on our network,even after i've been on for a while.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 9, 2011 at 11:02 am

    #1321861

    its going to be a permanent server... , its actually going to be a production server to replace a "glitchy" production server.. lol

    if it were a wins/dns resolution issue, would that not be removed by removing it from the dns, and hardcoding the mapping of hostname to IP in the HOSTS file? we did that, and yet it still performed slower.

    does sql server 2000 not use the hosts file before checking wins/dns?

  • May 12, 2011 at 6:13 am

    #1323602

    Ive managed to fix the query. seems like it was a local issue on the database box, i opened the client network config utility and disabled the shared memory protocol. after that the query when using the hostname works as fast as the IP. no idea why this is the case.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply