Disabling pager in view jacks the views query time
I've got an interesting problem with pager_query and views_build_view query execution time.
http://www.cctvcambridge.org/community
I'm running a complicated view that basically shows all nodes that have been recently posted, edited, or commented. The list view has about seven CCK fields from Bio module nodes and normal nodes. The execution time on the page is disastrous between 8000ms and 11000ms so I am trying to speed it up without sacrificing too much of the feature.
With Devel I could see that the biggest queries were more than 4000ms, both for the pager_query operation. I searched the forums and didn't find any great explaination, so instead I disabled the pager from the view to see if the speed of the whole view page would be faster.
http://www.cctvcambridge.org/files/temp_pager.png
It wasn't. The long pager_query time was replaced by a longer views_build_view time. I don't know why.
http://www.cctvcambridge.org/files/temp_views.png
I mean, in the end I just want this view's resulting page to load faster, but there is some stuff that isn't making sense to me about the two operations above.
I understand Views should cache itself, but not so much when pulling custom views consisting of primarily CCK fields. I like the looks of this approach below but I'm behind the PHP skill curve so I am still experimenting with it: http://drupal.org/node/67502
So here are my questions:
1. Can I do something to reduce the pager_query execution time?
2. If not, can I drop the pager and reduce the views_build_view execution time?
3. Can I hit this with a bigger hammer and do it all differently to get the same result?
Actual SQL query
It would be good if you could paste the actual SQL here, as it would appear in the devel list.
But in general, it makes sense that the query is slower without pager (provided that you mean that you now get all results and don't set a limit to it).
Once you have the final SQL, run that against your database directly via some DB tool (most hosts also offer phpMyAdmin or something like that). That way you take Drupal out of the equation and can focus on just that query.
I believe the caching in views 1 is not so much about caching the result set itself, but the SQL query itself after it has been assembled. On our site we use hundreds of views and we built our own small cache system into the views_build_view() function, which essentially then caches the final output of a view, next time not really doing any processing at all....the performance benefit is huge.
However, that approach fits into our use of views, it wouldn't fit everywhere.
ourbrisbane.com
queries
Ok, easy enough. Maybe this information will be more telling.
This first two queries listed below are the longest execution times, with the pager enabled. From phpmyadmin the execution looks like this: Showing rows 0 - 19 (20 total, Query took 4.7361 sec)
pager_query
SELECT DISTINCT(node.nid), node_comment_statistics.last_comment_timestamp AS node_comment_statistics_last_comment_timestamp_last_comment_timestamp, bio_node_data_field_userimage.field_userimage_fid AS bio_node_data_field_userimage_field_userimage_fid, bio_node_data_field_userimage.field_userimage_title AS bio_node_data_field_userimage_field_userimage_title, bio_node_data_field_userimage.field_userimage_alt AS bio_node_data_field_userimage_field_userimage_alt, node.title AS node_title, node.changed AS node_changed, users.name AS users_name, users.uid AS users_uid, node.type AS node_type, node_comment_statistics.last_comment_timestamp AS node_comment_statistics_last_comment_timestamp, node_comment_statistics.comment_count AS node_comment_statistics_comment_count, bio.nid AS bio_nid FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid AND term_node.tid IN ('872','873','883','878') LEFT JOIN node_comment_statistics node_comment_statistics ON node.nid = node_comment_statistics.nid LEFT JOIN node bio ON node.uid = bio.uid AND bio.type = 'profile' LEFT JOIN content_type_profile bio_node_data_field_userimage ON bio.vid = bio_node_data_field_userimage.vid INNER JOIN users users ON node.uid = users.uid WHERE (node.status = '1') AND (node.type IN ('blog','audio','product','video','article','date','group','page','profile')) AND (term_node.tid IS NULL) ORDER BY node_comment_statistics_last_comment_timestamp_last_comment_timestamp DESC LIMIT 0, 20pager_query
SELECT count( DISTINCT(node.nid)) FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid AND term_node.tid IN ('872','873','883','878') LEFT JOIN node_comment_statistics node_comment_statistics ON node.nid = node_comment_statistics.nid LEFT JOIN node bio ON node.uid = bio.uid AND bio.type = 'profile' LEFT JOIN content_type_profile bio_node_data_field_userimage ON bio.vid = bio_node_data_field_userimage.vid INNER JOIN users users ON node.uid = users.uid WHERE (node.status = '1') AND (node.type IN ('blog','audio','product','video','article','date','group','page','profile')) AND (term_node.tid IS NULL)This is the query as found in the Devel logs for the view on the page after the pager is disabled. It completes at about the same speed through phpmyadmin as when run through Drupal page loads.
views_build_view
SELECT DISTINCT(node.nid), node_comment_statistics.last_comment_timestamp AS node_comment_statistics_last_comment_timestamp_last_comment_timestamp, bio_node_data_field_userimage.field_userimage_fid AS bio_node_data_field_userimage_field_userimage_fid, bio_node_data_field_userimage.field_userimage_title AS bio_node_data_field_userimage_field_userimage_title, bio_node_data_field_userimage.field_userimage_alt AS bio_node_data_field_userimage_field_userimage_alt, node.title AS node_title, node.changed AS node_changed, users.name AS users_name, users.uid AS users_uid, node.type AS node_type, node_comment_statistics.last_comment_timestamp AS node_comment_statistics_last_comment_timestamp, node_comment_statistics.comment_count AS node_comment_statistics_comment_count, bio.nid AS bio_nid FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid AND term_node.tid IN ('872','873','883','878') LEFT JOIN node_comment_statistics node_comment_statistics ON node.nid = node_comment_statistics.nid LEFT JOIN node bio ON node.uid = bio.uid AND bio.type = 'profile' LEFT JOIN content_type_profile bio_node_data_field_userimage ON bio.vid = bio_node_data_field_userimage.vid INNER JOIN users users ON node.uid = users.uid WHERE (node.status = '1') AND (node.type IN ('blog','audio','product','video','article','date','group','page','profile')) AND (term_node.tid IS NULL) ORDER BY node_comment_statistics_last_comment_timestamp_last_comment_timestamp DESC LIMIT 0, 20With Drupal out of the equation does this point me to performance issues with the database server itself, or are these queries just unnecessarily complex? I don't have a lot experience mysql and certainly don't know how to navigate database server performance, so any advice you have would be seriously appreciated!
Also, I've been cursing views for not caching results. It's a bummer. Can you point me to your custom caching technique because that sounds like an awesome thing. Thanks!
Caching views
Although I don't know the row counts of those tables that are queried, it does seem like a long time - so yes, MySQL config or other environmental settings could certainly be an issue.
I used custom caching in the views_build_view() function, which generates the final output of a view...basically when a it gets called it checks whether the output from that view is in the cache and if so returns that previously generated blob, otherwise it'll do what it always does.
So yes, you have to hack the module. Just use Drupal's built-in cache functions cache_get() and cache_set()...
Here's a simple example...the main point is to generate the right id for the cached item, which depends on what sort of views you have. If you don't use arguments and don't have views that are only visible to certain roles, this might be just a string with the view name, the type and the page...but it can also get more complex...I've put a few examples in there to give you an idea.
In our case this is far more complex and we built an interface to administer all the possible variables such as cache lifetime, an option to exclude certain views from this alltogether etc...but that's very specific to our usage of it...
Hope this helps, let me know if I should clarify anything.
<?php
function views_build_view($type, &$view, $args = array(), $use_pager = false, $limit = 0, $page = 0, $offset = 0, $filters = NULL) {
// START EDIT #####################################
// A very simple cache ID...name and view type.
$view_cache_id = $view->name .'|'. $type;
// If roles need to be taken into account, this needs to be reflected in the cache ID.
global $user;
$view_cache_id .= '|'. implode('+',$user->roles);
// Pager views or exposed filters need to be taken into account as well...
if ($type == 'page' && strpos($_SERVER[REQUEST_URI], '?')) {
$qstr = explode('?', $_SERVER[REQUEST_URI]);
$view_cache_id .= $qstr[1];
}
// Arguments...
if (count($args)) {
$view_cache_id .= serialize($args);
}
if ($cache = cache_get($view_cache_id, 'cache')) {
$output = $cache->data;
return $output;
}
// END EDIT #####################################
views_load_cache();
// Fix a number of annoying whines when NULL is passed in..
if ($args == NULL) {
$args = array();
}
// if no filter values are passed in, get them from the $_GET array
if ($filters == NULL) {
$filters = views_get_filter_values();
}
views_set_current_view($view);
$view->build_type = $type;
$view->type = ($type == 'block' ? $view->block_type : $view->page_type);
if ($view->view_args_php) {
ob_start();
$result = eval($view->view_args_php);
if (is_array($result)) {
$args = $result;
}
ob_end_clean();
}
$view->use_pager = $use_pager;
$view->pager_limit = $limit;
$view->current_page = $page;
$view->offset = $offset;
// Call a hook that'll let modules modify the view query before it is created
foreach (module_implements('views_pre_query') as $module) {
$function = $module .'_views_pre_query';
$output .= $function($view);
}
$info = _views_get_query($view, $args, $filters);
if ($info['fail']) {
return FALSE;
}
if ($type == 'queries') {
return $info;
}
$query = db_rewrite_sql($info['query'], 'node');
$items = array();
if ($query) {
if ($view->use_pager) {
$cquery = db_rewrite_sql($info['countquery'], 'node', 'nid', $info['rewrite_args']);
$result = pager_query($query, $view->pager_limit, $view->use_pager - 1, $cquery, $info['args']);
$view->total_rows = $GLOBALS['pager_total_items'][$view->use_pager - 1];
}
else {
$result = ($view->pager_limit ? db_query_range($query, $info['args'], $view->current_page * $view->pager_limit + $view->offset, $view->pager_limit) : db_query($query, $info['args']));
}
$view->num_rows = db_num_rows($result);
if ($type == 'result') {
$info['result'] = $result;
return $info;
}
while ($item = db_fetch_object($result)) {
$items[] = $item;
}
}
if ($type == 'items') {
$info['items'] = $items;
return $info;
}
// Call a hook that'll let modules modify the view just before it is displayed.
foreach (module_implements('views_pre_view') as $module) {
$function = $module .'_views_pre_view';
$output .= $function($view, $items);
}
$view->real_url = views_get_url($view, $args);
$output .= views_theme('views_view', $view, $type, $items, $info['level'], $args);
// Call a hook that'll let modules modify the view just after it is displayed.
foreach (module_implements('views_post_view') as $module) {
$function = $module .'_views_post_view';
$output .= $function($view, $items, $output);
}
// START EDIT #####################################
$seconds_to_live = 60 * 60;
$expiry= time() + $seconds_to_live;
cache_set($view_cache_id, 'cache', $output, $expiry);
// END EDIT #####################################
return $output;
}
?>
ourbrisbane.com
...
[EDIT: Deleted. I was adding something to the comment I wrote here yesterday, but instead of clicking "reply" I clicked "edit", so I accidentally obliterated my original text.]