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

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
http://blog.stevedoria.net/

gharmel wrote:[color=blue]
> 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?
>[/color]

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
Analyser?

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.