Drupal db query - avoid fetchAll to increase performance

Mar 13th, 2014

Also, see http://php.net/manual/en/function.iterator-to-array.php

Original article: http://www.microserveltd.co.uk/blogs/drupal-development/when-use-query-fetchall-...
Copied below for historical record.

Something that has come up on one of our projects recently is when to use fetchAll() or equivalents like fetchAllAssoc(). When writing a db_query() or db_select() query, you don't always need to use $query->fetchAll() or $query->fetchAllAssoc() to retrieve the result set; it's optional. Database queries in Drupal 7 are iterator objects, so you can treat them like an array and loop over the results with foreach(). If you're going to do any processing on the results, it'll be quicker and use much less memory this way. So for example, this will still work, note the lack of fetchAll():

<?php
// Build and run the query.
$results = db_select('node', 'n')
           ->fields('n', array('nid', 'title'))
           ->condition('status', 0, '!=')
           ->execute();
 
$nodes = array();
// We can use a foreach loop on the $results object.
foreach ($results as $n) {
  // Each $n is an object.
  $nodes[$n->nid] = $n->title;
}
?>

If you need the result as an array, you can use $results->fetchAssoc() to retrieve one item at a time:

<?php
// Build and run the query.
$results = db_select('node', 'n')
           ->fields('n', array('nid', 'title'))
           ->condition('status', 0, '!=')
           ->execute();
 
$nodes = array();
// We can use a foreach loop on the $results object.
while ($n = $results->fetchAssoc()) {
  // Each $n is an array.
  $nodes[$n['nid']] = $n['title'];
}
?>

One common use-case for using fetchAll() is so you can use count($results) to find out how many items were returned by the query. To get around this, you can use a count query instead. So:

<?php
// Create the query, but don't execute it yet.
$query = db_select('node', 'n')
         ->fields('n', array('nid', 'title'))
         ->condition('status', 0, '!=');
 
// This runs a COUNT(*) query to count the number of results.
$count = $query->countQuery()->execute()->fetchField();
// Now run the original query and return the result set.
$results = $query->execute();
?>

More details can be found in the DatabaseStatementInterface API documentation https://api.drupal.org/api/drupal/includes!database!database.inc/interface/Datab... , or in the PHP PDO manual http://www.php.net/manual/en/class.pdostatement.php

Article Type

General