Remove 1 Database Query Per Page

[Trigunflame]

Remove 1 Database Query Per Page

https://vborg.vbsupport.ru/ (https://vborg.vbsupport.ru/vborg_miscactions.php?do=installhack&threadid=79923)

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 ]


$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]


/**
* 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 ]

[sabret00the]

i'll probably install but i'm happy with my vote for MAF thank you :p

[Trigunflame]

i'll probably install but i'm happy with my vote for MAF thank you :p

lol, poo on you :p

[Princeton]

anything that improves overall performance is a PLUS ...

[Trigunflame]

anything that improves overall performance is a PLUS ...

Couldn't agree more XD

[nexialys]

i would like to see a single forum where we can post that kind of Performance hack, because there is some (*i can count at least 25 of these), and they would be more than interesting to add to the next update...

because they are not hacks, they are debugs (when we don't modify a function, it's a debug isn't it?)

[Trigunflame]

i would like to see a single forum where we can post that kind of Performance hack, because there is some (*i can count at least 25 of these), and they would be more than interesting to add to the next update...

because they are not hacks, they are debugs (when we don't modify a function, it's a debug isn't it?)

I imagine, I woulda stuck this in mini-mods, but eh it really belongs here, if anywhere :/

[Yami Shadow]

I like this, but it seems to increase my board's query by 1, rather than knocking one off. Originally had 10, went to 11 after I preformed the mod..

[Trigunflame]

I like this, but it seems to increase my board's query by 1, rather than knocking one off. Originally had 10, went to 11 after I preformed the mod..

Eh, thats not possible. Make sure you copied it exactly as I have it.

All this is doing is an IF condition to check the current page, if its the same page then the query is ignored.

Also should note: These are "Shutdown queries" which are performed after the output of data to the forum, and that would include whatever hack you are using to count queries.

These queries are not "counted" by traditional means, they are executed at the end of the life of the script.

[T3MEDIA]

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

[Trigunflame]

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 ;)

[nexialys]

please edit this, a if else if is not really appropriate... just change the first one: if (in_array(THIS_SCRIPT, $badScripts) OR ($session['location'] != WOLPATH))

[Trigunflame]

please edit this, a if else if is not really appropriate... just change the first one: if (in_array(THIS_SCRIPT, $badScripts) OR ($session['location'] != WOLPATH))


how about this instead:

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 :p.

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

[Trigunflame]

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.

[Deaths]

It's usefull for people who are new to PHP, but still want to optimize code a little.

Anyway, its your hack, your choice ;)

[twoseven]

i've been programmig so long that i read || as OR its just as readable imo.

[Trigunflame]

*wonders why my thread has become a php syntax discussion*

[Marco van Herwaarden]

*wonders why my thread has become a php syntax discussion*If you would like to make a comment, you should start with a '/*' and end with '*/', not just a '*':
/*wonders why my thread has become a php syntax discussion*/

[buro9]

/* You should really leave spaces around those comments too. */

[WebMasterAJ]

So... lol... is this working for everyone? Sorry to bring this thread... umm... back on topic. :lol:

[Erwin]

Good idea. You're right, shutdown queries don't show up on the microstats... hence I saw no difference when I installed this. :) That 1 query really doesn't make a huge difference considering what it's doing. But theoretically if you don't need that query, why have it there? :)

[Trigunflame]

Good idea. You're right, shutdown queries don't show up on the microstats... hence I saw no difference when I installed this. :) That 1 query really doesn't make a huge difference considering what it's doing. But theoretically if you don't need that query, why have it there? :)

That could be said about a lot of code vbulletin has :)

ps. I tried this on a forums im workin with, has on average 2000+ online; it "did" cut down his load somewhat by the reduce in just that 1 query.

[Trigunflame]

*wonders how many people have actually used this*

Surprised not that many... oO

[WebMasterAJ]

I have it installed... it can't hurt.

[neocorteqz]

*wonders how many people have actually used this*

Surprised not that many... oO
I just found this thread.

[GlitterKill]

After installing this hack I am now getting emails like this... 5 so far today:

Database error in vBulletin 3.0.6:

Invalid SQL: INSERTINTOthreadviewsaggregate SELECT threadid , COUNT(*)ASviewsFROM threadviews GROUP BY threadid mysql error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERTINTOthreadviewsaggregate SELECT threadid , COUNT(*)ASview

mysql error number: 1064

Date: Friday 20th of May 2005 03:56:02 PM
Script: http://forums.pimprig.com/cron.php?&rand=50303
Referer: http://forums.pimprig.com/showthread.php?t=44214
Username: Peng Lord
IP Address: 66.167.218.162

and another:

Database error in vBulletin 3.0.6:

Invalid SQL: INSERTINTOthreadviewsaggregate SELECT threadid , COUNT(*)ASviewsFROM threadviews GROUP BY threadid mysql error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERTINTOthreadviewsaggregate SELECT threadid , COUNT(*)ASview

mysql error number: 1064

Date: Friday 20th of May 2005 02:56:00 PM
Script: http://forums.pimprig.com/cron.php?&rand=325958
Referer: http://forums.pimprig.com/member.php?u=2606
Username: SpikeShot
IP Address: 68.52.66.139

My dedicated server runs PHP 4.3.11 and MySQL 4.0.24. Any ideas?

[neocorteqz]

I do believe your problem is here


INSERTINTOthreadviewsaggregate


Do you have another hack installed which uses the same file?

[Trigunflame]

After installing this hack I am now getting emails like this... 5 so far today:

Database error in vBulletin 3.0.6:

Invalid SQL: INSERTINTOthreadviewsaggregate SELECT threadid , COUNT(*)ASviewsFROM threadviews GROUP BY threadid mysql error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERTINTOthreadviewsaggregate SELECT threadid , COUNT(*)ASview

mysql error number: 1064

Date: Friday 20th of May 2005 03:56:02 PM
Script: http://forums.pimprig.com/cron.php?&rand=50303
Referer: http://forums.pimprig.com/showthread.php?t=44214
Username: Peng Lord
IP Address: 66.167.218.162

and another:

Database error in vBulletin 3.0.6:

Invalid SQL: INSERTINTOthreadviewsaggregate SELECT threadid , COUNT(*)ASviewsFROM threadviews GROUP BY threadid mysql error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERTINTOthreadviewsaggregate SELECT threadid , COUNT(*)ASview

mysql error number: 1064

Date: Friday 20th of May 2005 02:56:00 PM
Script: http://forums.pimprig.com/cron.php?&rand=325958
Referer: http://forums.pimprig.com/member.php?u=2606
Username: SpikeShot
IP Address: 68.52.66.139

My dedicated server runs PHP 4.3.11 and MySQL 4.0.24. Any ideas?

Don't know what you edited, but it doesn't have anything to do with this hack.. as you are not dealing with anything involving "Inserting" into "Threadviews", you just messed up somewhere.

[Marco van Herwaarden]

The errors are coming from some Scheduled Task, not from this hack.

[GlitterKill]

Sorry this was meant to go into the authors other optimization thread. Sorry. :(

[David Bott]

Installed on AVS Forum. :)

[Thug]

installed thanx ;)