View Full Version : Code Optimisation - Anyone got anything that can optimise your vB files?
Chris M
11-01-2002, 08:57 PM
Im kinda worried about how many queries my index.php file, and showthread.php file seem to be spilling out...
Has anyone got any code modifications that can reduce the Query count, and optimised the PHP code used?
Satan
DrkFusion
11-01-2002, 09:35 PM
InvisionBoard has the queries lower than 10, I don't know what makes vb queries go so high, must be all the extra functions, and juciy features.
Go ask invboard what they do ;)
"Hi, My name is Chris, I am from vBulletin.org, can you tell me how you have your queries so low? so I may use it for my vB board?"
:p
Tigga
11-01-2002, 10:05 PM
How many querie's do those pages have?
NTLDR
11-01-2002, 10:06 PM
I'd rather have the features than Invision Board :p
If you remove the queries for total posts, total threads, members, newest member, birthdays, avatars for welcome panel, WOL, that will lose you about hmm, 8 queries on forumhome...
forumdisplay, probably the wol there, and i dunno what else.
showthread, just get rid of everything apart from usernames in the postbit! So you can still see who's posting, and what they're saying!
hehe :D
Erwin
11-02-2002, 12:21 AM
Tips:
1) If you've removed the moderator column, remove the whole chunk of code that is used to look up moderators - saves you 1 query.
2) Combine the 3 PM queries into 1 - saves you 2.
3) Put all templates into templatesinclude="" section at the top of the file - saves you a few there.
4) Remove all the unecessary info - eg. I removed the newest member username from index - that's 1 query. I also removed total threads (I left only total posts) - that's another query.
All these queries add up. :)
Dean C
11-02-2002, 08:23 AM
Can you show me how to do no2 again Erwin :)
Regards
- miSt
Erwin
11-02-2002, 08:41 AM
Replace old PM code with this one.
$allpm=$DB_site->query_first("SELECT COUNT(*) AS messages,
SUM(IF(dateline>$bbuserinfo[lastvisit] AND folderid=0,1,0)) AS newpm,
SUM(IF(messageread=0 AND folderid=0,1,0)) AS unreadpm
FROM privatemessage WHERE userid=$bbuserinfo[userid] $ignoreusers");
$newpm['messages']=$allpm['newpm'];
$unreadpm['messages']=$allpm['unreadpm'];
Do remember that in some cases less querys can = more load on the servers side, sometimes its better to have 2 small querys then one huge one.
AL: That happens when there's one huge query, as it takes time to run it ;) but if you combine three small queries, it isn't too bad ;)
Question: In some files, I have a few lines of templatesused=
ie. I have several occurances, like this:
$templatesused= loads of template names here
$templatesused= loads here too
$templatesused= some here too!
Would it be better to combine these?
Also, when a template is called elsewhere within the code, how can I tell if it's running an extra query?
Also, what was the method to see what queries are run on each page? I know it's something to do with debug mode...
Erwin
11-02-2002, 09:11 AM
No point having 3 $templatesused - combine them into 1 line.
Any template being eval() that's not in $templateused will be 1 query. No question about it.
To see exactly what queries are being run, add this line to your config.php file in your admin directory:
debug=1
Then run any php file with ?explain=1 at the end of the file, and you will see the list of queries being run.
Remember to remove that line from config.php - it's a security risk.
N9ne: that post was directed at hellsatan, not erwin ;)
to see the querys on any page add:
$debug = "1";
to the config.php file then put &explain=1 at the end of any url on your vbulletin you wish to see the query's for, remember to trun off debug mode when your done as it poses a risk.
Erwin you just beat me to it ;)
Tigga
11-02-2002, 11:03 AM
Originally posted by Erwin
No point having 3 $templatesused - combine them into 1 line.
Isn't it done that way to kinda group them together for different circumstances? Ex - All templates that are used when a user is logged in are in one set, all templates for a guest are in the next set, and all templates used by both are in another (or something to that effect). I could be wrong about that, but I thought it was done like that for a reason.
Scott MacVicar
11-02-2002, 11:12 AM
its also just to make it easier to read, its not going to execute any extra queries or anything.
You can end up doing more table scans than needed if you combine all 3 queries as Anime-Loo said.
Chris M
11-02-2002, 11:21 AM
Forumhome page was : 38 Queries
Forumhome page is now : 32 Queries
Yey!:)
Satan
Chris M
11-02-2002, 11:26 AM
PPN - How would you do that?
Satan
Chris M
11-02-2002, 01:01 PM
I found something out guys...
If I were you, I would go through all your files, and add :
,error_nopermission to the end of templatesused...
There are no end of files without it;)
Satan
Tigga
11-02-2002, 01:06 PM
In most cases that will actually slow things down (just a bit, but still). Think about how often that page is displayed, as opposed to how often it won't be. It's better to not have the template pulled from the database every time a page is loaded when it is only used maybe 2% of the time.
Chris M
11-02-2002, 01:13 PM
Have you looked at the vB files, recently if ever?
Have you seen how many times:
show_nopermission();
occurs?
Satan
Tigga
11-02-2002, 01:28 PM
Why the attitude? All I was trying to do was help you out. Why don't you post on vbulletin.com and ask their opinion? I'm sure if you're right they would want to know anyways since that could be considered a small error in the coding.
You're right... The code is displayed many times in the files, but how often is the no permission page actually displayed? If you go through and put that in every file, it is going to be calling the error_nopermission template from the database every time you load a page, which isn't necessary since 98% of the time your users won't be getting that page.
Chris M
11-02-2002, 01:52 PM
There wasnt an attitude, I just remember being told that that saves a few queries...
I get about as many guests on at one time as users, so it helps;)
Well I did alot of adding templates that werent added to "$templatesused", and i reduced alot of queries...
Satan
Tigga
11-02-2002, 02:04 PM
Whether it's there or not, I don't see it making that much of a difference. Save a query here, pull an unnecessary template there. It's a double-edge sword. :)
I should probably look through my files as well... I'm sure I missed a template or two when I was adding hacks many months ago and didn't know much about what I was doing. ;)
Chris M
11-02-2002, 02:26 PM
Indeed...
I found that quite a few hacks didnt have the templates in the $templatesused...
I posted a Site Feedback message a few mins ago asking that something be added to the rules about adding templates to the $templatesused if your hack requires new templates;)
Satan
Tigga
11-02-2002, 02:38 PM
Hehe... I searched through my board and amazingly I only found one hack that needed the templates added (an IRC chat hack). I'm kinda surprised I didn't find more.
Good idea for the post in the feedback forum too. :)
Satan, plur's right. Adding templates that are barley used to the $templatesused can slow down preformance, it is a doubled edged sword ;).
Chris M
11-02-2002, 02:55 PM
Yup...For most people maybe;)
But with the amount of guests I get, and Users (that do post) who try and do things they cant, its worth it:)
Satan
NTLDR
11-02-2002, 06:12 PM
Removing the forum jump code aslo saves you at least 1 query if you don't use it ;)
Erwin
11-02-2002, 07:41 PM
For example, with vBPortal, the templates are not added to the $templateused by default - and they have many custom templates - imagine the waste in querying the database when they could all be cached. (Actually, the vBPortal code tries to include them, but it is written wrongly, and are not included).
Dean C
11-02-2002, 07:52 PM
Erwin:
Is this the old PM code?
$allpm=$DB_site->query_first("SELECT COUNT(*) AS messages FROM privatemessage WHERE userid=$bbuserinfo[userid] $ignoreusers");
$newpm=$DB_site->query_first("SELECT COUNT(*) AS messages FROM privatemessage WHERE userid=$bbuserinfo[userid] AND dateline>$bbuserinfo[lastvisit] AND folderid=0 $ignoreusers");
$unreadpm=$DB_site->query_first("SELECT COUNT(*) AS messages FROM privatemessage WHERE userid=$bbuserinfo[userid] AND messageread=0 AND folderid=0 $ignoreusers");
- miSt
Erwin
11-02-2002, 07:54 PM
Yes. Replace that with the code posted.
Chris M
11-02-2002, 08:57 PM
Its good:)
That and a few other modifications have reduced the forumhome to just 29 Queries!:)
Satan
I have another question for you php and mysql experts!
Lets say there is a line like this:
eval("\$closeopenthread = \"".gettemplate("newthread_closeopen")."\";");
That line is in my showthread.php file. It has the eval thing, does that mean it's adding a query? If so, how do I change it so it doesn't?
I did this explain thing and what on earth does all the stuff mean :eek:
Query: SELECT template FROM template WHERE title='colforumtablefooter' AND (templatesetid=-1 OR templatesetid=1) ORDER BY templatesetid DESC LIMIT 1
Time before: 0.51396095752716
Time after: 0.51483500003815
table type possible_keys key key_len ref rows Extra
template range title title 32 2 where used; Using filesort
Like that, I just don't know, I mean it's the template table, but I don't understand what to do?
Ok this looks bad:
Query: UPDATE user SET lastactivity=1036276130 WHERE userid=1 AND lastactivity<1036276130
Time before: 0.19920098781586
Time after: 0.2000550031662
--------------------------------------------------------------------------------
Query: UPDATE user SET lastactivity=1036279082 WHERE userid=1 AND lastactivity<1036279082
Time before: 0.20031905174255
Time after: 0.200718998909
--------------------------------------------------------------------------------
Query: UPDATE user SET lastactivity=1036278622 WHERE userid=1 AND lastactivity<1036278622
Time before: 0.20096302032471
Time after: 0.20142102241516
--------------------------------------------------------------------------------
Query: UPDATE user SET lastactivity=1036278025 WHERE userid=1 AND lastactivity<1036278025
Time before: 0.20167303085327
Time after: 0.20206105709076
--------------------------------------------------------------------------------
Query: UPDATE user SET lastactivity=1036277677 WHERE userid=1 AND lastactivity<1036277677
Time before: 0.20230603218079
Time after: 0.20269000530243
--------------------------------------------------------------------------------
Query: UPDATE user SET lastactivity=1036277096 WHERE userid=1 AND lastactivity<1036277096
Time before: 0.20292901992798
Time after: 0.20330798625946
--------------------------------------------------------------------------------
Query: UPDATE user SET lastactivity=1036276868 WHERE userid=1 AND lastactivity<1036276868
Time before: 0.20354700088501
Time after: 0.20392203330994
--------------------------------------------------------------------------------
Query: DELETE FROM session WHERE lastactivity<1036280063
Time before: 0.2041449546814
Time after: 0.20440900325775
--------------------------------------------------------------------------------
Query: DELETE FROM search WHERE dateline<1035675763
Time before: 0.20457899570465
Time after: 0.2589670419693
Those are the last few queries in showthread...
NTLDR
11-02-2002, 09:56 PM
I perfer using showqueries=1
I'm not sure as to what the tables that are produced mean, but you can see each query that is run, and the time each one is taking.
As for the eval bit, if the template name is not in $templatesused before global.php is required then one extra query is added, otherwise no extra query for it.
Oh right so if there's an eval code line, I just get the template name from it and put it in templatesused and that's the query gone?
Scott MacVicar
11-02-2002, 10:51 PM
you'll find that all of them in the eval statement is in the templatesused.
Just ones that aren't called often or rarely are left out as it would be a waste of time to fetch those pages with the rest.
Erwin
11-03-2002, 04:43 AM
@ N9NE - let me guess, you have the "total user time online" hack by gforce2k installed. :) Those are extra queries to reset the time for when you are online I think.
So the Total Time Online hack is adding about 7 queries to showthread? :eek:
NTLDR
11-03-2002, 06:12 PM
Only if you have applied it wrong, I've checked mine and I don't have 7 queries for it.
wajones
11-03-2002, 07:08 PM
Originally posted by Erwin
For example, with vBPortal, the templates are not added to the $templateused by default - and they have many custom templates - imagine the waste in querying the database when they could all be cached. (Actually, the vBPortal code tries to include them, but it is written wrongly, and are not included).
How about telling me what I'm doing wrong and how to do it right so I can fix it. I'm presently adding them before it calls global.php or Email me at waj@adelphia.net
Originally posted by NTLDR
Only if you have applied it wrong, I've checked mine and I don't have 7 queries for it.
What code should I check? IIRC the hack only modifies a few files: sessions.php functions.php member.php
Which file could the problem be occuring from?
NTLDR
11-03-2002, 07:25 PM
I'd have said if you made a mistake it would have been in sessions.php
Hmm, this is weird though, showthread only has 27 queries for me, yet when viewing what the queries are, there's such a long list of those :confused:
...Just checked the code in sessions.php and it's fine, and I still get only 27 queries on showthread...weird.
NTLDR
11-03-2002, 07:35 PM
Do all the queries listed add up to 27 on showqueries? I've got 26 on my showthread.
Scott MacVicar
11-03-2002, 08:24 PM
if your talking about those ones to update lastactive on the user, they are shutdown queries which run when the script is ending execution for things like setting lastactive time about one in 100 views it will update all the sessions that have ended because users have left in the cookietimeout.
look at the bottom of functions.php
Dean C
11-04-2002, 05:38 PM
$allpm=$DB_site->query_first("SELECT COUNT(*) AS messages,
SUM(IF(dateline>$bbuserinfo[lastvisit] AND folderid=0,1,0)) AS newpm,
SUM(IF(messageread=0 AND folderid=0,1,0)) AS unreadpm
FROM privatemessage WHERE userid=$bbuserinfo[userid] $ignoreusers");
$newpm['messages']=$allpm['newpm'];
$unreadpm['messages']=$allpm['unreadpm'];
That new PM code doesn't work for me :(
My new pm's and unread pm's dont show up and the total count for my number of PM's is wrong :(
- miSt
Xenon
11-04-2002, 06:03 PM
Mist look here, perhaps this could help ya:
https://vborg.vbsupport.ru/showthread.php?s=&threadid=44131&highlight=Queries
Dean C
11-04-2002, 06:34 PM
I'll give it a try Xenon :)
Thanks
- miSt
vBulletin® v3.8.12 by vBS, Copyright ©2000-2025, vBulletin Solutions Inc.