One of the most versatile Drupal modules, Views, comes with an impressive user interface that allows you to capitalize on all its functionality – or does it? Although this interface is the primary means of using Views, the module has plenty to offer on the code level too.
Recently I was working on a task that involved selecting nodes of a certain type from a huge list of node IDs. When it comes to data manipulation on a complex web site, a web developer needs to take due precaution. An inefficient SQL query can bring the site to its knees by creating a performance bottleneck. So I looked into several ways to make the queries more efficient; Views was a good candidate.
In this case, I had a list that contained node IDs only – there was no way to join it to the node table directly to get the type in one SQL query. Therefore I had to determine the node types separately. The first thing that comes to mind is using node_load() to retrieve the node object, which contains the type property. This would be a perfectly good solution for loading a couple of nodes, but if you need to load hundreds, that could have a negative impact on performance.
Not only does node_load() run a heavy SQL query with three table joins, it also fires the nodeapi hooks in all modules. This is the hook where modules load all node related data and attach it to the node object, so this can add up to quite a few queries with just some basic modules enabled. Using the Devel module, I summarized the execution times of queries that run during a node_load() call.
The figure below shows the SQL query times for functions in commonly used modules for a single node_load() call; the values are averages of three runs. On a complex site, of course, this is a rough underestimation.
| Function that executes the SQL query | Query execution time (ms) |
|---|---|
| node_load | 0.56 |
| comment_nodeapi | 0.43 |
| drupal_lookup_path (called by path_nodeapi) | 0.66 |
| taxonomy_node_get_terms (called by taxonomy_nodeapi) | 5.97 |
| page_title_load_title (called by page_title_nodeapi) | 0.34 |
| Total | 7.96 |
As one can infer, node_load() retrieves far more data than was needed for the task. In Views, on the other hand, I can select the data that I want loaded. When the view is executed, it retrieves only the data that I selected, eliminating unnecessary queries. Fortunately enough, Views offers a function, named views_get_view_result(), to get the data a view retrieves, without having to render the view. Returning an array of the selected nodes, it can act as an abstraction layer above database functions. The following figure shows the execution times of queries run during a views_get_view_result() call – assuming the view is already cached.
| Function that executes the SQL query | Query execution time (ms) |
|---|---|
| cache_get | 0.51 |
| title_query | 0.45 |
| execute | 0.26 |
| Total | 1.22 |
That’s an attractive six-fold performance increase. In terms of page load time – a pivotal point of busy sites, this gain could lead to a time-savings in the hundreds of milliseconds.
There might be occasions where you simply want to run an SQL query against the node table to retrieve only a few pieces of data. Although this indeed can be even faster than using a view, on the long run it might be worthwhile to use an abstraction layer like Views to avoid problems in case the underlying data structure changes.
UPDATE 3/2/2011:
When measuring the overall performance, however, one should expect Views to inflict some additional load on other resources. As a result, you can profit from this approach only if the database connection is the weakest point of the server.