db_query performance

Question

This is a more general question about db_query() performance.

I have this function to create an API resource with Services module, and I need to get about 10 fields belonging to several nodes. Basically I have a list of nids and create an array containing information from their fields. The nodes have a lot more fields than I need so doing node_load to get the info is really slow, so at the moment I am geting the info using db_query() calls for each field that I need.

My question is: would it perform better if I only have one db_query with about 10 inner joins? or should I leave it as 10 separate db_queries?

I am going to do some performance tests, but I thougt someone might have some insight or explanation on why one would perform better tha. the other.

Answer 1

• Install entitycache - it will load the nodes quickly from a persistent cache ( e.g memcache, but db ok )
• Use EntityFieldQuery to fetch the relevant nodes.

e.g:

  // Get published articles created in last 7 days.
  $query = new EntityFieldQuery();
  $query->entityCondition('entity_type', 'node')
        ->entityCondition('bundle', 'article')
        ->propertyCondition('status', 1)
        ->propertyCondition('created', strtotime('-7 days', REQUEST_TIME), '>');
  $result = $query->execute();
  if(!empty($result['node'])){
    // These will be loaded from entitycache
    $nodes = node_load_multiple(array_keys($result['node']));
    // Process result nodes
    foreach($nodes as $node){
      // Do stuff
    }
  }

Answer 2

You don't want to be doing 10 joins on the field tables. If your content base (ie. fields tables) grows to any substantial size, this query will get very, very slow. Doing 10 separate queries isn't ideal, but not the end of the world, either.

Realistically though, doing node_load() on 10 ids shouldn't be too bad. Nodes are statically cached, so multiple calls to node_load() with the same id won't result in more queries than necessary, and if the same nodes are already being used for something else on the page, you might as well take advantage of their presence in the static cache.