php - while - Is there a performance difference between PDO fetch statements?




php statement fetch (4)

like in

/* Exercise PDOStatement::fetch styles */
print("PDO::FETCH_ASSOC: ");
print("Return next row as an array indexed by column name\n");
$result = $sth->fetch(PDO::FETCH_ASSOC);
print_r($result);
print("\n");

print("PDO::FETCH_BOTH: ");
print("Return next row as an array indexed by both column name and number\n");
$result = $sth->fetch(PDO::FETCH_BOTH);
print_r($result);
print("\n");

print("PDO::FETCH_LAZY: ");
print("Return next row as an anonymous object with column names as properties\n");
$result = $sth->fetch(PDO::FETCH_LAZY);
print_r($result);
print("\n");

print("PDO::FETCH_OBJ: ");
print("Return next row as an anonymous object with column names as properties\n");
$result = $sth->fetch(PDO::FETCH_OBJ);
print $result->NAME;
print("\n");

Well the default are FETCH BOTH, I am wondering if FETCH ASSOC is faster when I'm going to fetch a lot of data; or they are the same?

Thanks


I am wondering if FETCH ASSOC is faster when I'm going to fetch a lot of data; or they are the same?

Do not fetch a lot of data, when you don't need it. that's all.

Once you need it - microscopic difference between these methods would be your least concern.


ASSOC, BOTH and OBJ are generally the same, except that they return a different structure. No performance differences there.

LAZY does some sort of lazy loading. PDO::FETCH_LAZY creates the object variable names as they are accessed. This means that you get the performance penalty only when you access the properties, not when calling fetch(). This is useful if you use only a part of the returned data.


Last answer is silly ("do no fetch...."): what if you have to transform data for a big table, or interface dbs tables?

I changed the benchmarking code above, because it was not testing corretcly IMHO (one single fetch per loop is not enough ;-) ), I replaced it by 10000 records x 100 loops per fetch type.

I added Fetch_class which was the question I had for myself. I added a real class to be certain the latter test was correct.

results (sorted):

Array
(
    [Lazy] => 88.43896484375
    [Num] => 281.11694335938
    [Assoc] => 310.59375
    [Class] => 384.8310546875
    [Obj] => 395.36401367188
    [Both] => 411.62109375
)

Lazy value is incomplete, since there is no access. but "Both" actually HAS impact

here is the modified gist modified Benchmark code


The benchmark script posted in another answer is counting prepare() and execute() to the time so I write a script for testing only fetch time. I have MySQL database, with about 100000 rows of real data. LongRow contains TEXT column (about 4kB of data per row). ShortRow is (30 bytes per row). FETCH_INTO uses predefined class with all column properties. PHP version 5.4. MySQL 5.5.35. One thing is missing here: average of some runs.

Array
(
    [Items: 10] => Array
        (
            [ShortRow] => Array
                (
                    [FETCH_INTO] => 0.0001068115234375
                    [FETCH_OBJECT] => 0.00013899803161621
                    [FETCH_COLUMN (STR8)] => 6.0081481933594E-5
                    [FETCH_COLUMN (INT)] => 5.8174133300781E-5
                    [FETCH_NUM] => 9.2029571533203E-5
                    [FETCH_ASSOC] => 9.8943710327148E-5
                    [FETCH_BOTH] => 0.00011897087097168
                    [FETCH_LAZY] => 6.3180923461914E-5
                )

            [LongRow] => Array
                (
                    [FETCH_INTO] => 0.00012779235839844
                    [FETCH_OBJECT] => 0.00016498565673828
                    [FETCH_COLUMN (TEXT)] => 4.9829483032227E-5
                    [FETCH_COLUMN (INT)] => 4.3153762817383E-5
                    [FETCH_NUM] => 0.00010180473327637
                    [FETCH_ASSOC] => 0.00010895729064941
                    [FETCH_BOTH] => 0.00013399124145508
                    [FETCH_LAZY] => 4.3869018554688E-5
                )

        )

    [Items: 100] => Array
        (
            [ShortRow] => Array
                (
                    [FETCH_INTO] => 0.00081610679626465
                    [FETCH_OBJECT] => 0.0011789798736572
                    [FETCH_COLUMN (STR8)] => 0.00040292739868164
                    [FETCH_COLUMN (INT)] => 0.00041294097900391
                    [FETCH_NUM] => 0.00067806243896484
                    [FETCH_ASSOC] => 0.00076103210449219
                    [FETCH_BOTH] => 0.00092482566833496
                    [FETCH_LAZY] => 0.00043201446533203
                )

            [LongRow] => Array
                (
                    [FETCH_INTO] => 0.0010471343994141
                    [FETCH_OBJECT] => 0.0013670921325684
                    [FETCH_COLUMN (TEXT)] => 0.00037693977355957
                    [FETCH_COLUMN (INT)] => 0.00030612945556641
                    [FETCH_NUM] => 0.00079894065856934
                    [FETCH_ASSOC] => 0.00094914436340332
                    [FETCH_BOTH] => 0.0011270046234131
                    [FETCH_LAZY] => 0.00031089782714844
                )

        )

    [Items: 1000] => Array
        (
            [ShortRow] => Array
                (
                    [FETCH_INTO] => 0.0082287788391113
                    [FETCH_OBJECT] => 0.0099248886108398
                    [FETCH_COLUMN (STR8)] => 0.0037147998809814
                    [FETCH_COLUMN (INT)] => 0.0038070678710938
                    [FETCH_NUM] => 0.006443977355957
                    [FETCH_ASSOC] => 0.0070838928222656
                    [FETCH_BOTH] => 0.008652925491333
                    [FETCH_LAZY] => 0.0039060115814209
                )

            [LongRow] => Array
                (
                    [FETCH_INTO] => 0.0092909336090088
                    [FETCH_OBJECT] => 0.011745929718018
                    [FETCH_COLUMN (TEXT)] => 0.0031650066375732
                    [FETCH_COLUMN (INT)] => 0.0025970935821533
                    [FETCH_NUM] => 0.0068809986114502
                    [FETCH_ASSOC] => 0.0087978839874268
                    [FETCH_BOTH] => 0.010183811187744
                    [FETCH_LAZY] => 0.0026650428771973
                )

        )

    [Items: 10000] => Array
        (
            [ShortRow] => Array
                (
                    [FETCH_INTO] => 0.067224025726318
                    [FETCH_OBJECT] => 0.086459159851074
                    [FETCH_COLUMN (STR8)] => 0.03191089630127
                    [FETCH_COLUMN (INT)] => 0.031462907791138
                    [FETCH_NUM] => 0.047988891601562
                    [FETCH_ASSOC] => 0.05333399772644
                    [FETCH_BOTH] => 0.065713882446289
                    [FETCH_LAZY] => 0.028834819793701
                )

            [LongRow] => Array
                (
                    [FETCH_INTO] => 0.12389183044434
                    [FETCH_OBJECT] => 0.15812706947327
                    [FETCH_COLUMN (TEXT)] => 0.03816294670105
                    [FETCH_COLUMN (INT)] => 0.035914897918701
                    [FETCH_NUM] => 0.1117901802063
                    [FETCH_ASSOC] => 0.10923099517822
                    [FETCH_BOTH] => 0.12394094467163
                    [FETCH_LAZY] => 0.030914068222046
                )

        )

)

Here is a code too:



    //Code is missing connect to DB
    header('Content-Type: text/plain');
    class testModel1 {
        public $id;
        public $invoice;
        public $transaction;
        public $creditedInvoice;
        public $amount;
        public $payment_type;
        public $currency;
        public $created;
        public $timestamp;
    }

    class testModel2 {
        public $id;
        public $cid;
        public $c_amount;
        public $object;
        public $person;
        public $date;
        public $type;
        public $invoice_type;
        public $version;
        public $templateInvoice;
        public $account;
        public $variable_symbol;
        public $number;
        public $accounting_year;
        public $amount;
        public $currency;
        public $comment;
        public $data;   //is a text column (avg size about 4kB)
        public $created;
        public $modified;
        public $timestamp;
    }
    $items = array(10,100,1000,10000);
    foreach($items as $item) {
        $ivStmt = $pdo->prepare("SELECT * FROM `invoices_paying` LIMIT $item");
        $ivStmt->execute(array('items'=>$item));
        $out = array();
        $testModel1 = new testModel1();
        $ivStmt->setFetchMode(PDO::FETCH_INTO, $testModel1);
        $start = microtime(true); 
        while($id = $ivStmt->fetch()) {
        }
        $end = microtime(true);
        $out['FETCH_INTO'] = $end-$start;

        $ivStmt->execute(array('items'=>$item));
        $start = microtime(true); 
        while($id = $ivStmt->fetchObject()) {
        }
        $end = microtime(true);
        $out['FETCH_OBJECT'] = $end-$start;

        $ivStmt->execute(array('items'=>$item));
        $start = microtime(true); 
        while($id = $ivStmt->fetchColumn(5)) {
        }
        $end = microtime(true);
        $out['FETCH_COLUMN (STR8)'] = $end-$start;

        $ivStmt->execute(array('items'=>$item));
        $start = microtime(true); 
        while($id = $ivStmt->fetchColumn(0)) {
        }
        $end = microtime(true);
        $out['FETCH_COLUMN (INT)'] = $end-$start;

        $ivStmt->execute(array('items'=>$item));
        $start = microtime(true); 
        while($id = $ivStmt->fetch(PDO::FETCH_NUM)) {
        }
        $end = microtime(true);
        $out['FETCH_NUM'] = $end-$start;

        $ivStmt->execute(array('items'=>$item));
        $start = microtime(true); 
        while($id = $ivStmt->fetch(PDO::FETCH_ASSOC)) {
        }
        $end = microtime(true);
        $out['FETCH_ASSOC'] = $end-$start;

        $ivStmt->execute(array('items'=>$item));
        $start = microtime(true); 
        while($id = $ivStmt->fetch(PDO::FETCH_BOTH)) {
        }
        $end = microtime(true);
        $out['FETCH_BOTH'] = $end-$start;

        $ivStmt->execute(array('items'=>$item));
        $start = microtime(true); 
        while($id = $ivStmt->fetch(PDO::FETCH_LAZY)) {
        }
        $end = microtime(true);
        $out['FETCH_LAZY'] = $end-$start;

        $table['Items: '.$item]['ShortRow'] = $out;
    }
    foreach($items as $item) {
        $ivStmt = $pdo->prepare("SELECT * FROM `invoices` LIMIT $item");
        $ivStmt->execute(array('items'=>$item));
        $out = array();
        $testModel2 = new testModel2();
        $ivStmt->setFetchMode(PDO::FETCH_INTO, $testModel2);
        $start = microtime(true); 
        while($id = $ivStmt->fetch()) {
        }
        $end = microtime(true);
        $out['FETCH_INTO'] = $end-$start;

        $ivStmt->execute(array('items'=>$item));
        $start = microtime(true); 
        while($id = $ivStmt->fetchObject()) {
        }
        $end = microtime(true);
        $out['FETCH_OBJECT'] = $end-$start;

        $ivStmt->execute(array('items'=>$item));
        $start = microtime(true); 
        while($id = $ivStmt->fetchColumn(17)) {
        }
        $end = microtime(true);
        $out['FETCH_COLUMN (TEXT)'] = $end-$start;

        $ivStmt->execute(array('items'=>$item));
        $start = microtime(true); 
        while($id = $ivStmt->fetchColumn(0)) {
        }
        $end = microtime(true);
        $out['FETCH_COLUMN (INT)'] = $end-$start;

        $ivStmt->execute(array('items'=>$item));
        $start = microtime(true); 
        while($id = $ivStmt->fetch(PDO::FETCH_NUM)) {
        }
        $end = microtime(true);
        $out['FETCH_NUM'] = $end-$start;

        $ivStmt->execute(array('items'=>$item));
        $start = microtime(true); 
        while($id = $ivStmt->fetch(PDO::FETCH_ASSOC)) {
        }
        $end = microtime(true);
        $out['FETCH_ASSOC'] = $end-$start;

        $ivStmt->execute(array('items'=>$item));
        $start = microtime(true); 
        while($id = $ivStmt->fetch(PDO::FETCH_BOTH)) {
        }
        $end = microtime(true);
        $out['FETCH_BOTH'] = $end-$start;

        $ivStmt->execute(array('items'=>$item));
        $start = microtime(true); 
        while($id = $ivStmt->fetch(PDO::FETCH_LAZY)) {
        }
        $end = microtime(true);
        $out['FETCH_LAZY'] = $end-$start;

        $table['Items: '.$item]['LongRow'] = $out;
    }
    print_r($table);







fetch