Lesson Learned #243: Enabling ODBC connection pooling in PHP
Published Oct 26 2022 01:26 AM 1,402 Views

Connection Pooling is key for many applications to reduce the latency connecting to Azure SQL Database, in this post, I would like to share my lessons learned using connection pooling in PHP.


The first thing was to write a PHP code to connect to the database using different drivers, we have several to connect to Azure SQL Database, but, I choose ODBC and the driver 18 as a common driver for Windows and Linux/MacOS environments


Also, based on my code, I choose to enable the attribute PDO::SQLSRV_ATTR_DIRECT_QUERY   $conn->setAttribute(constant('PDO::SQLSRV_ATTR_DIRECT_QUERY'), true); because I'm not going to reuse the prepare command in my code just only open the connection, run SELECT 1 and close it. 


Below you could find the source code:




  echo 'Connecting to the database..' . PHP_EOL;
  for ($i = 1; $i <= $NumberInteractions; $i++)

    $starttime = microtime(true);
    #$conn = new pdo("sqlsrv:server=tcp:servername.database.windows.net,1433;Database=DatabaseName;ConnectionPooling=true;LoginTimeout=30;ApplicationIntent=ReadOnly;Encrypt=true;MultipleActiveResultSets=false;APP=Test Latency Connection", "username@servername","Password");
    $conn = new pdo("odbc:DRIVER={ODBC Driver 18 for SQL Server};server=tcp:servername.database.windows.net,1433;Database=DatabaseName;ConnectionPooling=true;LoginTimeout=30;ApplicationIntent=ReadOnly;MultipleActiveResultSets=false;APP=Test Latency Connection", "username@servername","Password");
    $conn->setAttribute(constant('PDO::SQLSRV_ATTR_DIRECT_QUERY'), true);  
    $conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );  
    $getResults= $conn->query("SELECT 1 as X");
    echo 'Time '.$i. ' Reading data from table - Value:' ;
    if ($getResults == FALSE)
        echo (sqlsrv_errors());
    while ($row = $getResults->fetch(PDO::FETCH_ASSOC)) {
      echo ($row['X'] );

    $endtime = microtime(true);
    $timediff = $endtime - $starttime;
    $timeAvgDiff = $timeAvgDiff+$timediff;

    echo ' (Time): '.($timediff).PHP_EOL;

echo '---------------------------------------------'.PHP_EOL;
echo ' Total Time connection + execution time: '.($timeAvgDiff).PHP_EOL;
echo ' Average connection + execution time: '.(($timeAvgDiff/$NumberInteractions)).PHP_EOL;
echo ' Total number of connections:         '.($NumberInteractions).PHP_EOL;
echo '---------------------------------------------'.PHP_EOL;

function secondsToTime($s)
    $h = floor($s / 3600);
    $s -= $h * 3600;
    $m = floor($s / 60);
    $s -= $m * 60;
    return $h.':'.sprintf('%02d', $m).':'.sprintf('%02d', $s);





Basically, Using ODBC Data Source Administrator for 64 Bits, I need to modify the setting to enable connection pooling




Once I have this, running the application connecting 1000 times, running a SELECT 1 every time and I got the following time spent:




In Linux (ubuntu 20.04)


First, I need to modify the file odbcinst.ini located on /etc/odbcinst.ini to enable connection pooling.




[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server


[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server


Second, running the same PHP code in Linux environment the time spent was:




So, the time spent on both environments were pretty much the same using connection pooling. 


Besides that, disabling the connection pooling, I would like to pay attention that connection pooling will help when we stablish more than a connection. Remember that the first connection always will take more time due to several operations needed to cache it. 


Also, if you want to add a retry logic per module in your code, you could find here additional information about it - AzureSQLConnectivityChecker/PHP at master · JMNetwalker/AzureSQLConnectivityChecker · GitHub






Version history
Last update:
‎Oct 26 2022 01:32 AM
Updated by: