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

by

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

More Related Contents:

  1. PDO prepared statement fetch() returning double results
  2. Can I mix MySQL APIs in PHP?
  3. How to apply bindValue method in LIMIT clause?
  4. PDO support for multiple queries (PDO_MYSQL, PDO_MYSQLND)
  5. PDOException “could not find driver”
  6. Row count with PDO
  7. How to prevent duplicate usernames when people register?
  8. PDO get the last ID inserted
  9. How do I use password hashing with PDO to make my code more secure? [closed]
  10. How to view query error in PDO PHP
  11. PDO::__construct(): Server sent charset (255) unknown to the client. Please, report to the developers
  12. Insert/update helper function using PDO
  13. pdo prepared statements with wildcards
  14. mysql_fetch_array return only one row
  15. Insert multiple rows with PDO prepared statements
  16. Do SQL connections opened with PDO in PHP have to be closed
  17. Is there a way to fetch associative array grouped by the values of a specified column with PDO?
  18. php code to test pdo is available?
  19. What is the advantage of using try {} catch {} versus if {} else {}
  20. Alternative for mysql_num_rows using PDO
  21. ERROR: SQLSTATE[HY000] [2002] No connection could be made because the target machine actively refused it
  22. Unknown database error in PHP but it exists in PHPMyAdmin
  23. How do detect that transaction has already been started?
  24. return one value from database with mysql php pdo
  25. Trying to access array offset on value of type bool
  26. return multiple response data in one response
  27. How to get a query error from prepare() in PDO PHP?
  28. How can I pass an array of PDO parameters yet still specify their types?
  29. PHP PDO how to run a multiple query request?
  30. Is a BLOB converted using the current/default charset in MySQL?

Leave a Comment