Re: [development] Views & lots-o-data

7 Oct 2007

      I find that I almost never use full node or teaser views, as they do a full 
node load on each view.  That's extremely expensive.  Instead, almost ever 
view I have is a List View, which is a single query.  It's much faster in 
most cases.

The next thing to do is take the query Views generates and run an EXPLAIN on 
it.  Dollars to donuts you can get a huge performance boost by adding one or 
two carefully placed indexes in your database.

Cheers.

On Sunday 07 October 2007, Ryan Courtnage ☠ wrote:
...
Hi all,
I've just written a node module that implements a view.
First, I got my simple view to display the node title & body - this works.
Next, I add a field that I've exposed from my custom module's table (a
"city" field).  This is where I run into a problem.  My module's
node-type has over 88,000 entries.  The query that the Views module
runs will cause mysql to pin the CPU (I've watched it pin for over 100
minutes before killing the process).
What can I do to improve the perfomance (in a big big way)?  I'm not a
DBA, is there something I should be doing at the DB table level to
improve performance?  Or perhaps there's a Views option I'm missing?
My views implementation follows.  I've commented above the
$view->field array that causes the problem.
Thank you!
Ryan
irc: Bentley
----
function node_charity_views_tables() {
  $table = array(
    'name' => 'node_charity',
    'join' => array(
      'left' => array(
        'table' => 'node',
        'field' => 'nid'
      ),
      'right' => array(
        'field' => 'nid'
      ),
    ),
    'fields' => array(
      'address_city' => array(
        'name' => t('Charity: City'),
        'help' => t('City of Charity'),
        'sortable' => FALSE
      ),
    )
  );
  $tables["node_charity"] = $table;
  return $tables;
}
function node_charity_views_arguments() {
}
function node_charity_views_default_views() {
  $view = new stdClass();
  $view->name = 'Charities1';
  $view->description = 'CCOs';
  $view->access = array (
);
  $view->view_args_php = '';
  $view->page = TRUE;
  $view->page_title = 'Charities';
  $view->page_header = '';
  $view->page_header_format = '1';
  $view->page_footer = '';
  $view->page_footer_format = '1';
  $view->page_empty = '';
  $view->page_empty_format = '1';
  $view->page_type = 'list';
  $view->url = 'charities1';
  $view->use_pager = TRUE;
  $view->nodes_per_page = '50';
  $view->sort = array (
  );
  $view->argument = array (
  );
  $view->field = array (
    array (
      'tablename' => 'node',
      'field' => 'title',
      'label' => '',
      'handler' => 'views_handler_field_nodelink',
      'options' => 'link',
    ),
    array (
      'tablename' => 'node',
      'field' => 'body',
      'label' => '',
      'handler' => 'views_handler_field_body',
    ),
    // the following array causes cpu to pin for hours.
    array (
      'tablename' => 'node_charity',
      'field' => 'address_city',
      'label' => '',
    ),
  );
  $view->filter = array (
    array (
      'tablename' => 'node',
      'field' => 'type',
      'operator' => 'OR',
      'options' => '',
      'value' => array (
        0 => 'node_charity',
      ),
    ),
  );
  $view->exposed_filter = array (
  );
  $view->requires = array(node, node_charity);
  $views[$view->name] = $view;
return $views;
}
-- 
Larry Garfield			AIM: LOLG42
larry@garfieldtech.com		ICQ: 6817012

"If nature has made any one thing less susceptible than all others of 
exclusive property, it is the action of the thinking power called an idea, 
which an individual may exclusively possess as long as he keeps it to 
himself; but the moment it is divulged, it forces itself into the possession 
of every one, and the receiver cannot dispossess himself of it."  -- Thomas 
Jefferson