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?
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.
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.
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 ...
PDO::FETCH_BOTH (default) Returns an array indexed by both column name and 0-indexed column number as returned in your result set.
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/>';
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With