Given that db_select is much slower than db_query, why would I want to use it?

Question

For the background on this, please see http://drupal.org/node/1067802.

Given all that, what situations exists where I might want to use db_select(), or should I be relying solely on db_query?

Answer 1

There are 5 reasons to use SelectQuery

• You are building dynamic queries with a varying number of conditions, joins, fields and so on. See field_read_fields() for an example.

• You want to use so called Extenders. Example extenders are PagerDefault (replaces pager_query()) and TableSort (replaces tablesort_sql()). These allow to add additionally functionality to SelectQuery. See also How do you make sortable tables with a pager with data from a custom table?. An example: node_page_default().

• You want to allow other modules to alter your queries. Then you can add so called tags and SelectQuery will automatically call a corresponding alter hook for that tag. I'm am heavily relying on this with my Privatemsg module (We already did that in D6 with a custom query builder).

• If you want/need to use the node_access system to only show nodes the user is allowed to see. Just add the 'node_access' tag to your $query. This replaces db_rewrite_sql().

• SelectQuery has a few features that help to make your code work the same across all supported databases. For example there is SelectQuery::orderRandom(). And if you have a LIKE condition, ->condition('field', $value, 'LIKE') will make sure that it is always a case insensitive comparison. In D6, you had to use LOWER() for that which was much slower. But AFAIK, there aren't more than this two right now.

If none of these reasons apply for a specific case, use db_query().

Answer 2

The documentation about db_query() says:

Use this function for SELECT queries if it is just a simple query string. If the caller or other modules need to change the query, use db_select() instead.

Answer 3

In addition to what said from Berdir, I would say that in the case the query is altered from a function (even another function from the same module that is executing the query), then altering the object returned by db_select() is easier than altering the string containing the query.
Another example of when I would use db_select() instead of db_query() is when the query is dynamically built basing on some parameters, or conditions. The following code is the code executed when calling comment_get_display_ordinal():

function comment_get_display_ordinal($cid, $node_type) {
  // Count how many comments (c1) are before $cid (c2) in display order. This is
  // the 0-based display ordinal.
  $query = db_select('comment', 'c1');
  $query->innerJoin('comment', 'c2', 'c2.nid = c1.nid');
  $query->addExpression('COUNT(*)', 'count');
  $query->condition('c2.cid', $cid);
  if (!user_access('administer comments')) {
    $query->condition('c1.status', COMMENT_PUBLISHED);
  }
  $mode = variable_get('comment_default_mode_' . $node_type, COMMENT_MODE_THREADED);

  if ($mode == COMMENT_MODE_FLAT) {
    // For flat comments, cid is used for ordering comments due to
    // unpredicatable behavior with timestamp, so we make the same assumption
    // here.
    $query->condition('c1.cid', $cid, '<');
  }
  else {
    // For threaded comments, the c.thread column is used for ordering. We can
    // use the vancode for comparison, but must remove the trailing slash.
    // See comment_view_multiple().
    $query->where('SUBSTRING(c1.thread, 1, (LENGTH(c1.thread) -1)) < SUBSTRING(c2.thread, 1, (LENGTH(c2.thread) -1))');
  }

  return $query->execute()->fetchField();
}