[nycphp-talk] Trying to decide between MDB2 and PHP PDO
Patrick May
patrick at hexane.org
Thu Jun 25 21:01:32 EDT 2009
Mainly b/c of it's variety of supported query types. it's quite nice.
Notice the seamless and consistent parameter escaping as well.
~ p
--
include('/path/to/adodb.inc.php');
$DB = NewADOConnection('mysql');
$DB->Connect($server, $user, $pwd, $db);
# M'soft style data retrieval with binds
$rs = $DB->Execute("select * from table where key=?",array($key));
while (!$rs->EOF) {
print_r($rs->fields);
$rs->MoveNext();
}
# PEAR style data retrieval
$rs = $DB->Execute("select * from table where key=123");
while ($array = $rs->FetchRow()) {
print_r($array);
}
# Alternative URI connection syntax:
$DB = NewADOConnection("*mysql://$user:$pwd@$server/$db?persist*");
# No need for Connect or PConnect when using URI syntax
$ok = $DB->Execute("update atable set aval = 0");
if (!$ok) mylogerr($DB->ErrorMsg());
# Updating tables
$ok = $DB->Execute("update table set col1=? where key=?",array($colval, $key));
# retrieving data shortcuts
$val = $DB->GetOne("select col from table where key='John'");
$row = $DB->GetRow("select col from table where key='John'");
$arr = $DB->GetAll("select col from table");
$arr = $DB->GetAssoc("select key,col from table"); # returns
associative array $key=>col
$val = $DB->GetCol("select col from table"); # returns array of col
# Retrieve high speed cached recordsets (cached for 3600 secs)
# Cache directory defined in global $ADODB_CACHE_DIR.
# CacheGetOne, CacheRow, CacheGetAll all work
$rs = $DB->CacheExecute(3600, "select orgname from users where user='JOHN'");
On Thu, Jun 25, 2009 at 8:57 PM, Patrick May <patrick at hexane.org> wrote:
> 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
>
>
--
Patrick May
+1 (347) 232-5208
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.nyphp.org/pipermail/talk/attachments/20090625/ab99479b/attachment.html>
More information about the talk
mailing list