[PHP-WIN] Slow MSSQL access when using TCP/IP

[Jah Raphael]

Hello! :)

I've run into a problem where connecting to our MSSQL Server from our
webserver using a TCP/IP connection is a -whole- lot slower than 
connecting to the same server using a Named Pipes connection (we're 
talking 10 to 20 times slower).

We are running SQL server on a non-standard port (1533 vs 1433). I
do believe we had tried running it on port 1433 and still had the
slowdown though.

In the Client Configuration utility on the WEB server I use the following 
settings:

Server Alias: mssql_named_pipes
Network Library: Named Pipes
Connection parameters: \\sqlserver\pipe\sql\query

Server Alias: mssql_tcpip
Network Library: TCP/IP
Connection parameters: 192.168.1.7,1533

I want to make sure to point out that everything -does- operate fine when
using the TCP/IP connection - it's just extremely slow.

We've tried connecting to other MSSQL servers and ran into the same problems 
where it will run fast when using Named Pipes, but slow when using TCP/IP.

Here is are 3 benchmarks of just the mssql_connect() between Named Pipes and 
TCP/IP versions so you can see how much slower it is. This is a sample of running
it three times (I've run this from the web and from command line and gotten the
same results both times):

Named Pipes took 13.3231ms
TCP/IP took 205.6559ms

Named Pipes took 12.3510ms
TCP/IP took 174.2430ms

Named Pipes took 13.2550ms
TCP/IP took 149.0561ms

Here's the code that generated the above:

-- snip snip --

function DoBenchmark( $name, $server, $numtimes )
{
 $start_time = getmicrotime();
 for ( $i = 0; $i < $numtimes; ++$i ) {
  $link_id = mssql_connect( $server, DATABASE_USER, DATABASE_PASSWORD );
  if ( !$link_id )
   exit;
  mssql_close( $link_id );
 }
 $total_ms = ( getmicrotime() - $start_time ) * 1000;
 printf( "$name took %.4fms<br>", $total_ms );
}

function getmicrotime() 
{
 list( $usec, $sec ) = explode( " " , microtime() );
 return (float)$usec + (float)$sec;
}

DoBenchmark( "Named Pipes", "mssql_named_pipes", $runs );
DoBenchmark( "TCP/IP",      "mssql_tcpip",       $runs );

-- snip snip --

Finally, here's the web server and sql server information:

SQL Server OS: Win2K Server SP1
SQL Version: MSSQL 7.0

WEB Server OS: Win2K Server SP2
WEB Server PHP: PHP 4.0.6 w/ latest extensions
WEB Server: IIS 5.0

We are using PHP in CGI mode.

The PHP.exe is compiled in-house, however there are no MSSQL related 
changes in our version.

Here are the results of "php -m":

Running PHP 4.0.6
Zend Engine v1.0.6, Copyright (c) 1998-2001 Zend Technologies
    with Zend Optimizer v1.1.0, Copyright (c) 1998-2000, by Zend Technologies

[PHP Modules]
standard
bcmath
Calendar
com
variant
ftp
mysql
odbc
pcre
session
xml
wddx
gd
mssql
Zend Optimizer

[Zend Modules]
Zend Optimizer

--

>From what I've been reading it sounds like there are a good number
of people who successfully use TCP/IP connections to MSSQL, so it
sounds like I may just be doing something wrong - I just don't know
what.

I hope that explains the problem. All I want is to be able to connect
to the MSSQL server using TCP/IP and not suffer the slowdowns we're
currently suffering :)

Thanks in advance for any help.

-Jah