[nycphp-talk] Trying to decide between MDB2 and PHP PDO
Eric Gewirtz
eric.gewirtz at solutiononecdi.com
Thu Jun 25 18:02:54 EDT 2009
Hi, I work with Kevin. Our application needs to run on both mySQL and Oracle
so we are looking for portability. We currently are using PEAR DB and we are
looking into moving to MDB2 or PDO so we thought it would be a good idea to
test performance before we make a decision. Amazing what you find when you
dig ...
Thanks for the ideas and perspective
SolutionOne
Eric Gewirtz
162 Turk Hill Road
Brewster NY, 10509
Phone: 845-729-7800
FAX: 845-279-5502
>-----Original Message-----
>From: talk-bounces at lists.nyphp.org [mailto:talk-bounces at lists.nyphp.org] On
>Behalf Of John Campbell
>Sent: Thursday, June 25, 2009 5:38 PM
>To: NYPHP Talk
>Subject: Re: [nycphp-talk] Trying to decide between MDB2 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?
>
>The "prepare is faster" lie was started as a conspiracy to trick the
>idiots into using prepared statement for security reasons. Prepared
>statements are typically twice as slow because you have to talk to the
>database twice, and in your case, they are about the same as not using
>a prepared since you are doing repeated queries.
>
>>From your data, it looks like MDB2 is probably doing something stupid
>under the hood and possibly re-preparing in each iteration.
>
>I think MDB2 and PDO offer some sort of "Emulate Prepared Statements"
>mode, and I would re-run your tests using that.
>
>Personally, I find PDO pointless, and MDB2 overkill. Any reason you
>are ruling out mysqli?
>
>Regards,
>John Campbell
>
>> }
>>
>> 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
>>
>_______________________________________________
>New York PHP User Group Community Talk Mailing List
>http://lists.nyphp.org/mailman/listinfo/talk
>
>http://www.nyphp.org/show_participation.php
More information about the talk
mailing list