By mritz_p on

Hello Everybody,

my Drupal 6 site is especially slow displaying a particular Taxonomy Page edited with the great plug-in "Views". This page displays 57 nodes. The nodes are ordered alphabetically. Below is the "View" query analysis:

	Query:

SELECT node.nid AS nid,
   node_data_field_personname.field_personname_value AS node_data_field_personname_field_personname_value
 FROM node node 
 INNER JOIN term_node term_node ON node.vid = term_node.vid
 LEFT JOIN content_type_person node_data_field_personname ON node.vid = node_data_field_personname.vid
 WHERE (term_node.tid = 2) AND (node.status <> 0)
   ORDER BY node_data_field_personname_field_personname_value ASC


	Query build time:
	407.43 ms

	Query execute time:
	10.41 ms

	View render time:
	20254.24 ms

21 seconds is far beyond acceptable for the display of the page, especially considering that the number of displayed nodes will grow in the next days reaching its maximum around 350.

My question is how i can boost the performance of this page from a drupal and server ressources point of view.

Thank you.

⋅ Categories: Drupal 6.x

Comments

nevets’s picture

nevets commented

Seems slow, to check out something try enabling the view taxonomy_term

Edit the view and preview with the same tid (2).

How do the times compare?

A side note, listing 350 full nodes on one page (without paging) will tend to be slow no matter what.

mritz_p’s picture

mritz_p commented

try enabling the view taxonomy_term

Sorry. I don't get it. The view is enabled for that taxonomy term.

A side note, listing 350 full nodes on one page (without paging) will tend to be slow no matter what.

I'm aware I'm giving the server a hard time. The teasers displayed are very small in terms of text lenght (names and details of supporters with link to the node). But they are most likely producing big db load since they are themed with "Content Template" (output of 6 CCK fields using 2 if-statements and 3 isempty-statements) and thrown in by "Views" row-style.

Thats why I'm looking for a creative solution in drupal (ruthless caching methods) and/or for what to tell my host to upgrade.

nevets’s picture

nevets commented

The view you show is not the taxonomy_term view that comes with views, I am wondering how much a difference the extra join costs.

The other thing to try is save your template information and disable the content template for the content type and see what the performance looks like.

Or from another point of view, with the "content template" disabled, create a new view that list nodes for the content type with the given tid (no extra sorting). See how it performs. Add the sort field, how do the times compare. Add the "content termplate" back, how do the times compare.

Regardless of the approach the goal here is to compare a basic views performance against one with the sort criteria and the "content template" looking for where the "extra" time comes in.

mritz_p’s picture

mritz_p commented

Thank you very much!

In the meantime overall server load changed so i analyzed the query from my original post again (5 tries):

Query build time:
avg: 152 ms
Query execute time:
avg: 5.6 ms
View render time:
avg: 14401 ms

Taxonomy term, ordered by date published (5 tries)

Query:
SELECT node.nid AS nid,
   node.sticky AS node_sticky,
   node.created AS node_created
 FROM node node 
 WHERE (node.status <> 0 OR node.uid = ***CURRENT_USER*** or ***ADMINISTER_NODES*** = 1) AND (node.vid IN (
  SELECT tn.vid FROM term_node tn
  WHERE tn.tid  = 2
  ))
   ORDER BY node_sticky DESC, node_created DESC

Query build time:
avg: 149 ms
Query execute time:
avg: 5.8 ms
View render time:
avg: 15906 ms

Taxonomy term, ordered alphabetically by field (5 tries)

Query:
SELECT node.nid AS nid,
   node_data_field_personname.field_personname_value AS node_data_field_personname_field_personname_value
 FROM node node 
 LEFT JOIN content_type_person node_data_field_personname ON node.vid = node_data_field_personname.vid
 WHERE (node.status <> 0 OR node.uid = ***CURRENT_USER*** or ***ADMINISTER_NODES*** = 1) AND (node.vid IN (
  SELECT tn.vid FROM term_node tn
  WHERE tn.tid  = 2
  ))
   ORDER BY node_data_field_personname_field_personname_value ASC

Query build time:
avg: 150 ms
Query execute time:
avg: 8.2 ms
View render time:
avg: 13911 ms

I dont see a significant difference here.

However displaying fields instead of nodes gives a huge speed boost:

Page, ordered alphabetically by field, output as fields (5 tries)

Query:
SELECT node.nid AS nid,
   node.title AS node_title,
   node_data_field_personalter.field_personalter_value AS node_data_field_personalter_field_personalter_value,
   node_data_field_personalter.nid AS node_data_field_personalter_nid,
   node.type AS node_type,
   node_data_field_personalter.field_personberuf_value AS node_data_field_personalter_field_personberuf_value,
   node_data_field_personalter.field_foto_value AS node_data_field_personalter_field_foto_value,
   node_data_field_personalter.field_mitglied_value AS node_data_field_personalter_field_spdmitglied_value,
   node_data_field_personalter.field_stadtteil_value AS node_data_field_personalter_field_tempelhofschoeneberg_value,
   node_data_field_personalter.field_personname_value AS node_data_field_personalter_field_personname_value,
   node_data_field_personalter.field_personort_value AS node_data_field_personalter_field_personort_value,
   node_data_field_personalter.field_personorganisation_value AS node_data_field_personalter_field_personorganisation_value,
   node_data_field_personalter.field_personvorname_value AS node_data_field_personalter_field_personvorname_value,
   node_data_field_personname.field_personname_value AS node_data_field_personname_field_personname_value
 FROM node node 
 INNER JOIN term_node term_node ON node.vid = term_node.vid
 LEFT JOIN content_type_person node_data_field_personalter ON node.vid = node_data_field_personalter.vid
 LEFT JOIN content_type_person node_data_field_personname ON node.vid = node_data_field_personname.vid
 WHERE (term_node.tid = 2) AND (node.status <> 0)
   ORDER BY node_data_field_personname_field_personname_value ASC

Query build time:
avg: 107 ms
Query execute time:
avg: 11.45 ms
View render time:
avg: 2516 ms

Sadly I can't use the field output because I need php statements.

dnewkerk’s picture

dnewkerk commented

Sadly I can't use the field output because I need php statements.

Yes, I think you can. The key is to use the correct Views template file where you will have access to the individual fields in the same way as on a custom-themed node template.

I didn't use php statements in mine, but I don't think there's a reason you can't... here's the content of my file views-view-fields--forum-content.tpl.php (which is the 2nd file name option on the "Display output" template... forum_content is the name of the view itself in my case).

This is a custom Views template with all desired fields specifically listed, instead of having Views run a loop to print them automatically. This prevents the need to use separate template files for each row, since the rows can be fully styled here. See: http://views-help.doc.logrus.com/help/views/using-theme

Since my View is completely set in stone how I want it to display, I erased all non-relevant code from the template file and replaced it with only this (though you could alternately insert this kind of code in place of <?php print $rows; ?> and leave the rest so that all of the optional Views functionality will still show, such as header/footer, attachment displays, etc):

<div class="sidebar-link">
  <?php print $fields['title']->content; ?>
</div>

<div class="block-list-info">
  Posted <?php print $fields['created']->content; ?>
</div>

Hope this helps.

-- David
davidnewkerk.com | absolutecross.com
View my Drupal lessons & guides

mritz_p’s picture

mritz_p commented

This sounds like a proper way of increasing the performance. Thank you very much for your help. I will try it tomorrow. But for now it's time for bed ;-)

mritz_p’s picture

mritz_p commented

Render time is around 2500ms constantly now (that's 8 times faster than the original setup!) with even more nodes on the page.

Thank you very much!

nelslynn’s picture

nelslynn commented

Keyz,

Can you think of any reason why this solution would not work:

Here is my setup. I have set up a content type of 'projects', with 3 fields field_image, field_project_desc, and field_project_title. I included a project taxonomy type for the project. I created a view to show all projects base on type.

In the view, I need to print each field in a very specific way... that being in a JavaScript statement. I therefore need full control of each field. When I try to replace print $row; in my views-view-unformatted.tpl.php template, with print $fields['field_project_desc']->content; , I get no output. When I switch back to print $row; , I get the full output, so everything else is set up correctly as far as the view is concerned.

What am I missing? Any hint will be appreciated!!! I've been struggling on how to dissect the fields of a view now for several days. I'm using Drupal 6.

nevets’s picture

nevets commented

If I read this correctly it is actually the fields you want to theme.

So start by editing the view and click on the "Theme: Information" link. This shows all the ways you can theme the view (including each field). Notice the titles are links, click the title and it shows the default template along with the variables available.

nelslynn’s picture

nelslynn commented

Field theming will not work, as each field needs to be placed in a JavaScript statement like this:

First off, I need a counter to count through each node, so lets call this: $cnt.

foreach (not sure what goes here, but in psudocode: for each node in view) {
$('loadContent echo $cnt; ').addEvent('click', function() { new ImageCollection([' print $fields['field_project_desc']->content; '], 'mainImage', ' print $fields['field_project_title']->content; ', ' print $fields['field_project_desc']->content; '); });
};

Sorry, the JavaScript statement gets split up in this post....I want to output one line of JavaScript with the values of each of the three fields in the node. One line of JavaScript per node.

nevets’s picture

nevets commented

Does your view display nodes or fields?

nelslynn’s picture

nelslynn commented

Nodes.

nevets’s picture

nevets commented

Edit the view, click the "Theme: information" link.

Look at the one labeled, "Row style output", click the label and it will show you the default template for a row. Since you have $node you can access any of the fields. Have this template out put the part from addEvent( to '); });.

Now back to "Theme: information", this time looking at "Style output". This theme file outputs $row, set up a counter to use in the loop and have the body of the loop output print "$('loadContent$cnt')." . $row . ';';.

nelslynn’s picture

nelslynn commented

Thanks for your time on this, but your suggestion will not work. My final output needs to look like:

new Gallery(['FIELD-1'], 'mainImage', 'FIELD-2', 'FIELD_3'); <--- this needs to be content from node1
$('loadContent0').addEvent('click', function() { new Gallery(['FIELD-1'], 'mainImage', 'FIELD-2', 'FIELD-3'); }); <---- node 1 again
$('loadContent0').addEvent('click', function() { new Gallery(['FIELD-1'], 'mainImage', 'FIELD-2', 'FIELD-3'); }); <---- node 2, and so on for 8 nodes.

FIELDS-1, FIELDS-2, and FIELDS-3 are the fields I created in my content type.

I figured out a way to 'almost' make this work... I changed the view display type to field (from node). This allows me to create custom field templates. But, I need the first node fields to print twice. This also seems like a very convoluted way to make this work... is there not a way to pull the field values individually?

dnewkerk’s picture

dnewkerk commented

...is there not a way to pull the field values individually?

That's what I explained how to do above: http://drupal.org/node/368691#comment-1236820
Definitely do use Fields in the view, and call them each out separately like I showed (and as in the documentation link I mentioned). You can certainly print any field more than once if you want (nothing wrong with that). Just write your jQuery, and drop in the PHP field variables where you need them within the javascript.

Your lines 2-3 above are in the views template I mentioned (it represents each node in sequence)... then (I haven't tried this part) look for a higher level template to use to insert that one static row above the "foreach" that prints all the rows normally. Getting that row's data (node 1 in the list) might require a little different code... hopefully someone can point that out for you (or maybe do a print_r in that template and see what it gives you). Hope this helps.

-- David
davidnewkerk.com | absolutecross.com
View my Drupal lessons & guides

nelslynn’s picture

nelslynn commented

Got it, Thanks! ... I could not get a grasp of the field ID required.

So to summarize. First, in your view, you need to use a Row Type of: Fields (not node). If you are trying to reference a standard page field (ie: title, content) you do it this way:
print $fields['title']->content;

For custom fields, you need to find the ID via the Theme: Information link for the view first. So, if you named a custom field something like: field_project, the id of this field may look something like: field_project_value. To individually reference this field in a view, you would use: print $fields['field_project_value']->content;

I was using a row type of node, and kept trying to use the machine name I gave it in the content type to reference it my view. This is wrong: print $fields['field_project']->content;

This can be closed. Thanks all.