Remove 1 Database Query Per Page

Remove 1 Database Query Per Page

Intro:
The idea is, lets remove 1 query from each page load, if the person is just refreshing the page. At first this may sound not important or just plain stupid, but on "larger" forums, cutting 1 query is always welcomed I know.

Description:
Basically all this does, is check the current Session Location from the DB against the current WOL path; the Session is already provided by vbulletin, so no extra query there.

All we do, is simply check the Location from the Database against the current WOL, if they don't match then the query is allowed to update the database; if they are the same - it is simply ignored.

Affects:
The only things this will do, is prevent the updating of the "lastactivity", and "location" if a person keeps refreshing the page, IMO that's not really a big deal, vbulletin takes care of deleting and remaking new sessions when they expire anyway. As long as your members are browsing around and such, their lastactivity, location will be updated as per usual.

Updates:
Version 1.1 - Scripts whosonline, misc are allowed to always update.
Version 1.0 - Release

Instructions:
1. [ open includes/sessions.php ]
2. [ go to about line 354, or look for $bypass = intval(SESSION_BYPASS); ]
3. [ replace this block of code ]

PHP Code:

    $DB_site->shutdown_query("
        UPDATE " . TABLE_PREFIX . "session
        SET useragent = '" . addslashes(USER_AGENT) . "', lastactivity = " . TIMENOW . $location . ", styleid = $styleid" . iif(VB_AREA !== 'Upgrade', ", bypass = $bypass") . "
        ###REPLACE###
        WHERE sessionhash = '" . addslashes($session['sessionhash']) . "'"
        , 'sessionupdate'
); 


4. [ with this block of code]

PHP Code:

    /**
     * Update Location On Page Change
     *
     * @author Trigunflame
     * @version 1.1
     * @copyright Dusty Burns 2005-2006
     */

    // Check Script
    if ($session['location'] != WOLPATH || THIS_SCRIPT == 'misc' || THIS_SCRIPT == 'online')
    {
        $DB_site->shutdown_query("
            UPDATE " . TABLE_PREFIX . "session
            SET useragent = '" . addslashes(USER_AGENT) . "', lastactivity = " . TIMENOW . $location . ", styleid = $styleid" . iif(VB_AREA !== 'Upgrade', ", bypass = $bypass") . "
             ###REPLACE###
             WHERE sessionhash = '" . addslashes($session['sessionhash']) . "'"
            , 'sessionupdate'
        );
    }

    /**
     * End Update Locations On Page Change
     */ 


5. [ save file ]

[Trigunflame]

Originally Posted by T3MEDIA

Hummm how are you guys checking your queries? via putty or something?

1. open index.php
2. go to bottom above eval('$navbar = "' . fetch_template('navbar') . '";');
3. put echo " ";
4. open includes/functions.php
5. find exec_shut_down()
6. at bottom, of function place echo "Queries: $query_count <br />";
7. there you go.

At the bottom the other queries hack is from erwin/teck, but like i said theirs doesn't count shutdown queries..

And without my small mod, the number to the right will show 9 everytime, instead of 8.

[Trigunflame]

Update;

Scripts whosonline and misc, are allowed to update page.

[buro9]

If you want to confirm when this query is being run, just add:

echo ".";

To the line above:

  	$DB_site->shutdown_query("
  		UPDATE " . TABLE_PREFIX . "session

Before you install this hack you'd see a dot in the top left hand corner of every page... but after you install this hack you'll only see the dot when you browse to new pages... click refresh on a page and the dot disappears.

Which means the query on the line after it disappears too.

This hack is pretty neat and will help to shave this unneeded query. If you have a large board 200+ users online the it's a good hack to install.

And no, it doesn't add queries anywhere

Whilst TrigunFlame is on a roll we should point him towards the index page to see if he can miraculously find some way of improving that too... I'm down to 0.06 seconds generate time... but I know it can be lower

please edit this, a if else if is not really appropriate... just change the first one:

PHP Code:

    if (in_array(THIS_SCRIPT, $badScripts) OR ($session['location'] != WOLPATH)) 


[Trigunflame]

Originally Posted by nexialys

please edit this, a if else if is not really appropriate... just change the first one:

PHP Code:

    if (in_array(THIS_SCRIPT, $badScripts) OR ($session['location'] != WOLPATH)) 


how about this instead:

PHP Code:

if ($session['location'] != WOLPATH || THIS_SCRIPT == 'misc' || THIS_SCRIPT == 'online') 


note: been up 3 days straight almost giving support and workin on stuff, give me a break :tired:

[sabret00the]

nevermind

just installed

[Deaths]

http://www.vbulletin.com/docs/html/codestandards_and_or

You're using ||

Nice hack by the way, very usefull for big boards. /me installs

[buro9]

Lord above, I never knew that file existed or that vBulletin had coding standards!

I now fear for my own hacks if standards police are going to come knocking

[Deaths]

Hehe .

It's a very usefull file, nice to read through it when you are bored .

Many authors make this mistake, but I just happened to notice it while scrolling his code.

[Trigunflame]

Originally Posted by Deaths

http://www.vbulletin.com/docs/html/codestandards_and_or

You're using ||

Nice hack by the way, very usefull for big boards. /me installs

Unfortunately you cannot depend on Vbulletin for coding standards, as its inards are a lot of crap coding.

I prefer using || instead of OR; They just suggest using it for readability, in PHP Documentation, it does not matter.