SQL Server Driver for PHP Connection Options: Connection Pooling

I’m back in from conferences in Germany (PHP World Kongress and TechEd) and am now at Jump In! Camp. I’ll echo what I seems to be a common sentiment after the first couple of days: it’s very cool to be surrounded by so many smart folks (and humbling too). It’s been fun having my knowledge of PHP, IIS, and SQL Server tested by folks asking questions that are real problems in real apps.  (Hopefully, I’m passing the tests.) One topic that came up yesterday was connection pooling and how much difference using pooled connections makes in app performance, so that’s what I’ll look at in this post. But first, a little background…

 

What is connection pooling?

I’ll borrow from the Wikipedia explanation here, as I think this is a good high-level explanation:

In software engineering, a connection pool is a cache of database connections maintained so that the connections can be reused when future requests to the database are required. Connection pools are used to enhance the performance of executing commands on a database. Opening and maintaining a database connection for each user, especially requests made to a dynamic database-driven website application, is costly and wastes resources. In connection pooling, after a connection is created, it is placed in the pool and it is used over again so that a new connection does not have to be established. If all the connections are being used, a new connection is made and is added to the pool. Connection pooling also cuts down on the amount of time a user must wait to establish a connection to the database.

The question that this explanation begs is, “What happens to a connection when it is returned to the pool?” The answer to that question lies in the architecture of the SQLSRV and PDO_SQLSRV drivers. Because both the SQLSRV and PDO_SQLSRV drivers are thin wrappers of SQL Server’s ODBC implementation (called SQL Server Native Client), they both can surface the connection pooling that is built into ODBC. When a connection is returned to the connection pool, sp_reset_connection is called. The best explanation of what this stored procedure does is on Stackoverflow: http://stackoverflow.com/questions/596365/what-does-spresetconnection-do.

 

How do I enable/disable connection pooling?

In both the SQLSRV and PDO_SQLSRV drivers, connection pooling is enabled by default. In other words, if you don’t turn off connection pooling, when you open a connection to SQL Server you are using a connection from the connection pool if an equivalent connection is available. If you want to disable connection pooling, you can set the “ConnectionOptions” setting to false (or 0) in the options you pass to sqlsrv_connect (for the SQLSRV driver)…

$server = 'serverName\sqlexpress';
$options = array("Database"=>"master", "UID"=>"user", "PWD"=>"password", "ConnectionPooling"=>0);
$conn = sqlsrv_connect($server, $options);

…or in the options you supply when creating a new PDO object (for the PDO_SQLSRV driver):

$server = 'serverName\sqlexpress';
$options = array("ConnectionPooling"=>0);
$conn = new PDO( "sqlsrv:Server=$server;Database=master", "user", "password", $options);

For most internet applications, you would most likely want to leave connection pooling enabled. In fact, I’ve struggled to think of a scenario in which you wouldn’t want it enabled (especially given the performance benefits…see below). I’d be very interested in hearing about situations where there are benefits to disabling connection pooling.

 

How long does a connection stay in the connection pool?

By default, connections remain in the connection pool for 60 seconds. You can change this value by using the ODBC Data Source Administrator. On the Connection Pooling tab, double click the driver name (SQL Server Native Client) and supply the value you want:

image

 

How much difference does connection pooling make?

To test how much difference connection pooling makes, I ran the scrip below as is, and then ran it with connection pooling on (“ConnectionPooling”=>1). On my dev machine, it took anywhere between 1.5 and 2.0 seconds to complete with connection pooling off and less than 0.03 seconds with connection pooling on (your mileage may vary).

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

$start_time = microtime_float();
for($i = 0; $i<=999; $i++)
{
    $server = 'bswanlaptop\sqlexpress';
    $options = array("Database"=>"master", "UID"=>"sa", "PWD"=>"pass@word1", "ConnectionPooling"=>0);
    $conn = sqlsrv_connect($server, $options);
    sqlsrv_close($conn);
}
$end_time = microtime_float();
$time = round($end_time - $start_time, 4);
echo $time;

That’s it for today.

Thanks.

-Brian

No Comments