By dado on

My site like many is becoming increasingly dependent on CCK and views. Good news- these ar both glorious modules. My trouble- slow views. 1 of my views_build_view queries takes 5 seconds consistently. Drupal's page caching only works for anonymous users, so I am effectively punishing users who register for account with slow response time

OK, yes it is a 5 table query. But curious- has anyone identified a way to cache pages or queries for logged in users, or to cache views queries?

⋅ Categories: Drupal 4.7.x

Comments

dado’s picture

dado commented

OK, so I still think some mechanism or module to selectively cache views_build_view queries would be nifty. But here is the best solution for slow multitable views queries, when using CCK nodes. It basically makes use of CCK's node caching, and it made my execution time of views_build_view drop from 4800ms to 13ms!

(1) install the devel module and enable tracking query execution times. Pay attention to the time for views queries, especially views_build_view
(2) export/import your slow view, to make a copy of it. Give it new name.
(3) In your new slow view, remove all fields for your view except the node title.
(4) create a custom views theme function through which to render your view, as per here
http://drupal.org/node/42597
(5) In your loop rendering your view, load the CCK node. You can then render any/all fields that the node possesses very cheaply.

Example code: SLOWER

/**
* Display the nodes of a view as a list.  Slower!
*/
function theme_views_view_list($view, $nodes) {
  $output = '';
  foreach ($nodes as $node) {
    $output .= '<br/><br/>Title: ' . $node->node_title;
    $output .= '<br/>City: ' . $node->location_city;
    $output .= '<br/>Description: ' . $node->field_data_field_description_field_description_value;
  }
  return $output;
}

Example code: FASTER

/**
* Display the nodes of a view as a list.  Faster!
*/
function theme_views_view_list($view, $nodes) {
  $output = '';
  foreach ($nodes as $base_node) {
    $node = node_load($base_node->nid);
    $output .= '<br/><br/>Title: ' . $node->title;
    $output .= '<br/>City: ' . $node->location['city'];
    $output .= '<br/>Description: ' . $node->field_description[0]['value'];
  }
  return $output;
}

dado

dado’s picture

dado commented

you can also do this to optimize your complex multitable views

(1) In your new slow view, select view as teaser list
(2) remove all fields for your view except the node title.
(3) if this is a view of CCK nodes: because CCK teasers are problematic at this writing, you might need to implement Nick Lewis' or other workaround to get proper teasers.
http://www.nicklewis.org/node/844

dado

seaneffel’s picture

seaneffel commented

dado, I know its been 2+ years, but what are the changes I need to make to this example above to make it work for Drupal 5. I have an issue with views execution timeL http://drupal.org/node/284416

giorgio79’s picture

giorgio79 commented

Thanks, I am just going to try it out now. My CCK views taxonomy query is killing my site...


100824.83	0	views_build_view	SELECT node.nid, node.sticky AS node_sticky, node.created AS node_created_created, node_data_field_embed_code.field_embed_code_embed AS node_data_field_embed_code_field_embed_code_embed, node_data_field_embed_code.field_embed_code_value AS node_data_field_embed_code_field_embed_code_value, node_data_field_embed_code.field_embed_code_provider AS node_data_field_embed_code_field_embed_code_provider, node_data_field_embed_code.field_embed_code_data AS node_data_field_embed_code_field_embed_code_data, node.title AS node_title, node.changed AS node_changed, users.name AS users_name, users.uid AS users_uid, node_counter.totalcount AS node_counter_totalcount, node.created AS node_created FROM drupal_node node LEFT JOIN drupal_term_node term_node ON node.nid = term_node.nid LEFT JOIN drupal_term_hierarchy term_hierarchy ON term_node.tid = term_hierarchy.tid LEFT JOIN drupal_term_hierarchy term_hierarchy2 ON term_hierarchy.parent = term_hierarchy2.tid LEFT JOIN drupal_term_hierarchy term_hierarchy3 ON term_hierarchy2.parent = term_hierarchy3.tid LEFT JOIN drupal_term_hierarchy term_hierarchy4 ON term_hierarchy3.parent = term_hierarchy4.tid LEFT JOIN drupal_term_hierarchy term_hierarchy5 ON term_hierarchy4.parent = term_hierarchy5.tid LEFT JOIN drupal_term_hierarchy term_hierarchy6 ON term_hierarchy5.parent = term_hierarchy6.tid LEFT JOIN drupal_term_hierarchy term_hierarchy7 ON term_hierarchy6.parent = term_hierarchy7.tid LEFT JOIN drupal_term_hierarchy term_hierarchy8 ON term_hierarchy7.parent = term_hierarchy8.tid LEFT JOIN drupal_term_hierarchy term_hierarchy9 ON term_hierarchy8.parent = term_hierarchy9.tid LEFT JOIN drupal_term_hierarchy term_hierarchy10 ON term_hierarchy9.parent = term_hierarchy10.tid LEFT JOIN drupal_term_hierarchy term_hierarchy11 ON term_hierarchy10.parent = term_hierarchy11.tid LEFT JOIN drupal_content_type_video_embed node_data_field_embed_code ON node.vid = node_data_field_embed_code.vid INNER JOIN drupal_users users ON node.uid = users.uid LEFT JOIN drupal_node_counter node_counter ON node.nid = node_counter.nid WHERE (node.type IN ('video_embed','video_upload')) AND (term_node.tid = '6295630' OR term_hierarchy2.tid = '6295630' OR term_hierarchy3.tid = '6295630' OR term_hierarchy4.tid = '6295630' OR term_hierarchy5.tid = '6295630' OR term_hierarchy6.tid = '6295630' OR term_hierarchy7.tid = '6295630' OR term_hierarchy8.tid = '6295630' OR term_hierarchy9.tid = '6295630' OR term_hierarchy10.tid = '6295630' OR term_hierarchy11.tid = '6295630') ORDER BY node_sticky DESC, node_created_created ASC LIMIT 0, 20
22.33	0	views_handler_arg_taxid	SELECT name FROM drupal_term_data WHERE tid IN (6295630)

My Drupal sites: