26

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

j0k
  • 22,600
  • 28
  • 79
  • 90

5 Answers5

47

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.

Community
  • 1
  • 1
GateKiller
  • 74,180
  • 73
  • 171
  • 204
  • 1
    I would appreciate feedback on the downvote. This is the only quantitative answer to this question so far. – GateKiller May 13 '13 at 09:54
  • It's a pain to have a downvote without an explanation, I know. Well, with my upvote, you're back to square one. I mean square zero. Thanks for the test and the script. Perhaps one enhancement. I think that using the data should be part of the test, as accessing an array or an object may also result in a difference in timing. In addition, there may even be a difference in requesting data from a large array or a large object. – Erik Bongers Jun 22 '13 at 20:04
  • Note. Laziness will cost more if you pull all the params from the result. I guess it useful when you get a huge response and need only a fraction. – michaelbn Jan 01 '16 at 08:40
4

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);
bugxo
  • 49
  • 2
3

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.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • 3
    It is not an answer for the question – Finesse Oct 25 '17 at 00:07
  • @your-common-sence I agree with you answer but it is an answer for another question – Finesse Oct 26 '17 at 07:27
  • Some questions shouldn't be answered. If someone asks "how to shoot myself in the foot", the answer is "don't do it!", not a detailed instruction on how to actually make one crippled for the rest of their life. – Your Common Sense Oct 26 '17 at 07:29
  • You are comparing a fly with an elephant. In some creasy cases even a minor performance is important. – Finesse Oct 27 '17 at 08:06
  • NOT in case when there is a FUNCTIONAL difference between approaches. One should choose the fetch mode that is best for their purpose, not one which has an imaginary performance benefit. – Your Common Sense Oct 27 '17 at 08:23
2

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.

Sjoerd
  • 74,049
  • 16
  • 131
  • 175
  • 2
    Is there any situation where lazy-loading decreases performance? – MD Sayem Ahmed Jul 25 '10 at 10:52
  • well i haven't actually tried it, but i am sure that the difference are minor. but i don't now really. – Adam Ramadhan Jul 25 '10 at 11:04
  • 1
    "This is useful if you use only a part of the returned data." Why would you query for data you aren't going to use? Or is it a matter of better get too much rather than having to query again? – PeeHaa Sep 03 '12 at 21:14
  • 1
    Note: `PDO::FETCH_LADY` cannot be used with `fetchAll()` – Rendicahya Apr 04 '15 at 08:49
  • @MDSayemAhmed , Yes, when you access all the properties you actually use the laziness for nothing. The laziness is then Just an extra work. – michaelbn Jan 01 '16 at 08:34
-2

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

Nadir
  • 695
  • 8
  • 12
  • The other answer also states "Once you need it [big data] - microscopic difference between these methods would be your least concern." Some PHP users have no common sense and would use not **the method which is appropriate for their needs** but one which is imaginary "faster". – Your Common Sense Jun 18 '14 at 06:35
  • 1
    Right. Ie I would always recommend Assoc because you don't want Num access, even if it faster. And I found that Assoc is always better than Class because you could "miss" data if prop is not defined. – Nadir Jun 18 '14 at 13:57