The fastest way to get nids in a default no cache situation

In this test case scenario a function was made to show the differences when querying for node ids. Mostly this kind of query is the first step to render nodes in a custom made view. Each query returns the same array of node ids. Execution times are tested by using the Drupal 7 bootstrap timer functions.

The result isn't to surprising, for a single table query, using db_query() or db_select() is way faster than using EntityFieldQuery(). The reason for choosing db_query or db_select is in this case mainly a developers preference. However, in my opinion it is better to build a dynamic query because it's much more readable when extended.


/**
 * Implements hook_block_info().
 */
function block_performance_block_info() {
  $blocks['default_node_view'] = array(
    'info' => t('Shows a block with some content in it.'),
       
  );

  return $blocks;
}

/**
 * Implements hook_block_view().
 */
function block_performance_block_view($delta = '') {
  switch($delta) {
    case 'default_node_view':
      return array(
        'subject' => t('Load some content.'),
        'content' => _block_performance_load_content(),
      );
      break;
  }
}

/**
 * Load some default content from the DB.
 */
function _block_performance_load_content() {
  
  /*
   * Test performance by using the Drupal bootstrap timer functions.
   * http://dribbit.eu/drupal-7/performance-using-the-bootstrap-timer-functions
   */
  
  // 1) Load content by using a default dynamic query.
  timer_start('block_performance_default_dynamic_query');
  
  $nids = db_select("node", 'n')
  ->fields('n', array('nid'))
  ->condition('type', 'article')
  ->condition('status', 1)
  ->orderBy('created', 'ASC')
  ->execute()->fetchCol();
  
  dpm(timer_stop('block_performance_default_dynamic_query'));
  
  /*
   * Block loaded 5 times as a logged in user without caching enabled.
   * Loaded 321 elements on a new standard install.
   * 
   * - 4 ms
   * - 5.23 ms
   * - 2.82 ms
   * - 2.4 ms
   * - 3.2 ms
   * 
   * Fast execution time and in my opinion the best readable and extendable query structure.
   */
  
  // 2) Load content by using EntityFieldQuery.
  timer_start('block_performance_entityfieldquery');
  
  $query = new EntityFieldQuery();
  $query->entityCondition('entity_type', 'node')
  ->entityCondition('bundle', 'article')
  ->propertyCondition('status', 1)
  ->propertyOrderBy('created', 'ASC');
  $result = $query->execute();
    
//   timer_start('array_key');
//   $nids = array_keys($result['node']);
//   dpm(timer_stop('array_key'));
  /*
   * Array keys processes in an average of 1.2ms.
   */
  
  timer_start('foreach_key');
  foreach($result['node'] as $node) {
    $nids[] = $node->nid;
  }
  dpm(timer_stop('foreach_key'));
  
 /*
  * Foreach loop processes in an average of 0.7 ms.
  */
  
  dpm(timer_stop('block_performance_entityfieldquery'));
  
  /*
   * Block loaded 5 times as a logged in user without caching enabled.
   * Loaded 321 elements on a new standard install.
   * 
   * - 33.82 ms
   * - 29.97 ms
   * - 36.7 ms
   * - 23.58 ms
   * - 27.83 ms
   * 
   * EntityFieldQuery is a painfull last in this situation.
   * One reason is the additional array_keys function to process the $result['node'] array.
   * -> We can use a foreach, which processed nids twice as fast but this doesn't change the big picture. 
   * 
   * Another one is that a node data object is returned with nid, vid and type, 
   * while we only need nid in this particular case.
   */
  
  // 3) Load content by using a default string query.
  timer_start('block_performance_default_string_query');
  
  $nids = db_query(
    'select nid from node where status = :status and type = :type order by created asc', 
    array(
      ':status' => 1, 
      ':type' => 'article',    
    )
  )->fetchCol();
  
  dpm(timer_stop('block_performance_default_string_query'));
  
  /*
   * Block loaded 5 times as a logged in user without caching enabled.
   * Loaded 321 elements on a new standard install.
   * 
   * - 2.28 ms
   * - 3.22 ms
   * - 2.4 ms
   * - 4.85 ms
   * - 3.02 ms
   * 
   * Slightly the fastest query.
   * However, the more complex the query becomes, the less readable it gets.
   */

}

Add new comment

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
By submitting this form, you accept the Mollom privacy policy.