Hello all,
I am trying to decide whether to use MDB2 or PHP PDO. I am sort of
leaning towards using PHP PDO at the moment, but I'd rather ask some
experts on their opinion. Just fyi, I am using PHP 5.2.6-1+lenny3 and
mySQL on a Linux server, I have created a table of about 20 fields and
loaded 20,000 rows of data into the table. I then ran a select with a
where clause and an update on every row, while testing performance of
MDB2 and PHP PDO. I also tested whether a raw statement query to the
server would be faster than forming a prepare statement and then
executing using both MDB2 and PHP PDO.
In my tests I found that using raw statements in MDB2 and PHP PDO is
faster than using a prepare statement and then executing it for each
row (raw statement was much faster using MDB2 and only slightly faster
using PHP PDO). To capture the times I issued a microtime() just
before the query/execute and just after.
For processing 20,000 transactions:
Here are my results for the MDB2 raw Select php program:
- ran for 10.1 seconds vs 17.7 seconds for the prepare. The MDB2 raw
update ran for 12.3 seconds vs 20.3 seconds for the MDB2 prepare.
Here are my results for the PDO raw Select PHP program:
- ran for 8.3 seconds vs 8.3 seconds for the prepare. The PDO raw
update ran for 5.78 seconds vs 5.92 seconds for the PDO prepare.
I have pasted two of my programs' source code: the first uses prepares
and MDB2 and the second uses prepares and PHP PDO.
*//**//*I was told that using a prepare is much faster then using raw
sql but my test results prove otherwise. Why is the prepare and
execute method is slower than building the statement manually and
querying the server? Shouldn't the prepare and execute method be
faster since you only need to generate the prepare once as opposed to
generating the statements within a loop?
-Kevin
<?php
$fpiDataAddr = fopen('outputAddr.txt','r') or die("can not open New In
File ");
ini_set('memory_limit', -1);
require_once('MDB2.php');
//Connect to the Database
$arrDsn = array(
'phptype' => 'mysql',
'username' => 'u1',
'password' => 'p1',
'hostspec' => 'localhost',
'database' => 'demo',
);
$mdb2 =& MDB2::connect($arrDsn);
$seqno = 0;
//for normal prepare
$sql1 = 'SELECT * FROM demo.kb_addr WHERE UNIQUE_NUM = ?';
$time_start = getmicrotime();
$sth1 = $mdb2->prepare($sql1, MDB2_PREPARE_RESULT);
$sql2 = 'UPDATE demo.kb_addr SET AM_CITY = ? WHERE UNIQUE_NUM
= ?';
$arrType = array('text','integer');
$sth2 = $mdb2->prepare($sql2, $arrType, MDB2_PREPARE_MANIP);
while($inrec = fgetcsv($fpiDataAddr,0,',','"')){
if($seqno == 0){
$x= count($inrec);
$arrFields = array();
for ($y = 0; $y < $x; $y++) {
$arrFields[$inrec[$y]] = $y; //creates associative array
that associates fields with the index in $inrec
}
echo "Array of Field Names From Header Record in Input data is
\n";
print_r($arrFields);
$seqno++;
continue; }
$key = 0+$inrec[$arrFields['Unique #']];
//for normal prepare
$values = array($key);
$time_start1 = getmicrotime();
$affectedRows =& $sth1->execute($values);
$arrCnt['select'] += getmicrotime() - $time_start1;
$time_elapsed = getmicrotime() - $time_start;
if (PEAR::isError($res)) {
die($res->getMessage());
}
$values = array();
$values = array('Kevin',$key);
$time_start1 = getmicrotime();
$affectedRows =& $sth2->execute($values);
$arrCnt['update'] += getmicrotime() - $time_start1;
$time_elapsed = getmicrotime() - $time_start;
if (PEAR::isError($res)) {
die($res->getMessage());
}
if($seqno > 20000) break;
$seqno++;
}
echo "total: ". $time_elapsed."\n";
echo "execution times:\n";
var_dump($arrCnt);
$rate = $time_elapsed / $seqno;
echo "rate: ".$rate."\n";
// ***************
// Calculate the time including fractions of a second
// ***************
function getmicrotime() {
$mtime = microtime();
$mtime = explode(' ', $mtime);
return($mtime[1] + $mtime[0]);
}
fclose($fpiDataAddr) or die("can not close data file");
$mdb2->disconnect();
?>
------------------------------------------------------------------------------------------------------------
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
------------------------------------------------------------------------------------------------------------
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
------------------------------------------------------------------------------------------------------------
<?php
$fpiDataAddr = fopen('outputAddr.txt','r') or die("can not open New In
File ");
ini_set('memory_limit', -1);
//Connect to the Database
$dsn = 'mysql:host=localhost;dbname=demo';
$connHandle = new PDO ($dsn,'u1','');
$seqno = 0;
$time_start = getmicrotime();
//for normal prepare
$sql1 = 'SELECT * FROM demo.kb_addr WHERE UNIQUE_NUM = ?';
$sth1 = $connHandle->prepare($sql1);
$sql2 = 'UPDATE demo.kb_addr SET AM_CITY = ? WHERE UNIQUE_NUM
= ?';
$sth2 = $connHandle->prepare($sql2);
while($inrec = fgetcsv($fpiDataAddr,0,',','"')){
if($seqno == 0){
$x= count($inrec);
$arrFields = array();
for ($y = 0; $y < $x; $y++) {
$arrFields[$inrec[$y]] = $y; //creates associative array
that associates fields with the index in $inrec
}
$seqno++;
continue; }
$key = 0+$inrec[$arrFields['Unique #']];
//for normal prepare
$values = array($key);
$time_start1 = getmicrotime();
$affectedRows =& $sth1->execute($values);
$arrCnt['select'] += getmicrotime() - $time_start1;
$time_elapsed = getmicrotime() - $time_start;
if (PEAR::isError($res)) {
die($res->getMessage());
}
$values = array();
$values = array('Kevin',$key);
$time_start1 = getmicrotime();
$affectedRows =& $sth2->execute($values);
$arrCnt['update'] += getmicrotime() - $time_start1;
$time_elapsed = getmicrotime() - $time_start;
if (PEAR::isError($res)) {
die($res->getMessage());
}
if($seqno > 20000) break;
$seqno++;
}
echo "total: ". $time_elapsed."\n";
echo "execution times:\n";
var_dump($arrCnt);
$rate = $time_elapsed / $seqno;
echo "rate: ".$rate."\n";
// ***************
// Calculate the time including fractions of a second
// ***************
function getmicrotime() {
$mtime = microtime();
$mtime = explode(' ', $mtime);
return($mtime[1] + $mtime[0]);
}
fclose($fpiDataAddr) or die("can not close data file");
//disconnect
$connHandle = null;
?>
------------------------------------------------------------------------
_______________________________________________
New York PHP User Group Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk
http://www.nyphp.org/show_participation.php
