CODEWITHSUNDEEP
phppdofetch
user397916
user397916

Reputation:

Is there a performance difference between PDO fetch statements?

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

Upvotes: 26

Views: 13847

Answers (5)

bugxo
bugxo

Reputation: 49

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

Upvotes: 4

GateKiller
GateKiller

Reputation: 75869

I wanted to know the answer to this question, so I wrote a quick benchmark script.

I suggest you run this benchmark on your own server, however, this is a typical result on my setup, for single row results:

  1. PDO::FETCH_ASSOC - 936 ms
  2. PDO::FETCH_BOTH - 948 ms
  3. PDO::FETCH_NUM - 1,184 ms
  4. PDO::FETCH_OBJ - 1,272 ms
  5. PDO::FETCH_LAZY - 1,276 ms

For large data sets, these results are typical:

  1. PDO::FETCH_LAZY - 5,490 ms
  2. PDO::FETCH_NUM - 8,818 ms
  3. PDO::FETCH_ASSOC- 10,220 ms
  4. PDO::FETCH_BOTH - 11,359 ms
  5. PDO::FETCH_OBJ - 14,027 ms

See the comments on git hub, fetching all the rows does change the test.

Upvotes: 48

Nadir
Nadir

Reputation: 715

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

Upvotes: -2

Your Common Sense
Your Common Sense

Reputation: 157839

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.

Upvotes: 4

Sjoerd
Sjoerd

Reputation: 75578

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.

Upvotes: 2

Related Questions