On Thu, Jun 25, 2009 at 5:38 PM, John Campbell<[email protected]> 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? _______________________________________________ New York PHP User Group Community Talk Mailing List http://lists.nyphp.org/mailman/listinfo/talk http://www.nyphp.org/show_participation.php
