DB performance

[Roccivic]

I filed a bug report about the forum being slow a while ago and since
then I had a quick look around and I'm finding that the database usage
is not optimal.

I wrote a small function that can be used to cache the results of some
static queries (during a pageload, not in between different pageloads)
and I switched some of the queries (obviously just the static ones)
from gf_showtopic.php to use it. In my tests I saw an improvement of
10 to 20% in the time taken by the forum to display it's content.

Here's the patch: http://www.placella.com/gl/forum-db.diff.gz

Let me know what you make of it.

Rouslan

[Joe Mucchiello]

This could be done in one shot:

- if (DB_count( $_TABLES['sessions'], 'uid', $showtopic['uid']) > 0 AND
DB_getItem($_TABLES['userprefs'],'showonline',"uid={$showtopic['uid']}") == 1) {
- $avatar .= '<br' . XHTML . '>' .$LANG_GF01['STATUS']. ' '
.$LANG_GF01['ONLINE'];
+ $sessions = forum_staticQuery("SELECT COUNT(*) FROM
{$_TABLES['sessions']} WHERE uid='{$showtopic['uid']}'");
+ if ($sessions > 0) {
+ $showonline = forum_staticQuery("SELECT showonline FROM
{$_TABLES['userprefs']} WHERE uid='{$showtopic['uid']}'");
+ if ($showonline == 1) {
+ $avatar .= '<br' . XHTML . '>' .$LANG_GF01['STATUS']. ' '
.$LANG_GF01['ONLINE'];
+ } else {
+ $avatar .= '<br' . XHTML . '>' .$LANG_GF01['STATUS']. ' '
.$LANG_GF01['OFFLINE'];
+ }

$showonline = forum_staticQuery("SELECT showonline FROM $_TABLES['userprefs'] "
. "WHERE uid = '{$showtopic['uid']}' "
. "AND EXISTS (SELECT * FROM $_TABLES['sessions'] WHERE uid =
'{$showtopic['uid']}')", false);

Overall, I'm not a big fan of SQL caching in this manner. I'd prefer localized
functions that do only one thing:

function showonline($uid)
{
static $_cache;
global $_TABLES;

$uid = intval($uid);
if ($uid < 2) return false;
if (!array_key_exists($uid, $_cache))
{
$select = DB_getItem($_TABLES['userprefs'], 'showonline', "uid = '$uid' and
EXISTS (SELECT * FROM $_TABLES['sessions'] WHERE uid = '$uid')");
$_cache[$uid] = intval($select);
}
return $_cache[$uid];
}

The cache inside the function stores an int with int indexes. The function does
one thing and it does it well. And its performance degrades with the

number of distinct users instead of degrading based on random numbers of queries
made.
Joe

> --
> You received this message because you are subscribed to the Google Groups
>"Geeklog Forum" group.
> To post to this group, send email to geeklo...@googlegroups.com.
> To unsubscribe from this group, send email to
>geeklog-foru...@googlegroups.com.
> For more options, visit this group at
>http://groups.google.com/group/geeklog-forum?hl=en.
>
>

[Rouslan Placella]

Hey Joe,

Yeah, thanks, that makes a whole load more sense then what I had in
mind.

Rouslan