MSSQL connection from PHP slower when server is remote (crosspost)

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
I'm trying to get some clues on why I get (much) slower responses from
my PHP applications when dealing with a remote sql server as opposed to
a local sql server.

Here's my situation:

Server #1
MSSQL 2000 sp4
Windows 2000 Server
PHP4 on IIS5 (CGI)

Server #2
MSSQL 2000 sp4
Windows 2003 Server
PHP4 on IIS6 (CGI)

Server #1 and #2 are on the same local network and the same switch. We
use TCP/IP as the connection protocol and our connections use IPs
instead of domain names.

Server#1PHP --> Server#2 MSSQL = slow
Server#2PHP --> Server#1 MSSQL = slow
Server#1PHP --> Server#1 MSSQL = fast
Server#2PHP --> Server#2 MSSQL = fast

This slower behavior is most noticable when many stored procedures
(10-15) are being executed on one php page. When the database is local
on a page such as this, the response time is around 2 seconds. When the
sql server is remote, the response time is between 8 and 15 seconds.

Does this make sense at all? Does anyone have any ideas of what the
bottleneck could be?

Re: MSSQL connection from PHP slower when server is remote (crosspost)

I am going to guess that it is caused by network latency. You may want  
to run a local sql server that acts as a cache or buffer for that  
script, especially if it is used intensively. Of course, you'll also  
need to figure out how to deal with cache and buffer issues. I used to  
overlook a site that dependend on a remote sql server. It was cool to  
administer a "distributed" system, but it was not necessary and it cost  
us connection timeouts. We ended up just putting the site and sql server  
on the same network.

Steve Doria

gharmel wrote:
Quoted text here. Click to load it

Re: MSSQL connection from PHP slower when server is remote (crosspost)

How are you connecting to the database, through the mssql extension or
through ADO? Is the delay from making the connection, or in retrieving
the result? What happens when you run the same query through Query

It doesn't make sense. I've used MSSQL over a WAN before and have never
seen delays that long.

Re: MSSQL connection from PHP slower when server is remote (crosspost)

After more investigation it seems that every query had a connect and
disconnect. The act of connecting/disconnecting seems to be much more
expensive when the SQL server is not local.

Site Timeline