Excessive socket usage using MS-SQL server via ADO COMobject

[Cornald Kruyt]

Hi,

I've an database import script written in PHP which used ADO via COM.
It makes thousands of queries to a MS-SQL server. The problem is that
the process runs out of sockets.
The MSSQL server is used in the following way:

// In the beginning of the script, the connection is set up
$dbc=new COM("ADODB.Conn ection");
$dbc->ConnectionStri ng="Provider=sq loledb;Network
Library=DBMSSOC N;Data Source=FOO;Data base=BAR;User ID=FOO;Password =BAR";
$dbc->Open();

// Typical query
$rs=$dbc->execute("SELEC T ... FROM ...");
// process the result set here

// Close and free the resultset object:
$rs->Close();
unset($rs);

The script executes many queries in rapid succession. When I inspect
the output of 'netstat -an' during execution, I see thousands of open
sockets:

...
TCP 192.168.1.210:3 960 192.168.1.13:14 33 TIME_WAIT
TCP 192.168.1.210:3 961 192.168.1.13:14 33 TIME_WAIT
TCP 192.168.1.210:3 964 192.168.1.13:14 33 TIME_WAIT
....

Sometimes the script even dies with a COM exception error, that it can't
make a connection to the MSSQL server anymore. This happens when +/-
4000 sockets are in the TIME_WAIT state. After two minutes these
sockets are automatically closed by Windows and MSSQL connections are
possible again.

Is this a known problem? I couldn't find it in Google.

is there a way to make ADO (or PHP) reuse the existing socket, instead
of opening a new one for each Execute?

Greetings,

[Sean]

Re: Excessive socket usage using MS-SQL server via ADO COMobject

I am not sure how helpful this will be (probably not very) but if you
are using the ADOdb Library for PHP try $dbc->PConnect() instead of
$dbc->Open(). This should open a persistent connection instead of a new
connection each time.

[Cornald Kruyt]

Re: Excessive socket usage using MS-SQL server via ADO COMobject

"Sean" <oreilly.sean@g mail.com> wrote:
[color=blue]
> I am not sure how helpful this will be (probably not very) but if you
> are using the ADOdb Library for PHP try $dbc->PConnect() instead of
> $dbc->Open(). This should open a persistent connection instead of a new
> connection each time.[/color]

I am not using the ADOdb library, but thanks for the tip anyway: I will
look into the source code of ADOdb to see how PConnect() is implemented.
Maybe that will give some insight.

What do they mean by 'persistent connection'? That the connection is
kept alive between script invocations? Or that multiple queries done
from the same script invocation reuse the existing MSSQL socket?

Greetings,