NYCPHP Meetup

[Eddie Drapkin]

On Thu, Jun 25, 2009 at 5:38 PM, John Campbell<jcampbell1 at gmail.com> wrote:
>> 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
>

Wait, are you advocating //against// prepared statements?

It's a pretty well known fact, or at least I thought so, that MySQL
doesn't cache prepared statement opcodes and must re-compile them
every time ::execute() is called, whereas a better RDBMS (correct me
if I'm wrong, I believe postgres does) will cache the prepared
opcodes; granted, you're not going to see some great huge speed
improvement unless you're running ridiculous queries that ought to be
stored procedures anyhow.  I still don't understand the hate for
prepared statments, as they are inherently more secure and allow a
greater flexibility with data type checks, as well as making your code
look a whole hell of a lot cleaner if deployed properly.

And why the hate for PDO?