[nycphp-talk] Trying to decide between MDB2 and PHP PDO
Patrick May
patrick at hexane.org
Thu Jun 25 20:57:55 EDT 2009
i'd recommend adodb as your php db interface library:
http://adodb.sourceforge.net/
On Thu, Jun 25, 2009 at 5:28 PM, Eugene Jen <craigs.nyc.eugene at gmail.com>wrote:
> >From the result of your benchmark, I think I need to trace the PDO, MDB
> source.
> It is very possible PDO and MDB doesn't do MySQL server side prepared
> statement, which
> will increased the performance. PDO and MDB may just emulate JDBC doing
> fake client
> side prepared statement, which will not increase the speed for each query.
>
> But I guess until I look a PDO and MDB, my answer is just a guess.
>
> Eugene Jen
>
> Kevin Castiglia wrote:
>
> 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 Listhttp://lists.nyphp.org/mailman/listinfo/talk
> http://www.nyphp.org/show_participation.php
>
>
>
> _______________________________________________
> New York PHP User Group Community Talk Mailing List
> http://lists.nyphp.org/mailman/listinfo/talk
>
> http://www.nyphp.org/show_participation.php
>
--
Patrick May
+1 (347) 232-5208
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.nyphp.org/pipermail/talk/attachments/20090625/2104d8c9/attachment.html>
More information about the talk
mailing list