Results 1 to 11 of 11

Thread: Very Slow SQL Query When Using IP in Connection String

  1. #1

    Thread Starter
    Member
    Join Date
    Feb 2018
    Posts
    56

    Thumbs up Very Slow SQL Query When Using IP in Connection String

    Hello,
    I am having a real big headache with slow SQL query.
    I have two connection string which are as follows :

    ConnectString1 = "Driver={SQL Server};Server=MSSQLSERVER5;Database=SchoolMain;Uid=Admin;Pwd=admin101;"

    and

    ConnectString2 = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=Admin;Password=admin101;Initial Catalog=SchoolMain;Data Source=192.168.1.2,1433"

    Both are connecting to a SQL Database Instance on the same System. The first connects using the Database Instance Name, while second connects using IP Address (Over the Internet on the system) and Port Number.

    Query with ConnectString1 is very first, takes less than 2 seconds to execute while Query with ConnectString2 is extremely very slow and most times comes back with Query Time Out Expired.

    I have searched everywhere on the internet and still cannot find where the issue is. I read about turning off the LLMNR protocol and adding entries in the hosts file to tackle Reverse DNS, followed the steps but its still same as Query with ConnectString2 is still very slow.

    Though when i changed the IP Address in ConnectString2 from 192.168.1.2 to 127.0.0.1, Query works very fast, just exactly as it is with ConnectString1. Is there a way to route all IP address to 127.0.0.1 on the machine ?

    I need ConnectString2 to work Query will be pushed over the IP address from other systems outside the LAN.

    Note : I am using SQL Server 2008

    Please help.

  2. #2
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,458

    Re: Very Slow SQL Query When Using IP in Connection String

    Not sure if those two conenction strings are using the same drive, the second one is using the OLEDB driver and I think the first one is ODBC - that might account for the difference.

    Are both connecting over the internet or only the second one? If only one is connecting over the internet then that might account for the performance difference.

    Also 127.0.0.1 is the localhost address, it is connecting to itself - that will also be a possible source of performance differences.

  3. #3

    Thread Starter
    Member
    Join Date
    Feb 2018
    Posts
    56

    Re: Very Slow SQL Query When Using IP in Connection String

    Quote Originally Posted by PlausiblyDamp View Post
    Not sure if those two conenction strings are using the same drive, the second one is using the OLEDB driver and I think the first one is ODBC - that might account for the difference.

    Are both connecting over the internet or only the second one? If only one is connecting over the internet then that might account for the performance difference.

    Also 127.0.0.1 is the localhost address, it is connecting to itself - that will also be a possible source of performance differences.
    Thanks for the response. Both are connecting to a Dbase instance on same drive, same system. The first one doesn't use the internet, but the Database Instance name directly, while the second one uses the IP address and Instance Port Number.

    I want to make second one work as fast as the first one.

  4. #4
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,458

    Re: Very Slow SQL Query When Using IP in Connection String

    What happens if you make connection string 2 the same as the first connection string, but replace the server name with the IP address?

  5. #5

    Thread Starter
    Member
    Join Date
    Feb 2018
    Posts
    56

    Re: Very Slow SQL Query When Using IP in Connection String

    Quote Originally Posted by PlausiblyDamp View Post
    What happens if you make connection string 2 the same as the first connection string, but replace the server name with the IP address?
    Exactly same thing happens. Query is very slow or says Query Timed Out....

  6. #6
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,458

    Re: Very Slow SQL Query When Using IP in Connection String

    Just to clarify...

    If you are accessing the same server, from the same client, changing nothing but the server name to the server's IP address you get a big difference in performance and also timeouts. Is that correct?

    What operating system are you using on the server and the client?

  7. #7

    Thread Starter
    Member
    Join Date
    Feb 2018
    Posts
    56

    Re: Very Slow SQL Query When Using IP in Connection String

    Quote Originally Posted by PlausiblyDamp View Post
    Just to clarify...

    If you are accessing the same server, from the same client, changing nothing but the server name to the server's IP address you get a big difference in performance and also timeouts. Is that correct?

    What operating system are you using on the server and the client?
    Exactly correct. Same server, same client, same system. I am connecting to the MSSQL server on a machine via exactly same machine. When i use instance name in connection strings, connection and query is fast. When i use IP Address and port number of instance (e.g 192.168.1.2,1433) connection is very slow and time out most times.

    Operating system is Windows 7.

  8. #8
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,458

    Re: Very Slow SQL Query When Using IP in Connection String

    I take it that if you ping the server using either the IP address or Name it works, I am also assuming that if you ping it by name it resolves to the correct IP address.

    IF you follow the instructions at https://www.sqlshack.com/sql-server-...configuration/ which protocols are enabled / disabled?

  9. #9

    Thread Starter
    Member
    Join Date
    Feb 2018
    Posts
    56

    Re: Very Slow SQL Query When Using IP in Connection String

    Quote Originally Posted by PlausiblyDamp View Post
    I take it that if you ping the server using either the IP address or Name it works, I am also assuming that if you ping it by name it resolves to the correct IP address.

    IF you follow the instructions at https://www.sqlshack.com/sql-server-...configuration/ which protocols are enabled / disabled?
    Yes. I can ping the server name and IP address. I just wish to make connections over the Internet, using IP address and Port work as fast as connections using Instance name directly.

    Is there anything i need to do on the network adapter that can work ? Or in the host file ?

    When i ping the IP address : 192.168.1.2, time is < 1ms. But Query using IP address and Instance Port Number is still very very slow and times out.

  10. #10
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,458

    Re: Very Slow SQL Query When Using IP in Connection String

    If you ping the server name does it say the same address?

    Did you look at the link regarding enabled / disabled protocols?

    Also I wouldn't normally recommend having a sql server directly accessible over the Internet anyway... Typically you would want to build some kind of Web based api over the database and make that accessible via the Internet.

  11. #11
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Very Slow SQL Query When Using IP in Connection String

    Check the link that PD gave for the protocols. I think by default names pipes is enabled, but IP isn't. you'll need to turn it on.
    Secondly, is there a reason for wanting to use the IP of the server rather than the named instance? If the reason is so that you can access it from outside the network (ie, through the internet) 1) this is the WRONG way to go about doing it and 2) you're current way is actually using the network internal address, so you're still not accessing it over the internet in this case, but over the network - and if you're having this much problems with it on the internal network, wait until you try to access it from outside the network.

    Anyways, to answer your question as to what is going on, it's like this: in the first connection string, you're requesting a burger from the kitchen directly. You know where to go, no need to leave the house. In the second one, you've also requested a burger, but from the kitchen at a specific address ... so you have to leave the house, look at the address, find the house (same one you just left), then go to the kitchen to get the burger. Using named instances will generally always be preferably and faster than using an IP address.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width