Lesson Learned #243: Enabling ODBC connection pooling in PHP

Microsoft

Oct 26, 2022

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:

<?php 

#print_r(PDO::getAvailableDrivers()); 
#phpinfo();
  $NumberInteractions=1000;
  $timeAvgDiff=0;
  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'] );
    }

    $getResults=null;
    $conn=null;
    $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);
}

?>

Windows

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]
Pooling=Yes

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.8.so.1.1
UsageCount=1
CPTimeout=120

[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.1.so.1.1
UsageCount=1
CPTimeout=120

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