Use Views or Write Raw Query?

Question

I've run across an interesting topic through my work during the past few months. I use the views module heavily in Drupal projects, but I find Views to be painfully annoying in terms of its speed and how it generates all this extra markup (even using style_plugin->rendered_fields).

I decided one day to ditch Views for a project and write a raw query using Drupal's PDO layer instead. I feel using the PDO layer (despite the fact that it might not be perfect) gave me the ability to grab only the data that I needed in an efficient query.

I brought up to a coworker, "Why don't we just write raw queries...? Views is obviously not integrated into core yet for a reason..." Their response was that Views are easier for beginners and are generally easier to use and to understand. I agree that's a fair point, but what about the performance differences? Don't the performance differences (if there are even any, I might just be configuring things incorrectly) outweigh the usability of the package?

My question is, is there really a big performance hit with Views? Does it make efficient queries, or does it run node_load for each row? I'd also like to know if there's a reason that Views hasn't been included in core yet.

Is Views better than writing queries using the PDO layer, or is it just a matter of personal preference? What about when working with teams?

Answer 1

Here is my take on it

Views

1. First and most basic advantage is it has a UI which makes it easier for beginners and people with not much knowledge php of to fetch data and display it just with a few clicks.
2. You can apply filters and relationships to make any kind of joins and filtration of data just with a few clicks.
3. It provides easy access to exposed filters which allows you do do sorting of data.
4. It has ajax support.
5. It has a built in cache mechanism to cache data.
6. You can AND or OR your data just by drag and drop.
7. It has support to display the data in various formats like grid, table etc which are available just at a click.

These are just a few points. Now if you try and compare these points to writing a PDO query then yes PDO is definitely fast but you forget after getting the data you also have to do the processing to display the data. Now if you try and do the above mentioned points in a custom module I am pretty sure you will run over a 500 lines of code to implement most of the features.Also don't forget the custom code which needs to be maintained.

If you want to compare views to a PDO check the views query time and the PDO query time it will not be a major difference. Views takes time to do all the rest of the fancy stuff you need to process sanitize your data and display your data in the format of your choice which takes time.

If you are comfortable with PDO I would definitely recommend you to go ahead and use it. The adv are you are using a core feature, 1 less module to me loaded so less http request to load the js and css files.

UPDATE: 10/03/2012 Also came across this node on drupal.org: http://drupal.org/node/242311

Answer 2

Views design goals are quite different from those of the PDO layer. Though to compare them at the performance level, I would argue PDO wins every time provided you are writing queries that are more efficient than those auto-generated by Views.

Views brings many additional features to the table as well. For example, theming, caching, access control and integration with hundreds of contributed modules through an easy to swallow UI.

If you need to fine tune a query for performance reasons, hand-write the query.

If a client needs to be able to modify a query, the Views UI has a much simpler learning curve than PHP and the Drupal PDO layer.

Answer 3

Interesting question, especially these phrases in it that caught my attention:

• ... writing queries using the PDO layer ...
• ... an efficient query ...

Moreover, the title of this question (= "Use Views or Write Raw Query?") sounds to me that you might be interested in learning about other options available, different from using the views module. If that's the case, then the Forena module is another option to consider. It comes with quite some community documentation. And "Write Raw Query" is exactly one of the things you would use Forena for (i.e. what in forena is called 'data blocks').

Forena is built of the idea of using SQL to get data out of a database (which can be the Drupal database or an external one such as My SQL, Oracle, MS SQL, ...) and use XHTML and CSS to format it into web reports. There are quite some reasons for considering Forena, which also explains how "Forena complements the views module" (suggested in some other answers here also), i.e.:

• "Why not use views instead of forena?" ... The answer is generally something along the lines of "Most forena users love views, views is great! If you want to report against external data though, or you want to do aggregation or analytics, Forena is much more suited to address these requirements."
• "What does it take to create reports / charts with the typical Business Intelligence tools? Where would I start in case such reports are to be shown on a Drupal site ... and the actual data may either come from any of Drupal's own tables, or in a PDO-style external database, or some XML or CSV file?" ... Trying to answer these questions leads to more questions such as "How far can you get (and what would it take to get there) with the views module (not even talking about how to secure the access to such data)?

Visit the Forena HowTos page for some links (near the bottom) to some live demo / showcase links.

Refer to the example in my answer to https://drupal.stackexchange.com/questions/152166/fetching-drupal-articles-tags-image-directly-from-database/154746#154746 to get an idea of what it is like to use Forena, such as the SQL query and the source of the "custom report", which looks like so:

  <head>
    <title>My custom report title</title>
    <frx:category>Articles related Reports</frx:category>
    <frx:options skin="My_Skin_File_With_CSS_And_JS_To_Beaf_Up_My_Rpt"/>
  </head>
  <body>
    <div frx:block="MyDatabaseConnection/MySqlQuery">
      <p frx:foreach="*">
      {title}<Br/>
      {tags}<Br/>
      {body}<Br/>
      </p>
    </div>
  </body>

Consider the above sample as an alternative to what in one of the answers is mentioned, i.e.: "Now if you try and do the above mentioned points in a custom module I am pretty sure you will run over a 500 lines of code to implement most of the features.Also don't forget the custom code which needs to be maintained.". I do agree with that, in cases where Forena is not being considered (eg because not many people seem to know about it?).

Similar thoughts about what I read in another answer, i.e.: "Views brings many additional features to the table as well. For example, theming, caching, access control and integration with hundreds of contributed modules through an easy to swallow UI.". In case of Forena these additional features are available also, except that there are not 100s of such modules (though there are integrations with heavily used modules like feeds, bean, views, date, etc). In case of Forena these few modules will get you pretty far. After all, don't we always try to have as few as possible contributed module enabled? So I'd hope I don't need too much of all those 100 to just enhance the views based approach. And how many sites don't have at least views and date already enabled, probably also bean more and more.

What I don't seem to read anywhere here in the question, or any answers, are these Forena supported features that might make sense to consider also:

• How far do you get with views if the data are coming from an external database?
• What about aggregation types of operations, like the features of the views_calc module? Disclosure: I recently became a legacy maintainer of it's D6 version.

Refer to Forena's community docu mentioned above for details about how Forena does also address those issues (and how it complements views ...). If appropriate, I'd be happy to further expand this answer to explain Forena AbitMORE.

Disclosure: I'm a co-maintainer (not owner ...) of this module,
I hope this does not violate the site's policy on self-promotion.