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

Question

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?

Answer 1

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/>';

Answer 2

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.

Answer 3

all benchmarks above which measure "memory footprint" are actually incorrect for the very simple reason.

PDO by default does load all the things into the memory and it does not care if you use fetch or fetchAll. To really get benefits of unbuffered query you should instruct PDO to use unbuffered queries:

$db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

In that case you will see huge difference in memory footprint of the script

Answer 4

@Arkh

// $data in this case is an array of rows;

$data = $stmt->fetchAll();


// $data in this case is just one row after each loop;

while($data = $stmt->fetch()){}


// Try using

$i = 0;

while($data[$i++] = $stmt->fetch()){}

The memory difference should become neglijable

Answer 5

But surely if you're storing the fetched data in an array, the memory usage will be equal?

<?php
define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASS', '');
// database to use
define('DB', 'test');
try
{
   $dbh = new \PDO('mysql:dbname='. DB .';host='. DB_HOST, DB_USER, DB_PASS);   $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
   $sql = 'SELECT * FROM users 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/>';
}
catch ( PDOException $e )
{
   echo $e->getMessage();
}
?>

Result : 
fetchAll : 2.6941299438477E-5s, 9824b
fetch : 1.5974044799805E-5s, 9824b

Answer 6

As Mihai Stancu was saying, there is almost no memory difference though fetchAll beats fetch + while.

Result : 
fetchAll : 0.160676956177s, 118539304b
fetch : 0.121752023697s, 118544392b

I got the results above with running while correctly:

$i = 0;
while($data[$i++] = $stmt->fetch()){
    //
}

So the fetchAll consumes less memory, but fetch + while is faster! :)

Answer 7

I know this is an old topic, but I run across this having the same question. Having run my own simple "benchmark" and reading what others wrote here I came to the conclusion that this is not an exact science and while one should strive to write the quality, light code, there is no point wasting too much time at the start of the project.

My suggestion is: Gather data by running the code(in beta?) for a while and then start optimizing.

In my simple benchmark (only tested execution time) I've got results varying between 5% and 50% BOTH ways. I run both options in the same script, but when I run fetch + while first it has been faster than fetchall and vice versa. (I know I should have run them single and couple hundred times get the median and mean and then compare, but - as I have said at the begining - I concluded that in my case it is too early to start doing so.)