Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PDO::fetchAll vs. PDO::fetch in a loop

Tags:

php

mysql

pdo

fetch

Just a quick question.

Is there any performance difference between using PDO::fetchAll() and PDO::fetch() in a loop (for large result sets)?

I'm fetching into objects of a user-defined class, if that makes any difference.

My initial uneducated assumption was that fetchAll might be faster because PDO can perform multiple operations in one statement while mysql_query can only execute one. However I have little knowledge of PDO's inner workings and the documentation doesn't say anything about this, and whether or not fetchAll() is simply a PHP-side loop dumped into an array.

Any help?

like image 670
Lotus Notes Avatar asked May 05 '10 04:05

Lotus Notes


People also ask

What is the difference between fetch and fetchAll?

fetchall() fetches all the rows of a query result. An empty list is returned if there is no record to fetch the cursor. fetchone() method returns one row or a single record at a time. It will return None if no more rows / records are available.

What does PDO fetchAll return?

Return Values ¶ PDOStatement::fetchAll() returns an array containing all of the remaining rows in the result set. The array represents each row as either an array of column values or an object with properties corresponding to each column name. An empty array is returned if there are zero results to fetch.

How does PDO fetch work?

fetch does not store information in memory and it works on row to row basis, so it would go through the result set and return row 1, than again would go to the result set and than again return row 2 mind here that it will not return row 1 as well as 2 but would only return row 2, so fetch will not store anything into ...

Which one is default mode of fetching data in PDO?

PDO::FETCH_BOTH (default) Returns an array indexed by both column name and 0-indexed column number as returned in your result set.


2 Answers

Little benchmark with 200k random records. As expected, the fetchAll method is faster but require more memory.

Result : fetchAll : 0.35965991020203s, 100249408b fetch : 0.39197015762329s, 440b 

The benchmark code used :

<?php // First benchmark : speed $dbh = new PDO('mysql:dbname=testage;dbhost=localhost', 'root', ''); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $sql = 'SELECT * FROM test_table WHERE 1'; $stmt = $dbh->query($sql); $data = array(); $start_all = microtime(true); $data = $stmt->fetchAll(); $end_all = microtime(true);  $stmt = $dbh->query($sql); $data = array(); $start_one = microtime(true); while($data = $stmt->fetch()){} $end_one = microtime(true);  // Second benchmark : memory usage $stmt = $dbh->query($sql); $data = array(); $memory_start_all = memory_get_usage(); $data = $stmt->fetchAll(); $memory_end_all = memory_get_usage();  $stmt = $dbh->query($sql); $data = array(); $memory_end_one = 0; $memory_start_one = memory_get_usage(); while($data = $stmt->fetch()){   $memory_end_one = max($memory_end_one, memory_get_usage()); }  echo 'Result : <br/> fetchAll : ' . ($end_all - $start_all) . 's, ' . ($memory_end_all - $memory_start_all) . 'b<br/> fetch : ' . ($end_one - $start_one) . 's, ' . ($memory_end_one - $memory_start_one) . 'b<br/>'; 
like image 141
Arkh Avatar answered Oct 04 '22 07:10

Arkh


One thing about PHP that I've found to be true almost always is that a function you implement yourself will almost always be slower than the PHP equivalent. This is because when something is implemented in PHP it doesn't have all the compile time optimizations that C has (which PHP is written in) and there is high overhead of PHP function calls.

like image 26
Kendall Hopkins Avatar answered Oct 04 '22 07:10

Kendall Hopkins