Category: josephscott

Categories
josephscott

PHP Database Functions vs. PEAR::DB vs. ADOdb (and PDO)

I started putting together a few small PHP scripts to move data around. I’ve been using PEAR::DB a fair bit for this sort of thing, but I figured there wasn’t really much reason for it in this case since I knew these were only going to be talking to specific databases and wouldn’t be ported for anything else. This got me to wondering what sort of performance penalty I was paying for using PEAR::DB instead of the specific PHP functions. This lead to a few simple test scripts against a PostgreSQL database.

The first script uses the PHP PostgreSQL functions directly:

ini_set("display_errors", 1);
$time_start = microtime(true);
$db = pg_connect("host=dbserver dbname=testdb user=test password=testpw");

$runs = 250;
for($i = 0; $i = '25-MAR-06'";
    $rs = pg_query($db, $sql);
    while($row = pg_fetch_assoc($rs)) { }
}

$time_end = microtime(true);
$time = $time_end - $time_start;
print("TIME: {$time}n");

Nothing too exciting, just run the same query 250 times and ditch the results. This query happens to return 273 rows against my test database. I ran this script from the command line 10 times. The fastest time for this script was 3.6526100635529 seconds. Normally I’d include error checks in these, but I was only interested in how fast I could get through data. The second script made use of PEAR::DB:

ini_set("display_errors", 1);
require_once("PEAR.php");
require_once("DB.php");
$time_start = microtime(true);

$db = DB::Connect("pgsql://test:testpw@dbserver/testdb");
$runs = 250;
for($i = 0; $i = '25-MAR-06'";
    $rs = $db->query($sql);
    while($row = $rs->fetchRow()) { }
}

$time_end = microtime(true);
$time = $time_end - $time_start;
print("TIME: {$time}n");

Same conditions, run 10 times from the command line with the same 273 rows returned from the query. The fastest time was 6.5583028793335 seconds. So PEAR::DB added an additional 2.90569281578 seconds to the process, an increase of almost 80%.

Just add more data to the mix I put together another script using ADOdb:

ini_set("display_errors", 1);
require_once("./adodb/adodb.inc.php");
$time_start = microtime(true);

$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
$db = NewADOConnection("postgres://test:testpw@dbserver/testdb");
$runs = 250;
for($i = 0; $i = '25-MAR-06'";
    $rs = $db->Execute($sql);
    while(!$rs->EOF) { $rs->MoveNext(); }
}

$time_end = microtime(true);
$time = $time_end - $time_start;
print("TIME: {$time}n");

Once again 10 runs from the command line with 273 rows being returned from the query. The fastest time was 5.3411331176758 seconds. Faster than PEAR::DB by more than a second, but still well behind the script that used the direct database functions by 1.68852305412 seconds. This is about 46% slower compared to PEAR::DB’s almost 80%.

I should note that I tried to include PEAR::MDB2 in the test but it kept giving me reference errors. I modified my test script to deal with a couple of them, but gave up after more of them showed up.

What does all this mean? Here is what I take from it:

  1. If you need performance over supporting multiple databases and use of PEAR packages that require PEAR::DB (like PEAR::DB_DataObject) then stick with calling the database functions directly. No way to beat that from a performance perspective.
  2. If you need to support multiple databases and don’t have to worry about other PEAR packages then go with ADOdb. It is faster than PEAR:DB good margin.
  3. If you are using other PEAR packages that require PEAR::DB then go with that.

This wasn’t intended to be an exhaustive test, I just wanted to get a feeling for the relative speed of these three options. For reference I used PHP 5.1.2 on a FreeBSD 6 system. If you run a similar comparison be sure to look at the relative differences between the times, not the times themselves.

UPDATE Tue 4 Apr 2006 @ 4:30pm : Philip Hofstetter pointed out that I should have included the new PDO functions in my test. So here it is:

ini_set("display_errors", 1);
$time_start = microtime(true);
$db = new PDO("pgsql:host=dbserver dbname=testdb user=test password=testpw");

$runs = 250;
for($i = 0; $i = '25-MAR-06'";
    foreach($db->query($sql) as $row) { }
}

$time_end = microtime(true);
$time = $time_end - $time_start;
print("TIME: {$time}n");

Once again 10 runs from the command line, with the query returning 273 rows. The fastest time was 4.287976026535 seconds. So PDO comes in at number two, beating ADOdb by more than a second. But still behind direct function calls by more than six tenths of a second, adding only 17% over head. PDO will only work with PHP 5, so if you want/need your app to work with PHP 4 then don’t even include PDO on your list of options.

UPDATE Tue 11 Apr 2006 @ 12:10am : Deane over at Gadgetopia.com wanted to know how the ADOdb extension faired in this test. So here we are one more time 🙂

After installing the ADOdb extension I ran the same exact test script for ADOdb 10 times from the command line with the same query returning 273 rows. The unmodified script returned times that were very close to the numbers with ADOdb without the extension. The fastest time was 5.3139600753784 seconds, only 0.027173 seconds faster than plain ADOdb. To test out the speed of the extension I made one change to the test script, using adodb_movenext($rs); instead of $rs->MoveNext(); in the while loop:

ini_set("display_errors", 1);
require_once("./adodb/adodb.inc.php");
$time_start = microtime(true);

$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
$db = NewADOConnection("postgres://test:testpw@dbserver/testdb");

$runs = 250;
for($i = 0; $i = '25-MAR-06'";
    $rs = $db->Execute($sql);

    while(!$rs->EOF) {
#       $rs->MoveNext();
        adodb_movenext($rs);
    }
}
$time_end = microtime(true);
$time = $time_end - $time_start;
print("TIME: {$time}n");

Another 10 runs from the command line and the fastest time was 4.2841749191284 seconds. This is 1.0569582 seconds faster than ADOdb without the extension. This is ever so slightly faster than PDO, beating it out by 0.0038011. Calling the direct database functions are still 0.6315648 seconds faster than ADOdb + ADOdb-extension.

To make things easier here is a table of the results. Remember that the focus is on the relative differences, not the specific times, which will vary with different hardware/OS/etc. All times are given in seconds.

Library/Function Time Absolute Difference
Direct database functions 3.6526100635529 0
ADOdb + ADOdb/extension 4.2841749191284 0.6315648
PDO 4.287976026535 0.6353659
ADOdb (w/o extension) 5.3411331176758 1.688523
PEAR::DB 6.5583028793335 2.9056928

Using the specific database functions still wins, I suspect they always will. Interesting that PDO didn’t beat out ADOdb + ADOdb/extension, although the times are so close that it is probably a wash.