Site icon TheWindowsUpdate.com

Lesson Learned #117: Testing the connection time using Azure Database for MySQL and PHP

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

Today, I worked on a very interesting service request that our customer noticed some milliseconds of difference connecting a MySQL local environment and Azure Database for MySQL using PHP. 

 

The first point that we need to know that these milliseconds of difference is expected because when you are connecting to Azure Database for MySQL all connections will be made against a proxy server as you could see in this URL

 

For this reason, we have some best practices and recomendations that my colleague Bashar reported

 

In this article just wanted to share the two examples that how to show you measure the results: 

 

In Summary

 

Results running on Azure

 

Test using PDO:

 

 

<?php

for ($i = 1; $i <= 1000; $i++)

 {

    $starttime = microtime(true);

    $conn = new pdo("mysql:host=servername.mysql.database.azure.com;dbname=databasename", "username@servername","password", array(

    PDO::ATTR_PERSISTENT => true));

    $tsql= "SELECT 1 as X";

    $getResults= $conn->query($tsql);

    echo ("Reading data from table" . PHP_EOL);

    if ($getResults == FALSE)

        echo (sqlsrv_errors());

    while ($row = $getResults->fetch(PDO::FETCH_ASSOC)) {

      echo ($row['X'] . PHP_EOL);

    }

    $getResults=null;

    $conn=null;

    $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);

}

?>

 

 

<?php

for ($i = 1; $i <= 1000; $i++)

 {

    $starttime = microtime(true);

    $conn = new pdo("mysql:host=servername.mysql.database.azure.com;dbname=databasename", "username@servername","password", array(

    PDO::ATTR_PERSISTENT => false));

    $tsql= "SELECT 1 as X";

    $getResults= $conn->query($tsql);

    echo ("Reading data from table" . PHP_EOL);

    if ($getResults == FALSE)

        echo (sqlsrv_errors());

    while ($row = $getResults->fetch(PDO::FETCH_ASSOC)) {

      echo ($row['X'] . PHP_EOL);

    }

    $getResults=null;

    $conn=null;

    $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);

}

?>

 

Test using mySQLcli I got the same results using connection pooling/persistent connections:

 

 

<?php

for ($i = 1; $i <= 1000; $i++)

 {

    $starttime = microtime(true);

    //Establishes the connection

    $conn = new mysqli("p:servername.mysql.database.azure.com", "username@servername","password","databasename");

    $tsql= "SELECT 1 as X";

    $getResults= $conn->query($tsql);

    echo ("Reading data from table" . PHP_EOL);

    if ($getResults == FALSE)

        echo (sqlsrv_errors());

    while ($row = $getResults->fetch_assoc()) {

      echo ($row['X'] . PHP_EOL);

    }

    $getResults->free();

    $conn->close();

    $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);

}

?>

 

 

<?php

for ($i = 1; $i <= 1000; $i++)

 {

    $starttime = microtime(true);

    //Establishes the connection

    $conn = new mysqli("servername.mysql.database.azure.com", "username@servername","password","databasename");

    $tsql= "SELECT 1 as X";

    $getResults= $conn->query($tsql);

    echo ("Reading data from table" . PHP_EOL);

    if ($getResults == FALSE)

        echo (sqlsrv_errors());

    while ($row = $getResults->fetch_assoc()) {

      echo ($row['X'] . PHP_EOL);

    }

    $getResults->free();

    $conn->close();

    $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);

}

?>

 

Results running on Local Environment

 

Test using PDO:

 

 

<?php

for ($i = 1; $i <= 1000; $i++)

 {

    $starttime = microtime(true);

    $conn = new pdo("mysql:host=localhost;dbname=databasename", "username","password", array(

    PDO::ATTR_PERSISTENT => false));

    $tsql= "SELECT 1 as X";

    $getResults= $conn->query($tsql);

    echo ("Reading data from table" . PHP_EOL);

    if ($getResults == FALSE)

        echo (sqlsrv_errors());

    while ($row = $getResults->fetch(PDO::FETCH_ASSOC)) {

      echo ($row['X'] . PHP_EOL);

    }

    $getResults=null;

    $conn=null;

    $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);

}

?>

 

 

<?php

for ($i = 1; $i <= 1000; $i++)

 {

    $starttime = microtime(true);

    $conn = new pdo("mysql:host=localhost;dbname=databasename", "username","password", array(

    PDO::ATTR_PERSISTENT => true));

    $tsql= "SELECT 1 as X";

    $getResults= $conn->query($tsql);

    echo ("Reading data from table" . PHP_EOL);

    if ($getResults == FALSE)

        echo (sqlsrv_errors());

    while ($row = $getResults->fetch(PDO::FETCH_ASSOC)) {

      echo ($row['X'] . PHP_EOL);

    }

    $getResults=null;

    $conn=null;

    $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);

}

?>

 

If you need more information you could visit this URL and script to have more details about other tests done.

 

Enjoy!

Exit mobile version