Sidebar: Top today performance

[savant]

Hey

I was looking at how "Top today" bits are done on the sidebar, here is how it works

1. Create a sql query to get link_id for 5 top stories.
2. Run query (1db call)
3. Foreach link_id call $link->read() (5 db calls)
4. Foreach link_id call fill_smarty() (This has quite a few calls: 5 for category name, 5 for comments name )
4.1 On each fill_smarty $s->display("blank.tpl") is called, which takes up a few cycles.

So i set up a basic site (on localhost) and used some timing script(thanks revolver) to see how it loads.

Average time : 1.338937283s

Now i applied some of my code that doesn't do any oop and focuses on performance.

First function.sidebarstories.php

PHP Code:

<?php
function sidebarstories_today()
{
    global $db;
    include_once(mnminclude.'search.php');
    $search=new Search();
    $search->orderBy = "link_votes DESC";
    $search->pagesize = 5;
    $search->filterToStatus = "all";
    $search->filterToTimeFrame = "today";
    $search->doSearch();
    
    //lets create a sql query to get all the values we need rather than link_id only
    $linksum_sql = $search->sql;
    $linksum_sql = str_replace("DISTINCT link_id", "DISTINCT link_id, link_title_url, link_title, link_category", $linksum_sql);
    //fetch the link data we need and store in array
    $links_query = mysql_query($linksum_sql);
    $links = array();
    while ($rows = mysql_fetch_array ($links_query, MYSQL_ASSOC)) array_push ($links, $rows);
    
    //build a sql query to get the categories for all the links
    //this way we call the database only once instead of n times (n = number of links)
    $where_categories = array();
    foreach($links as $link)
    {
        $where_categories[] = "category_id = '" . $link['link_category']. "'";
    }
    $where_cat_text = implode(" OR ", $where_categories);
    //get categories and store in array
    $categories = mysql_query("select category_id, category_safe_name from pligg_categories where $where_cat_text");
    $categorylist = array();
    while ($rows = mysql_fetch_array ($categories, MYSQL_ASSOC)){
        $categorylist[$rows['category_id']] = $rows['category_safe_name'];
    }
    
    //iterate through all the links we have and build an array that will be passed to smarty
    $smarty_sidebarstories_today = array();
    foreach($links as $link)
    {
        if ($link['link_title_url'] == ""){
            $story_url = getmyurl("story", $link['link_id']); // internal link to the comments page
        } else {
            $story_url =  getmyurl("storyURL", $categorylist[$link['link_category']], urlencode($link['link_title_url']),$link['link_id']);
        }
        
        $smarty_sidebarstories_today[] = array(
                                    'story_url' => $story_url,
                                    'title_short' => htmlspecialchars(utf8_wordwrap($link['link_title'], 30, " ", 1))
                                    );                                    
    }
    return $smarty_sidebarstories_today;
}
?>

In do_sidebar() function of html1.php

PHP Code:

//get the top today stories
include_once(mnminclude.'function.sidebarstories.php');
$var_smarty->assign('sidebarstories_today', sidebarstories_today()); 


then sidebar_stories_u.tpl

PHP Code:

<div class=tlb>
    <span>
        {literal}<a onclick="new Effect.toggle('sstop','blind', {queue: 'end'});">{/literal}
        <img src="{$my_base_url}{$my_pligg_base}/templates/yget/images/expand.png" onClick=expandcontent(this,'sstop')></a>
    </span>
    <a href="{$my_base_url}{$my_pligg_base}">{#PLIGG_Visual_Top_Today#}</a>
    </div>
<div id=sstop style=padding-bottom:5px>

{section name=story_n loop=$sidebarstories_today}
    <a href="{$sidebarstories_today[story_n].story_url}" class="switchurl">{$sidebarstories_today[story_n].title_short}</a>
{/section}
</div> 


With this changes i was able to get the same results.
and the timing said : 1.088807058s
which is about 18%.

Only problem with this is maintainability. what happens if some db changes or some other code change.

note: you need to change the tables according to prefix)

[dollars5]

Good work, love to see it implemented to the HEAD version of Pligg.