NYCPHP Meetup

[John Campbell]

> 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
>