Should I be using node_load() rather than building my own queries with db_select()?

Question

I'm fairly comfortable with using Drupal 7 functions like db_select(). While node_load() is obviously very easy to use, I often find that it returns fields I don't need and doesn't provide fields that I do need (field that would require some joins to obtain).

Therefore, will building my own queries provide better performance than using node_load() in conjunction with other queries? Or is there some kind of performance boost that results from using node_load(), perhaps from cacheing, etc.?

I'm far from an expert on MySQL performance issues, but it seems to me that constructing a single, specific query is better than using multiple queries that individually only retrieve part of what I ultimately need (and a lot of what I don't).

Answer 1

Technically, yes, writing your own db_query() statements will return the data faster and with less overhead. However, it is not really the "Drupal way" to do things.

node_load() provides a 'static' cache; in other words, if you load the same node twice in one page request, it will only hit the DB once.

What you are really missing out on by not using node_load() is all the API hooks and third-party module integration. If you need a CCK field, node_load() gets all that data for you, where as a manual query would require a lot of joins.

As you note, there is a lot of extra processing/loading going on that you may not need at the given moment. If you don't need the data, it won't technically hurt you not to have it. It is really up to you if you want to go this route.

The biggest reason IMO for using node_load() even though you might not need the extra data is just for the purpose of standardization. You won't have to worry about what pieces of data you have at a certain point. You will have a fully loaded node and will have more options for adjusting the data as necessary through hook_nodeapi(). Other developers looking at your code will have an easier time understanding what they are working with. Also, 6 months down the road when you go to add a new feature/display field you wont have to adjust your queries.

node_load is definitely a resource hog, but i have found it better to use in my experience. Drupal 7 greatly improves the situation by providing node_load_multiple() which, as its name implies, reduces the number of queries when loading multiple nodes.

Answer 2

As explained in jakarska's answer, node_load(), node_load_mutliple() do more than just DB querying. They take care of calling all the node building hooks implemented by the enabled modules of your site.

In addition, in Drupal 7, with the introduction of pluggable field storages, when working on re-usable code, you can't known whether or not your field data will be available in your (main) database. As a general rule, I would use node_load(), node_load_mutliple() and EntityFieldQuery to query for nodes. If you only need a specific field for multiple node you can also use field_attach_load() with an $options['field_id'] on the results of an EntityFieldQuery.

So, I wouldn't go for a custom SQL query unless the code is sure to be used in cases where everything that node_load(), node_load_mutliple() and EntityFieldQuery provide are not, and will never be, needed and on data that are, and will always, be stored in the SQL database.

Answer 3

node_load() is the "Drupal way"; it means that, by calling this function, the API sends message to modules when node loading has started, so these can add/change/remove their own information. Yes, even node_load() uses caching, as it uses a lot of queries.
You can use db_select() for selecting concrete data, but be sure that you collect/change/delete correct data.

Answer 4

You can carefully build a view to filter and relate what you want and then use views_get_view_result() function to get the result.

And by carefully I mean that you only use the fields you need. For example, including a field of node title with a link to it's node will make your query considerably longer in views instead of only using node id without a link.

Views is a powerful query builder and using it programmatically could be a good answer to your problem. You can get the best of both worlds: easy queries that are friendly with Drupal and faster results compared to loading all data from a particular entity.

You must configure your views module to see the generated queries when building it. Also you could find useful to name or tag your views as "system" or something like that, to avoid undesirable overrides.