By betarobot on

Hi,

I use this code at my photoblog (http://andreys.info) to show all submitted photos: 

<?php
$list_length = 1000;
$sql = "SELECT node.type, node.nid FROM node WHERE node.type = 'image' ORDER BY node.nid DESC LIMIT $list_length";
$result = db_query($sql);
while ($anode = db_fetch_object($result)) {
$output .= node_view(node_load(array('nid' => $anode->nid)), $teaser = TRUE, $page = FALSE, $links = FALSE);
}
print $output;
?>

But average page generation time is awful. It's more then 11 sec (for just slightly more then 100 nodes)...

Do you have an idea how to optimize/modify the code to make it faster?

Thanks!

⋅ Categories: Drupal 4.7.x

Comments

morbus iff’s picture

morbus iff
they/them
commented

Why are you loading 1000 rows from the database, then node_load()ing 1000 nodes, when you're only showing 9 on your front page?

http://www.disobey.com/
http://www.gamegrene.com/
Developer of Drupal's GameAPI

http://www.disobey.com/

betarobot’s picture

betarobot commented

Thanks! I'm blind :) but the code about this page: http://andreys.info/all-photos And it has more then 9 nodes to list.

I removed this 1000 limit for now. We'll see how it will go now.

Cheers,
Andrey

Drupal Sites Showcase. Add yours! | My Blog

nevets’s picture

nevets commented

The first time I load the page it is slow, if I refresh the page it is much faster. So some of the time issue maybe the number of photos. I would try using the devel module as it will allow you to see the page load time and also all the sql queries with times which should help narrow things down.

Side note: if you every really try and load 1000 nodes on one page, that will be sloooow.

Budrick’s picture

Budrick commented

1. Try

 while ($anode = db_fetch_object($result)) {
print node_view(node_load(array('nid' => $anode->nid)), $teaser = TRUE, $page = FALSE, $links = FALSE);
}

2. Turn on cache

betarobot’s picture

betarobot commented

This one was a bit faster but about the same (8000ms).

As for cache... unfortunately I can't turn it on as random images will not work then. I didn't find any workaround for this yet. Basically with several dozen of daily visits it's ok to live without cache.

custom drupal themes | my photoblog

Cheers,
Andrey

Drupal Sites Showcase. Add yours! | My Blog

ufku’s picture

ufku commented

here is a probably faster query but it's not the proper way since it ignores load and nodeapi hooks that node_load runs. It gets all image nodes in a single query. There may be an even faster way that gets all thumbnail images from files table and prints each of them inside a link that points to url(node/f.nid).

$sql = "SELECT n.nid, n.vid, n.type, n.status, n.created, n.changed, n.comment, n.promote, 
n.moderate, n.sticky, r.timestamp AS revision_timestamp, r.title, r.body, r.teaser, r.log, r.format, u.uid,
u.name, u.picture, u.data, f.filepath FROM {node} n INNER JOIN {users} u ON u.uid = n.uid INNER JOIN
{node_revisions} r ON r.vid = n.vid INNER JOIN {files} f ON n.nid = f.nid WHERE n.type = 'image' AND
f.filename = 'thumbnail'";
$result = db_query($sql);
while ($node = db_fetch_object($result)) {
  $node->images = array('thumbnail' => $node->filepath);
  print node_view($node, TRUE, FALSE, FALSE);
}

(Some of the attributes in the query may be useless. i got the exact query from node_load().)

--
Geneticists from METU

betarobot’s picture

betarobot commented

Wow! Thank you! This one is dramatically faster! Page generated in less then 1000ms. (You can check older version here: http://andreys.info/all3)

This is not so neat/API way of course. But the page generated is quite extreme by itself. It's a rare case when you need to output hundreds of nodes in one page. So this approach is probably quite adequate.

And one more thing: this code prints latest thumbnail first which is not the best idea for a blog. Here is small modification: 

<?php
$sql = "SELECT n.nid, n.vid, n.type, n.status, n.created, n.changed, n.comment, n.promote, 
n.moderate, n.sticky, r.timestamp AS revision_timestamp, r.title, r.body, r.teaser, r.log, r.format, u.uid,
u.name, u.picture, u.data, f.filepath FROM {node} n INNER JOIN {users} u ON u.uid = n.uid INNER JOIN
{node_revisions} r ON r.vid = n.vid INNER JOIN {files} f ON n.nid = f.nid WHERE n.type = 'image' AND
f.filename = 'thumbnail' ORDER BY n.nid DESC";
$result = db_query($sql);
while ($node = db_fetch_object($result)) {
  $node->images = array('thumbnail' => $node->filepath);
  print node_view($node, TRUE, FALSE, FALSE);
}
?>

(I'll filter out unnecessary query attributes later.)

custom drupal themes | my photoblog

Cheers,
Andrey

Drupal Sites Showcase. Add yours! | My Blog