1

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.

  • Have you tried EntityFieldQuery()? – vintorg Jul 15 '13 at 22:11
2
  • 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
    }
  }
  • I already get the node ids from a view using views_get_view_result() so I basically skip the first bit of your answer (EntityFieldQuery) and would go straight to your bit with node_load_multiple(). The view is limited to 100 results so I will give entitycache combined with node_load_multiple() a try. – Titi Jul 16 '13 at 8:40
0

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.

  • 1
    Static cache is only available for entities loaded in the same page request, hence the suggestion to use entitycache which creates a persistent node cache. – David Thomas Jul 16 '13 at 3:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.