Koshigoe Hiki  Index  Search  Changes  RSS  Login

[PHP]DBライブラリのパフォーマンスチェック

概要

PHPMySQLした場合の、利用ライブラリによるパフォーマンスの違いを見てみます。

PEAR::DB版

スクリプト

test_db_pear.php

<?php

require_once "DB.php";

$dbuser = "root";
$dbpass = "";
$dbhost = "localhost";
$dbname = "test";

$sql = array(
             "create" => "create table php_db_test_pear (id int, name text);",
             "insert" => "insert into php_db_test_pear(id, name) value(?, ?);",
             "update" => "update php_db_test_pear set name=concat(name, '_updated');",
             "delete" => "delete from php_db_test_pear;",
             "drop"   => "drop table php_db_test_pear;",
             );

$db = DB::connect("mysql://$dbuser:$dbpass@$dbhost/$dbname");
check($db);

$clock_start  = time();
$memory_start = memory_get_usage();

$res = $db->query($sql["create"]);
check($res);

$stmt = $db->prepare($sql["insert"]);
for ($i = 0; $i < 10000; $i++) {
    $res  = $db->execute($stmt, array($i, "hoge" . $i));
    check($res);
}

$res = $db->query($sql["update"]);
check($res);

$res = $db->query($sql["delete"]);
check($res);

$res = $db->query($sql["drop"]);
check($res);

$time   = time() - $clock_start;
$memory = number_format(memory_get_usage() - $memory_start);

echo "Time: $time?n";
echo " Mem: $memory?n";


function check($res) {
    if (PEAR::isError($res)) {
        die($res->getMessage());
    }
}

?>

結果

% php test_db_pear.php
Time: 12
 Mem: 22,976

PDO版

スクリプト

test_db_pdo.php

<?php

$dbuser = "root";
$dbpass = "";
$dbhost = "localhost";
$dbname = "test";

$sql = array(
             "create" => "create table php_db_test_pdo (id int, name text);",
             "insert" => "insert into php_db_test_pdo(id, name) value(:id, :name);",
             "update" => "update php_db_test_pdo set name=concat(name, '_updated');",
             "delete" => "delete from php_db_test_pdo;",
             "drop"   => "drop table php_db_test_pdo;",
             );

try {
    $db = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);

    $clock_start  = time();
    $memory_start = memory_get_usage();

    $db->exec($sql["create"]);

    $stmt = $db->prepare($sql["insert"]);
    for ($i = 0; $i < 10000; $i++) {
        $stmt->bindParam(':id',   $id);
        $stmt->bindParam(':name', $name);
        $id   = $i;
        $name = "hoge" . $i;
        $stmt->execute();
    }

    $db->exec($sql["update"]);

    $db->exec($sql["delete"]);

    $db->exec($sql["drop"]);

    $time   = time() - $clock_start;
    $memory = number_format(memory_get_usage() - $memory_start);

    echo "Time: $time?n";
    echo " Mem: $memory?n";
} catch (PDOException $e) {
    die($e->getMessage());
}


?>

結果

% php test_db_pdo.php
Time: 4
 Mem: 1,656

PEAR::MDB2

スクリプト

<?php

require_once "MDB2.php";

$dbuser = "root";
$dbpass = "";
$dbhost = "localhost";
$dbname = "test";

$sql = array(
             "create" => "create table php_db_test_pear_mdb2 (id int, name text);",
             "insert" => "insert into php_db_test_pear_mdb2(id, name) value(?, ?);",
             "update" => "update php_db_test_pear_mdb2 set name=concat(name, '_updated');",
             "delete" => "delete from php_db_test_pear_mdb2;",
             "drop"   => "drop table php_db_test_pear_mdb2;",
             );

$db = MDB2::factory("mysql://$dbuser:$dbpass@$dbhost/$dbname");
check($db, "connect");

$clock_start  = time();
$memory_start = memory_get_usage();

$res = $db->query($sql["create"]);
check($res, "create");

$stmt = $db->prepare($sql["insert"], array('integer', 'text'), MDB2_PREPARE_MANIP);
for ($i = 0; $i < 10000; $i++) {
    $res  = $stmt->execute(array($i, "hoge" . $i));
    check($res, "insert $i");
}

$res = $db->query($sql["update"]);
check($res, "update");

$res = $db->query($sql["delete"]);
check($res, "delete");

$res = $db->query($sql["drop"]);
check($res, "drop");

$time   = time() - $clock_start;
$memory = number_format(memory_get_usage() - $memory_start);

echo "Time: $time?n";
echo " Mem: $memory?n";


function check($res, $where) {
    if (PEAR::isError($res)) {
        die($where . ": " . $res->getMessage() . "?n");
    }
}

?>

結果

% php test_db_pear_mdb2.php
Time: 52
 Mem: 482,888
Last modified:2006/08/07 00:57:07
Keyword(s):[PHP] [勉強]
References:[PHP]