Monthly Archives: October 2010

Quality assurance ideas in agile developement

Nowadays being agile is cool. All people is speaking about Scrum and agile development. There’s even people going far away and speak about Kanban. Agile development isn’t a new idea. Maybe it’s only setting up a name to an old one.Quality assurance activities should be designed to identify missing, ineffective or inefficient policies, processes and procedures used in the project. According to the PMBOK guide there’s a tool to perform quality audits called root analysis. Basically root analysis aims to find the source and the solution of the problem and helps to determine inefficiencies. Performed it regularly you can fine tune processes. The steps to perform root analysis technique: 

  • Define the problem
  • Gather data to describe the problem
  • Determine possible causes
  • Select the root cause
  • Develop a solution strategy
  • Test and evaluate the solution

Clients must bear in mind agile development means they must take actively part within the development process. Maybe some clients don’t like this way of work. Maybe they prefer to pay for the service or deliverable and let the work for us. But really it worth the effort. Work close to the client is one of the principles of agile development. One important action to obtain the commitment between both parts is give access to the customer to the Bug Tracker. With this simple step our customer can track our work. But there’s a problem. Our bug tracker is not only an internal development tool now. It has become also into a communication tool. That’s means developers maybe decide don’t report internal technical things because they don’t want to be read by the clients.

Bug trackers can help us in our agile developement. There’s a lot of bug tracking software over there. Open source software you can install in your host, such as Mantis or Track. You can find also hosted solutions, free or paid ones. Probably Jira is the most famous and widely used software(not a free software).

Currently I’m thinking about the bug life cycle. I’m trying to standardize the way we use to face the bugs. I’ve created the following flowchart:

 

What do you think about this flow chart? Do You think is it is up to your requirements?

Speed up PHP scripts with asynchronous database queries

That’s the situation. A web application with 4 heavy queries. Yes I know you can use a cache or things like that but imagine you must perform the four queries yes or yes. As I say before the four database queries are heavy ones. 2 seconds per one. Do it sequentially, that’s means our script will use at least 8 seconds (without adding the extra PHP time). Eight seconds are a huge time in a page load.

So here I will show up a technique to speed up our website using asynchronous calls.

One possible solution is fork each query into a separate thread but, does PHP support threads? The short is answer No. OK that’s not 100% true. We can use PCNTL functions but it’s a bit tricky for me to use them. I prefer to use this another solution to create fork and perform asynchronous operations.

The basic idea is isolate each query into a separate script.

// fork.php
$dbh = new PDO('pgsql:dbname=pg1;host=localhost', 'nov', 'nov');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$t = filter_input(INPUT_GET, 't', FILTER_SANITIZE_STRING);
switch ($t) {
    case 0;
    case 1;
    case 2;
       $sql = "select * from test.tbl1";
       break;
}
$stmt = $dbh->prepare($sql);
$stmt->execute();
$data = $stmt->fetchAll();
echo json_encode($data);

And now we are going to use the curl‘s multi functions to execute the database queries at the same time:

// index.php
class Fork
{
    private $_handles = array();
    private $_mh      = array();

    function __construct()
    {
        $this->_mh = curl_multi_init();
    }

    function add($url)
    {
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, $url);
        curl_setopt($ch, CURLOPT_HEADER, 0);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($ch, CURLOPT_TIMEOUT, 30);
        curl_multi_add_handle($this->_mh, $ch);
        $this->_handles[] = $ch;
        return $this;
    }

    function run()
    {
        $running=null;
        do {
            curl_multi_exec($this->_mh, $running);
            usleep (250000);
        } while ($running > 0);
        for($i=0; $i < count($this->_handles); $i++) {
            $out = curl_multi_getcontent($this->_handles[$i]);
            $data[$i] = json_decode($out);
            curl_multi_remove_handle($this->_mh, $this->_handles[$i]);
        }
        curl_multi_close($this->_mh);
        return $data;
    }
}

$fork = new Fork;

$output = $fork->add("http://localhost/fork.php?t=0")
    ->add("http://localhost/fork.php?t=1")
    ->add("http://localhost/fork.php?t=2")
    ->run();

echo "<pre>";
print_r($output);
echo "</pre>";

And that’s all. Four queries will be executed at the same time and our scrip will use 2 seconds (the slowest query) instead of  8 seconds. But there’s a little inconvenient within this solution. We need to open 4  connections instead of one because of the lack of connection pooling in PHP. Another problem with this technique appears if we need to debug our script. The debugger won’t debug our “forks” at the same time (we will need to debug then separately). Because of that we need to balance the usage of this technique or not, but it’s another weapon we have to improve our scripts.

Performance analysis using bind parameters with PDO and PHP.

Some months ago a work mate asked me for the differences between using bind variables versus executing the SQL statement directly as a string throughout a PDO connection.

Basically the work-flow of almost all database drivers is the same: Prepare statement, execute and fetch results. We have the following small example with a simple  update

$dbh = new PDO('pgsql:dbname=pg1;host=localhost', 'user', 'pass');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->beginTransaction();
$stmt = $dbh->prepare("UPDATE test.tbl1 set field1=:F1 where id=1");
$stmt->execute(array('F1' => $field1));
$dbh->commit();

And we also can get the same result with the following code:

$dbh = new PDO('pgsql:dbname=pg1;host=localhost', 'user', 'pass');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->beginTransaction();
$dbh->prepare("UPDATE test.tbl1 set field1='{$field1}' where id=1")->execute();
$dbh->commit();

What’s the best one? Both method work properly. The difference is how databases manage the operation internally. When we prepare a statement we are compiling the string into the database within the current connection. After that we can execute the statement and if our statement is ready to receive parameters we can bind those parameters with PHP values or variables. With this idea in mind we can guess that if we need to perform several executions of the same prepared statement is better to use bind parameters instead of compile and execute directly the string.
b
I have created a simple benchmark to show it.

<?php
error_reporting(-1);
function microtime_float()
{
   list($usec, $sec) = explode(" ", microtime());
   return ((float)$usec + (float)$sec);
}

$dbh = new PDO('pgsql:dbname=pg1;host=localhost', 'user', 'pass');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$time_start = microtime_float();
$dbh->beginTransaction();
$field1 = uniqid();
$stmt = $dbh->prepare("UPDATE test.tbl1 set field1=:F1 where id=1");
$stmt->execute(array('F1' => $field1));
$dbh->commit();
$time_end = microtime_float();
$time = round($time_end - $time_start, 4);
echo "<p>Single UPDATE with bind parameters: $time seconds</p>";

$time_start = microtime_float();
$dbh->beginTransaction();
$field1 = uniqid();
$stmt = $dbh->prepare("UPDATE test.tbl1 set field1='{$field1}' where id=1");
$stmt->execute();
$dbh->commit();
$time_end = microtime_float();
$time = round($time_end - $time_start, 4);
echo "<p>Single UPDATE without bind parameters: $time seconds</p>";

$time_start = microtime_float();
$dbh->beginTransaction();
$stmt = $dbh->prepare('UPDATE test.tbl1 set field1=:F1 where id=1');
foreach (range(1, 5000, 1) as $i) {
   $field1 = $i;
   $stmt->execute(array('F1' => $field1));
}
$dbh->commit();
$time_end = microtime_float();
$time = round($time_end - $time_start, 4);
echo "<p>Multiple UPDATE with bind parameters: $time seconds</p>";

$time_start = microtime_float();
$dbh->beginTransaction();
foreach (range(1, 5000, 1) as $i) {
	$stmt = $dbh->prepare("UPDATE test.tbl1 set field1='{$field1}' where id=1");
   $field1 = $i;
   $stmt->execute();
}
$dbh->commit();
$time_end = microtime_float();
$time = round($time_end - $time_start, 4);
echo "<p>Multiple UPDATE without bind parameters: $time seconds</p>";

The output of this benchmark is the following one:

Single UPDATE with bind parameters: 0.2623 seconds
Single UPDATE without bind parameters: 0.0195 seconds
Multiple UPDATE with bind parameters: 4.1123 seconds
Multiple UPDATE without bind parameters: 8.1732 seconds

As we can see in the output of the benchmark a single update is slightly faster without bind any parameter but if we need to execute the same update with different parameters the bind parameters way is significantly faster than parse+execute again and again.

There’s another benefit of using bind parameters. Databases normally have internally a cache system for our prepared statement. In theory they reuse the statements. The main problem for us (in our PHP world) is that normally we create a new connection to the database at the beginning of the script (or lazy connection) and close it at the end. We don’t have natively a connection pooling like J2EE. So I’m not 100% sure if using bind parameters helps to the database to reuse statements.

There’s another issue we must take into account. Without prepared statements the multiple update example may throw a database exception (depend on our database configuration). Too many open cursors in active transaction. That error appear because for our database each update is a different statement (5000 in the example) and with the another method there’s only one.

Follow

Get every new post delivered to your Inbox.

Join 989 other followers