[Simon]

Using b9.5 - I was adding some test comments to an install, and checking query usage with the internal counter. Upon adding a new comment, the count rose by 11 queries! If this is accurate (can't confirm) then this is very inefficient.

Can anyone confirm how many queries are being used to select and display comments, and if it is anywhere near 10 per comment, I suggest this is the next area to be optimised! I can't believe it is that high, but I don't want to take any chances.

Example here - 54 queries counted, just 3 comments

Looking at this function in story.php, it seems to be selecting all comment_ids for a particular story, then doing a $comment->read() on every comment id selected. Then there are child comments too....

function get_comments (){
    Global $db, $main_smarty, $current_user, $CommentOrder, $link;
    
    //Set comment order to 1 if it's not set in the admin panel
    if(!isset($CommentOrder)){$CommentOrder = 1;}
    If ($CommentOrder == 1){$CommentOrderBy = "comment_votes DESC, comment_date DESC";}
    If ($CommentOrder == 2){$CommentOrderBy = "comment_date DESC";}
    If ($CommentOrder == 3){$CommentOrderBy = "comment_votes DESC, comment_date ASC";}
    If ($CommentOrder == 4){$CommentOrderBy = "comment_date ASC";}
    
    // get all parent comments
  $comments = $db->get_col("SELECT comment_id FROM " . table_comments . " WHERE comment_link_id=$link->id and comment_parent = 0 ORDER BY " . $CommentOrderBy);
  if ($comments) {
    require_once(mnminclude.'comment.php');
    $comment = new Comment;
    foreach($comments as $comment_id) {
      $comment->id=$comment_id;
      $comment->read();
      $comment->print_summary($link);            
    
            // get all child comments
            $comments2 = $db->get_col("SELECT comment_id FROM " . table_comments . " WHERE comment_parent=$comment_id ORDER BY " . $CommentOrderBy);
            if ($comments2) {
                echo '<div style="margin-left:40px">';
                require_once(mnminclude.'comment.php');
                $comment2 = new Comment;
                foreach($comments2 as $comment_id) {
                    $comment2->id=$comment_id;
                    $comment2->read();
                    $comment2->print_summary($link);
                }
                echo "</div>\n";
            }
    
         }
  }
}

Could all comment data be grabbed in a single query, like is done with the story data on the front page now?

[AshDigg]

Current comment code is inefficient. I will be spending time on it before 1.0 is released. thanks

[Simon]

I might try a quick fix myself - although i'll ignore the child comments for now (just display them all as regular) and try and grab all data in 1 go.

[Simon]

After displaying all queries executed to display a single comment, we can see the following:

SELECT comment_id FROM pligg_comments WHERE comment_link_id=1 and comment_parent = 0 ORDER BY comment_votes DESC, comment_date ASC
SELECT * FROM pligg_comments WHERE comment_id = 1
SELECT user_login FROM pligg_users WHERE user_id = 4
SELECT user_email FROM pligg_users WHERE user_id = 4
SELECT user_login FROM pligg_users WHERE user_id = 4
SELECT user_email FROM pligg_users WHERE user_id = 4
SELECT count(*) FROM pligg_votes WHERE vote_type='comments' AND vote_link_id=1 AND vote_value <> 0 AND vote_user_id=0 AND vote_ip='81.107.90.14'
SELECT user_login FROM pligg_users WHERE user_id = 4
SELECT user_email FROM pligg_users WHERE user_id = 4
SELECT user_login FROM pligg_users WHERE user_id = 4
SELECT user_email FROM pligg_users WHERE user_id = 4
SELECT * FROM pligg_users WHERE user_login='Test3'

Ouch!

EDIT: i've reduced the count a bit by combining the user_login and user_email requests into a single query (saves 4 queries per comment). However, I've noticed a number of repeat queries being displayed, such as in the link below:

http://www.hyiperway.com/advice/iceb..._network_blog/

EDIT2: first thing i've done is to make a global variable out of the username:

    function username() {
        global $db, $username;
        if($username != '') { 
            return $username; 
        } else {
            $user = $db->get_row("SELECT user_login,user_email FROM " . table_users . " WHERE user_id = $this->author");
            $this->username = $user->user_login;
            $this->author_email = $user->user_email;
            $username = $this->username;
            return $username;
        }
    }

Then add the global variable to the fill_smarty() function, and finally reset $username after each comment run. Down to 5 queries per comment now.

[Simon]

Some of the functions such as get_avatar() are calling user.php - and in some instances running the same query multiple times during a page load (sometimes 3 or 4 times).

[savant]

http://pligg.svn.sourceforge.net/vie...&revision=1057

When $this->username() is called the first time, it gets the username and email. so we don't need to keep calling the db.

$this->username() has 2 db calls and called 4 times, total 8 db calls per comment.

Now it should be just 1 db call from $this->username();

[savant]

I just realized rev 1057 does quite a good job.

I just implemented CommentsManager. It's like a wrapper around the comment class. It basically uses 1 db call to get all the information for the comments and puts it in the comments object. It also gets the username and email of the person commenting.

To implement it's pretty eas, only story.php had to be changed.

So here are the files.

class.commentmanager.php goes in /lib/ class.commentmanager.php
story.php goes in / (replace old one) story.php

To get best results, please apply 1057 as well.

in story.php, i was surprised i didn't have to make too much changes

PHP Code:

    $comments = new CommentManager(&$db);
    $comments->link_id = $link->id;
    $comments->order_by_index = $CommentOrder;
    $comments->getComments();

    //Is there any comments ??
    if ($comments->count) {
        //get all the parent comments
        $parent_comments = $comments->getParents();

        foreach($parent_comments as $parent_comment) {
            $parent_comment->print_summary();
            // get all child comments
            $child_comments = $comments->getChildren($parent_comment->id);
            if (!empty($child_comments)) {
                echo '<div style="margin-left:40px">';
                foreach($child_comments as $child_comment) {
                    $child_comment->print_summary($link);
                }
                echo "</div>\n";
            }

        }
    } 


Just giving it out to test now, i'll add some comments and commit it to the svn this afternoon

[Simon]

Cool, thanks for that, I'll try it out now.

Rev 1057: knocked us down to 5 queries per comment
Extra Changes: down to just 3 queries per comment!