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.

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.

Quote:

---

Originally Posted by PlausiblyDamp

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.

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

Quote:

---

Originally Posted by PlausiblyDamp

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....

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?

Quote:

---

Originally Posted by PlausiblyDamp

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.

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?

Quote:

---

Originally Posted by PlausiblyDamp

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.

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.

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