Auto-refreshing views, part II: Optimizing the pings

I wrote last time about the latest developments to my Views Auto-Refresh module, which periodically refreshes a Views page, either by reloading the whole view, or by incrementally inserting new items only. It's a useful tool for activity streams and other Twitter-like, real-time lists.

Still, I had a nagging feeling that my code was endangering the server. Consider this: every 15 seconds, each connected browser invokes a full Drupal bootstrap plus a full View render, just to ask the server if there are new items. This doesn't sound right.

So I came up with a solution: before invoking the secondary View refresh, the client code can use a "ping URL" that more efficiently determines whether there are new items or not. If yes, then full View render is invoked. The idea of the ping URL is that it can be implemented with any technology, not necessarily Drupal - and can be arbitrarily optimized.

The result is now committed to the latest Views Hacks dev. Here's how to use it:

An additional theme('views_autorefresh') parameter called ping_base_path is used to inform Views Auto-Refresh that some URL should be hit before the secondary view is invoked:

print theme('views_autorefresh', 5000, array(
  'ping_base_path' => drupal_get_path('module', 'liveblog') . '/liveblog.php?type=report, // ping URL (optional)
), array(
  'view_base_path' => 'liveblog/autorefresh', // the path of the update view
  'view_display_id' => 'page_2', // the display of the update view
  'view_name' => 'liveblog', // the name of the update view
  'sourceSelector' => '.view-content', // selector for items container in update view
  'targetSelector' => '.view-content', // selector for items container in on-page view
  'firstClass' => 'first', // class name for first element (optional),
  'lastClass' => 'last', // class name for last element (optional),
  'oddClass' => 'odd', // class name for odd elements (optional)
  'evenClass' => 'even', // class name for even elements (optional)
));

The script at this ping URL should accept a GET parameter called timestamp and return a JSON response of the following form:

array(
'pong' => $count,
);

where $count is 0 if there are no new items since timestamp, > 0 otherwise.

For example, here's a complete ping script:

require('../../../default/settings.php');

global $db_url;
$p = parse_url($db_url);
$mysql = mysql_pconnect($p['host'], $p['user'], @$p['pass']);
if (!$mysql) die('Could not connect to database server.');
mysql_set_charset('utf8', $mysql);
if (!mysql_select_db(str_replace('/', '', $p['path']), $mysql)) die('Could not select database.');

$timestamp = mysql_real_escape_string($_REQUEST['timestamp']);
$type = mysql_real_escape_string($_REQUEST['type']);
$sql = "SELECT COUNT(nid) FROM node WHERE type='" . $type .  "' AND created > " . $timestamp;
if (!empty($_REQUEST['uid'])) {
  $uid = mysql_real_escape_string($_REQUEST['uid']);
  $sql .= " AND uid = " . $uid;
}
$count = mysql_result(mysql_query($sql, $mysql), 0);

print json_encode(array(
  'pong' => $count,
));

This script illustrates parsing the Drupal MySQL connection string, receiving a number of parameters including timestamp, and returning the expected JSON response.

Two considerations are important in this script:

  1. Query parameters should be sanitized before they're used in the SQL query, to avoid SQL injections and other nasty surprises.
  2. The ping SQL query does not have to emulate the Views query exactly. If you make the ping query broader, it will create false positives, whereby the View render is invoked but no actual new items are found. If you make the ping query more restricted, it will create false negatives: no new items will be returned whereas the View would have returned new items. You probably want to avoid this last condition.

That's it! The results on my test server were encouraging: The auto-refresh round-trip time was reduced by an order of magnitude. I haven't measured CPU savings though - I leave it as the proverbial "exercise to the reader" :-)

Comments

I cannot get incremental

I cannot get incremental updates to work...

I created a view to display comments, enabled ajax, added the "simple" code to just refresh the view, then cloned the first view, added a page display with Comment: Post date (with operator) argument to the second view and finally modified the header text in the original view only, adding the input argument needed for incremental updates.

When I test this setup I get that the first view is displayed correctly, then upon first refresh every record is removed; if I add some comments, they gets added to the view, but the timestamp the ajax request is using does not get updated with the response timestamp, so the result is that all the comments submitted after the first refresh are pulled every time the views autorefreshes.

Moreover, if I enable ping the performance is even worse, because with every refresh after the first new comment has been added both the ping and the "incremental" view display are performed.

I am quite sure I have misconfigured the scripts in the first or second view header, but I cannot understand how.

Using Drupal core 6.24, Views 6.x-3.0 (latest dev) and Views Hacks 6.x-1.0-beta2 (latest dev).

Hi there, please file a

Hi there, please file a support ticket to better track your issue. Thanks!

Modified the code here to

Modified the code here to reflect the latest change to the module: the ping_base_path setting is now decoupled from the secondary view. The full signature for the theme function is:

function theme_views_autorefresh(
  $interval,
  $ping = NULL, // or array('ping_base_path')
  $incremental = NULL, // or array() with parameters as described previously
  $view = NULL // meaning views_get_current_view()
);

Isnt there a more Drupal way

Isnt there a more Drupal way of doing this? Maybe using ctools Ajax libraries? I don't believe they invoke the entire bootstrap and they return JSON .... It could just return a one or 0 on the ping and if it was true just return the one with the array of results....

Thoughts?

I should look more into this,

I should look more into this, thanks for the reference.

Clever. Thanks for sharing.

Clever. Thanks for sharing.