Lesson Learned #75: The importance of having the connection pooling parameter enabled in your connection string using PHP

This post has been republished via RSS; it originally appeared at: MSDN Blogs.

Hello,

As you know, all connections that are made to Azure SQL Database is using the Gateway before reaching the database, for this reason, nowadays, it is very important to have enable in your code the option of connection pooling.

Previously, we mentioned it, in our YouTube channel about connection pooling impact using C# in these URLs: English and Spanish 

Using open source code and based on this example, I would like to show another example using PHP in this case, also, adding a recomendation disabling the MultipleActiveResultSets as follows.

For example, using the code below, you could see the differences, the first connection is very similar in both executions, but, the rest of the connections took less time when the connection pooling is enabled.

When the connection pooling is 0, the connection time spent are:

Reading data from table
Time1---0:00:00.--0.037018060684204
Reading data from table
Time2---0:00:00.--0.016335010528564
Reading data from table
Time3---0:00:00.--0.01639986038208
Reading data from table
Time4---0:00:00.--0.02184796333313
Reading data from table
Time5---0:00:00.--0.016621828079224
Reading data from table
Time6---0:00:00.--0.02123498916626
Reading data from table
Time7---0:00:00.--0.017816066741943
Reading data from table
Time8---0:00:00.--0.016928911209106
Reading data from table
Time9---0:00:00.--0.017723083496094
Reading data from table
Time10---0:00:00.--0.016327142715454

When the connection pooling is 1, the connection time spent are:

Reading data from table
Time1---0:00:00.--0.038610935211182
Reading data from table
Time2---0:00:00.--0.015377998352051
Reading data from table
Time3---0:00:00.--0.0014491081237793
Reading data from table
Time4---0:00:00.--0.0010840892791748
Reading data from table
Time5---0:00:00.--0.0012030601501465
Reading data from table
Time6---0:00:00.--0.0012838840484619
Reading data from table
Time7---0:00:00.--0.0011310577392578
Reading data from table
Time8---0:00:00.--0.00099420547485352
Reading data from table
Time9---0:00:00.--0.0010828971862793
Reading data from table
Time10---0:00:00.--0.0010440349578857

<?php
for ($i = 1; $i <= 1000; $i++)  {     $starttime = microtime(true);     $serverName = "mysqlserver.database.windows.net"; // update me     $connectionOptions = array(         "Database" => "MyDatabase", // update me
        "Uid" => "myuser", // update me
        "PWD" => "mypassword", 
 "ConnectionPooling" => "1",
        "MultipleActiveResultSets" => "0", 
        "WSID" => "MyTestDesktop"
    );
    //Establishes the connection
    $conn = sqlsrv_connect($serverName, $connectionOptions);
    $tsql= "SELECT 1 as X";
    $getResults= sqlsrv_query($conn, $tsql);
    echo ("Reading data from table" . PHP_EOL);
    if ($getResults == FALSE)
        echo (sqlsrv_errors());
    while ($row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC)) {
     //echo ($row['X'] . PHP_EOL);
    }
    sqlsrv_free_stmt($getResults);
    $endtime = microtime(true);
    $timediff = $endtime - $starttime;
    echo 'Time'.$i.'---'.secondsToTime($timediff).'.--'.($timediff).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);
}
?>

Enjoy!