CES Intelligent Postbit Query

[thincom2000]

CES Intelligent Postbit Query
Version: 1.0.1

This hack is now used as a troubleshooting tool for members with unusually high queries on showthread. You do not need to install this unless your queries peak around 20 or higher.

Note that if after installing this you experience a drop in queries, it means you have a bad plugin installed. Leaving this installed, while this does not hurt your forum at all, does not solve your query problem - it only now hides it.

To disable the tool, you only need to change the line:
$lastcheck = ($lastfetchtype == $fetchtype) ? true : false;

to:
// $lastcheck = ($lastfetchtype == $fetchtype) ? true : false;

Post your query success stories in the thread! :)

For those without success (i.e. it doesn't seem to make a difference), please don't uninstall, but help us determine what is different in your case. PHP version, MySQL version, hacks installed, PHP cache, etc. would all be useful information. Thanks!

*** NEWS ***
This section has become a list of bad plugins. Often these plugins can be optimized by a rewrite using a different hook, and thus not causing this query issue.

Product: Username Management
Hook Location: postbit_factory
Bad Plugin: Add Username history to postbit(legacy)

1/28/2007 - v1.0.0, v1.0.1 released

I actually came across this while trying to reduce queries in my CES Site News (https://vborg.vbsupport.ru/showthread.php?t=137365) mod which uses a customized showthread.php.

Known Issues:
- NONE

- Some users see an amazing improvement, others do not see a difference in query count for some time, while still others don't see any difference at all. It all depends on your server setup and your current vBulletin installation (settings, other mods, etc). This hack is still young (1 day) so we haven't narrowed down what causes these differences in success.

- If you are still experiencing the stolen signatures issue, you need to clear your sig_cache. Do this by going to AdminCP -> Maintenance -> Update Counters -> Empty Signature Cache.

What It Does:
On some installations showthread wastes 1 query per post in showthread.php when creating the postbits. You can tell if this applies to your installation if you approach or exceed 20 queries on a page of only 10 or so posts.

By adding these 4 lines, showthread checks to see if it already has created the appropriate Postbit Factory object instead of doing it all over again. Queries on showthread are thus DRASTICALLY reduced (1 per post), even though the code reveals no direct database references.

This can be a HUGE performance boost.

I hope someone from Jelsoft sees this thread...

Mod Features:
- removes multiple pointless queries

Products to Install: 0
Plugins Included: 0
Files to Upload: 0
Files to Edit: 1
New Templates: 0
Template Edits: 0
Queries Added*: -1

*See "What It Does" for details.

*** Changelog ***
As of Version 1.0.1:
- fixed bug where posts would "steal" signatures from each other
Special thanks to TheFrienzNet (https://vborg.vbsupport.ru/member.php?u=181399) for his help ^_^

[winpro19]

Yay first to reply and install!! THanks, trying it out now!

[stwilson]

In your instructions you mention to replace this line
$postbit_obj =& $postbit_factory->fetch_postbit($fetchtype);

You do not mention to replace it twice. My showthread file has the above line listed in two different locations. Should I replace both of them with your suggested changes?

[thincom2000]

You can replace both if you want. The second instance of the line is for Hybrid / Threaded Modes which are the boring threadlists you sometimes get from search engines. If you don't have Hybrid / Threaded Mode enabled on your forum, changing that section of showthread.php is not necessary.

[stwilson]

Got it. Clicks installed.

[thincom2000]

If everyone could please post the number of queries they save on a full page of posts, that would be awesome.

Here's mine: 22 - 10 = 12.

[lolo?]

no change for me it's the same before and after :s

[thincom2000]

How many posts were in the thread? Link?

Also the page you are checking might still be cached in your browser. This doesn't happen much with regards to queries but happened to me a few times today.

[puregraf]

can this work with vb 3.5.4 ? I really need this.

thanks

[winpro19]

Hmm...this is probably a stupid question...but how do I determine the number of queries on my page? Let me know ,

Thanks guys

[Darat]

I've tried it on a page of default number of posts (40) - which generated 95 queries before and 95 queries afterwards.

So seems to have no effect. :(


By the way what is this line used for:

$lastfetchtype = ($lastfetchtype != $fetchtype) ? $fetchtype : $lastfetchtype;

You don't seem to make any reference to $lastfetchtype?

[thincom2000]

$lastfetchtype is a new string this adds to track what the last generated postbit was (i.e. a post, PM, etc.)

$lastcheck = ($lastfetchtype == $fetchtype) ? true : false;
$lastfetchtype = ($lastfetchtype != $fetchtype) ? $fetchtype : $lastfetchtype;

means:

If $lastfetchtype equals this $fetchtype, $lastcheck equals true, otherwise $lastcheck equals false.
Now that we know what $lastfetchtype is, we can bother changing it. If $lastfetchtype is not the same as this $fetchtype, update it, otherwise leave it alone.

There should be no need to reference $lastfetchtype.

Are you using Linear or Hybrid / Threaded Modes on your forum?

EDIT: Here's some screenshots to show this actually did something for me. I'm really clueless as to why it wouldn't work for others. I'll restore my backup showthread and check it out.

EDIT: I restored my backup and tried it again. No problems. Note that the line

$postbit_obj =& $postbit_factory->fetch_postbit($fetchtype);

appears twice in showthread (line 1046 and line 1507), and for the most part we are only modifying the first appearance. If you are experiencing problems, try changing it at both places

[thincom2000]

Hmm...this is probably a stupid question...but how do I determine the number of queries on my page? Let me know ,

Thanks guys

I'm actually wondering this too. Since I already have it enabled, can't find the setting now, and will be wanting to disable it in the future.

EDIT: https://vborg.vbsupport.ru/showthread.php?t=82900

[COBRAws]

wow!

I had a thread with 101 posts, after doing this changes I had 136 queries, now I just have 35!
Good work mate. But, is there any problems doing these edits?

[thincom2000]

Shouldn't be any problems. Unless you have a page with different postbits:

like it shows PMs, and posts, and post-legacies on the same page,
but the $lastfetchtype check should eliminate those problems.

Glad to hear about that success that's an awesome reduction. Unless someone has issues where it actually messes up functionality, I'm hoping for a MOTM XD, or at least Jelsoft putting it in 3.7.x.

[FleaBag]

With 50 posts per page, this is going to make a huge difference! Thanks very much! Think I will make sure all my files are all current version before I proceed through! :)

[TheFrienzNet]

Wow, thanks!

*EDIT*

WOW! MY forum's performance is 10x times faster! I mean wow! This is just great!

[Kiros72]

This is probably a stupid question, but how would you go about finding out how many queries it used, what the server load is, etc?

[thincom2000]

See this post: https://vborg.vbsupport.ru/showpost.php?p=1169028&postcount=13

[TheFrienzNet]

I have a bug to report, this occurs from time to time.

As I have a big community with over 45,000+ posts, some posts are messed up.

Sometimes, a user's signature is different from his real signature. Like me, sometimes my signature is different from my real one and it shows a signature from another user.

[Kiros72]

Thanks - trying it out now :)

Edit:

I'm getting the same bug as well - signatures messing up.
I'm not exactly sure why but it seems that there's no difference. 10 queries per page with or without this hack (10 posts per page as well) - or at least that's what I'm getting from the Microstats.

[thincom2000]

I had sigs turned off on my forum while testing this yesterday so I didn't notice. I will look into this issue.


I'm not exactly sure why but it seems that there's no difference. 10 queries per page with or without this hack (10 posts per page as well) - or at least that's what I'm getting from the Microstats.

A number of users have reported this just to edit their post 20 minutes later saying how awesome the changes were. If you are getting 10 queries per page it sounds like it's working (without the hack on a 10 post page should be about 20 queries). Are you using a PHP cache?

[TheFrienzNet]

Thanks thincom2000, but could you do it fast? I know I sound like a jerk but there are over 9,000 signatures messed up and my higher ranked bosses will be mad at me..

*Edit*

I got it fixed! In order to fix it, you must do 'Empty Signature Cache' in Maintenance! Thincom2000, I guess you have to update the readme. :)

*ReEdit*

Okay, I did that 'Empty Signature cache' but it worked only for a few minutes, after that it got messed up again. At the meantime, I will change the line back to what it was and run that 'Empty Signature Cache' maintenance script. Thank you Thincom2000.

[tanyeri24]

I have this line twice:
$postbit_obj =& $postbit_factory->fetch_postbit($fetchtype);

Replace both like the lines in the .txt file?

[TheFrienzNet]

I have this line twice:
$postbit_obj =& $postbit_factory->fetch_postbit($fetchtype);


Replace both like the lines in the .txt file?

Yes, if you want. One line is for hybrid mode and the normal view, but it's better doing it anyways. However, it's bugged, your user's signatures will be messed up.

[tanyeri24]

Yes, if you want. One line is for hybrid mode, but it's better doing it anyways. However, it's bugged, your user's signatures will be messed up.

Installed :up:

[TheFrienzNet]

You will experience a few bugs with users signatures. So I don't recommend it, yet.

[thincom2000]

Just turned them back on and I am not experiencing any issues with my user's signatures.

Looks like I'm going to need more info from you guys. What usergroups are experiencing this problem? What permissions do they have? Do you have any sig-modifying hacks installed? Where signatures messed up on new posts or old ones?

[Kiros72]

From what I can tell, if this affects one usergroup, it should affect them all, however, I first noticed it with Registered Users. I'm not certain if anyone else is being effected because I had to revert the changes - users were starting to complain about how someone "stole" their signature.

The Registered Users have set signature permissions as follows:

Can Use Signatures: Yes
Maximum Characters in Signature Including BBCode Markup: 550
Maximum Characters in Signature Excluding BBCode Markup: 450
Maximum Lines in Signature: 8
Allow BB Code: Yes
Allow Basic BBCode: Yes
Allow Color BBCode: Yes
Allow Size BBCode: Yes
Maximum Value of x for SIZE=x BBCode: 5
Allow Font BBCode: Yes
Allow Alignment BBCode: Yes
Allow List BBCode: Yes
Allow Link BBCode: Yes
Allow Code BBCode: Yes
Allow PHP BBCode: No
Allow HTML BBCode: No
Allow Quote BBCode: Yes
Allow IMG Code: Yes
Maximum Number of Images in Signature: 8
Allow Smilies: Yes
Allow HTML: No

The only signature hack that I have is one that modifies the modifysignature template. If I'm thinking clearly, that only modifies the UserCP "Edit Signature" jazz. The hack that I have for it is the "Custom Sig Permissions Display" - for reference.

The first time I noticed this bug was at the first post of a thread - the first poster's signature was copied to the second poster's signature area. It was a total overwrite, not an addition. I looked to see if that was consistent, however, I didn't come across another instance like it before I went to revert the file.

Edit:

Also, I think you might have to have several members and several posts in a thread for this bug to appear - just a guess.

Edit 2:

As a note, I've had the file reverted for about 15 minutes (at least) and I'm still getting only 10 queries on a thread with 10 posts per page. I'm not sure this hack had an effect or not. :?

[jedisct1]

Hello,

It seems to mess signatures. After installing this, some posts had the signature of another user.
Reverting the patch fixed it.

[Jorrit787]

* Clicks uninstall.

I'm having the same problem with the signatures :(

[iardon]

...or at least Jelsoft putting it in 3.7.x.

Have you contacted Jelsoft about this and your other CES mod?

[TheFrienzNet]

thincom2000, do you have MSN or any instant messenger so we can resolve this problem together?

[tanyeri24]

:( I have the signature problem too..

[TheFrienzNet]

We finally got it working! Please look at the first post.

[tanyeri24]

We finally got it working! Please look at the first post.

where changes? :o

[thincom2000]

I just uploaded the updated instructions.

Very soon I will be off to eliminate the next unnecessary query: did you ever notice that polls add 2 extra queries to showthread? It'll take some, not hacking but, butchering, but I will do it with 0!

Also coming soon an updated version of my intelligent attachment query, that's basically no different in practice just fewer edits to showthread.php (since it was another butcher job).

[tanyeri24]

and now again edited but I have anyways same query count like unedited showthread.php .. (I see the querys with microstat plugin)

[TheFrienzNet]

Do you have any other page compressor/cache accelerator or whatsoever?

[thincom2000]

What's the query count?

[tanyeri24]

Do you have any other page compressor/cache accelerator or whatsoever?
None I known.

What's the query count?

A thread with 10 posts:
Page generated in 0,58209 seconds with 15 queries [Server Loads: 2.04 2.76 : 3.19]

[TheFrienzNet]

Err... 15 queries. That's pretty good.

[tanyeri24]

Err... 15 queries. That's pretty good.

But I had it with original showthread.php too :D

[thincom2000]

Does that thread with 15 queries have attachments in it?

[Get Shorty]

Installed with no problems, but I don't notice a difference in the number of queries on each page.

[tanyeri24]

Does that thread with 15 queries have attachments in it?

Hi, it have no attachements. :rolleyes:

[COBRAws]

I was having the Steal sigs bug, now I updated with the new code replacements (replaced just the first instances and the two).

Anything I do, I get the following error msg:
Fatal error: Call to undefined method stdClass::construct_postbit() in /xxxxxxxxxxxx/showthread.php on line 1070

mmm :(

[TheFrienzNet]

As seen from your profile card, you are using vBulletin 3.5.4. This hack only supports 3.6.x.

[treasureman]

Uninstalled, made all my user signature files change to other users, big mess, many complaints!

This should not have been released without testing.

[thincom2000]

You need to clear your signature cache if you are still having this problem.

[COBRAws]

As seen from your profile card, you are using vBulletin 3.5.4. This hack only supports 3.6.x.
Im using 3.6.4 my profile was not updated.

[thincom2000]

I was having the Steal sigs bug, now I updated with the new code replacements (replaced just the first instances and the two).

Anything I do, I get the following error msg:


mmm :(

That happened to me while during primary testing. On vBulletin 3.6.4 it happens when you have:
$lastfetchtype = ($lastfetchtype != $fetchtype) ? $fetchtype : $lastfetchtype;
$lastcheck = ($lastfetchtype == $fetchtype) ? true : false;


instead of
$lastcheck = ($lastfetchtype == $fetchtype) ? true : false;
$lastfetchtype = ($lastfetchtype != $fetchtype) ? $fetchtype : $lastfetchtype;

[COBRAws]

That happened to me while during primary testing. On vBulletin 3.6.4 it happens when you have:
$lastfetchtype = ($lastfetchtype != $fetchtype) ? $fetchtype : $lastfetchtype;
$lastcheck = ($lastfetchtype == $fetchtype) ? true : false;


instead of
$lastcheck = ($lastfetchtype == $fetchtype) ? true : false;
$lastfetchtype = ($lastfetchtype != $fetchtype) ? $fetchtype : $lastfetchtype;
Thanks, quick replier :D but im using 3.6.4 as on my last message. Sorry for not updating my profile info.

[thincom2000]

In that case please post your showthread.php (stripped of license data), and I'll take a look at it.

[Kiros72]

Is there any reason the queries would appear to be the same (before and after the hack)?

[thincom2000]

For each post, -1 query. For each signature +1 query.

It would appear to be the same if the number of different signatures and posts are the same. In its current form, this would only be beneficial if the same people keep posting in a thread.

EDIT: That's completely untrue. I just looked at the code again, and at my site, and this does not add queries for signatures.

There is no reason I can think of why you should not be getting QUERIES SAVED = NUMBER OF POSTS ON PAGE - 1.

Do you have any hacks that run on postbit hooks ?

[Cap'n Steve]

Maybe I'm missing what exactly this hack does, but I know that default vBulletin doesn't do a query for every post. If you have more than 20 queries or so, then something is very wrong.

[Get Shorty]

Maybe I'm missing what exactly this hack does, but I know that default vBulletin doesn't do a query for every post. If you have more than 20 queries or so, then something is very wrong.

Yeah, like I mentioned, I didn't see any change in query usage.

[Kiros72]

Maybe I'm missing what exactly this hack does, but I know that default vBulletin doesn't do a query for every post. If you have more than 20 queries or so, then something is very wrong.
No, I don't have any hacks that would modify the postbits - not that I'm aware of :confused:

[G_Man]

Maybe I'm missing what exactly this hack does, but I know that default vBulletin doesn't do a query for every post. If you have more than 20 queries or so, then something is very wrong.


I just uninstalled since my queries were exactly the same as before making the edits. It sure seems to be helping on some forums though.

Hopefully something can be found to establish this benefit. I have a few mods boosting my queries to around 27-35 or so...

[thincom2000]

Maybe I'm missing what exactly this hack does, but I know that default vBulletin doesn't do a query for every post. If you have more than 20 queries or so, then something is very wrong.

According to showthread.php you are correct in saying that vBulletin doesn't do a query for every post. However, the function $postbit_factory->fetch_type($fetchtype) apparently calls a query. Where? I'm not sure. And why do certain users experience this madness and others don't? That I also don't know. All I know is that by controlling the number of times fetch_type() is called, those of us with query issues on showthread (20 - 25 queries) can now relax with 12 - 15 queries.

[da420]

oh man it's just too bad it doesn't work for me.

[thincom2000]

PHP & MySQL versions? PHP cache? Dedicated server?

[TheFrienzNet]

Oh wow! Damn! I mean WOW! I saved over 200 memory! The forum speed is now 10x times faster! I mean wow... this has to be the greatest tweaking I've ever saw! I hope Jelsoft includes this!

[fastforward]

As far as I can tell, the only way this hack will reduce the number of queries is if you have hacks that use the postbit hooks. If you do have hacks that use those hooks, then this hack may do more harm than good as it will bypass the calls to those hooks.

[Get Shorty]

Uninstalling... This didn't make any change for me.

[thincom2000]

To argue with all those who say there are no queries in the postbits, there are actually a number of queries vBulletin can possibly make during each cycle. I have been investigating this extensively, following function call to function call.

In class_postbit.php, the function call to cache_permissions() - cache_permissions is defined in functions.php and can execute a query under certain conditions. Tested, and this is not our extra query.

In class_bbcode.php, queries can be made to the table bbcode and smilie if $vbulletin->bbcodecache or $vbulletin->smiliecache are empty. Tested, and this is not our extra query.

I will be adding to this over time as I investigate further.

EDIT: I found the cause of the queries.

A little product called: Username Management
Has a plugin included for: postbit_factory
Plugin is called: Add Username history to postbit(legacy)

I don't know why the coder used a separate query for this when only a $hook_query_fields addition was necessary.

[AzzidReign]

Is there a reason why this is no longer up for download?

[tanyeri24]

coz not necesairy. I installed it but I had before and after same queries and same page generating time.. not useless for me.

[AzzidReign]

I'm sure on smaller boards it is pretty useless but I have a semi-large site so I wanted to test it out.

[da420]

Please read the original post by the author of this hack.
It will explain why it was taken out...

[thincom2000]

Yes, but to clarify: some users have reported that they did not have that product (Username Management) installed and still had high query counts.

There are a number of plugins that cause this, although in one case I have been unable to determine the origin of the 1 query per post.

If you have ibProArcade installed, there is a plugin which allows you to show crowns, etc. in the Postbit. This could potentially add 2 queries per post, so disable it.

If anyone has ibProArcade installed and can tell me the name of the plugin, I will include it in the first post.

Also, if you had neither of these installed and your query count on showthread is unusually high (around 20), post here, and we can work together to identify all the bad plugins (and request that the coders use $hook_query_joins in hook showthread_query instead).

I will re-upload the file for members to download as a troubleshooter in this respect, not as a hack.

[Skyline_GT]

I am looking to try this. How can we check how many Query it is using?

[da420]

Enable Debug mode.

[JohnBee]

Installed and it looks like its working from 20 queries down to 14 !!!
I will keep and eye on it for awhile to make certain this is for real :)

[fastforward]

Installed and it looks like its working from 20 queries down to 14 !!!
I will keep and eye on it for awhile to make certain this is for real :)

You might want to read this earlier post (https://vborg.vbsupport.ru/showpost.php?p=1170198&postcount=65) again. You might be breaking any plugins you have installed that use the pobit hooks.

[thincom2000]

In case you haven't read the description to this hack recently, or my extensive post on the purpose of the hack: the point IS to break those plugins. The fact that your queries dropped means that you have plugins installed that aren't properly optimized and should either be rewritten (if you have experience coding) or disabled.

[JohnBee]

In case you haven't read the description to this hack recently, or my extensive post on the purpose of the hack: the point IS to break those plugins. The fact that your queries dropped means that you have plugins installed that aren't properly optimized and should either be rewritten (if you have experience coding) or disabled.
No negative side effects to date. I have been working on the forum since yesterday testing things out.

Thanks for a great hack!

[oldford]

11 - 0 = 11 ... So no change for me. This is on a thread viewing 40 posts.

I appreciate your efforts and I'm glad it seems to help out those with more queries. I just wanted to post that it didn't affect mine. Thanks!

[thincom2000]

10 queries is the lowest I've been able to get queries on a showthread page. This is with NO attachments and NO polls. An attachment(s) will add 1 query to the entire thread, and a poll will add 2.

So if you have 10-13 queries, as a rule you probably don't need any optimization.

For me, so far I've been able to join all queries that do anything on a showthread page except the 2 additional poll queries. They seem to be a little more complicated.

[oldford]

Yeah, that seems to be the case with my forum.

Standard threads = 10
Threads w/ attachments = 11
Threads w/ attachments & poll = 13

[MissKalunji]

i have a couple hack for postbit but it didn't do a difference to me...besides that i found out the award hack has a problem but thats it

[thincom2000]

Then that means your postbit hacks don't have any problems. Congrats.

[MissKalunji]

woohoo great hack *clicks install*

[thincom2000]

Of course since you don't have any problems, you should proceed to disable this hack by commenting out the lines mentioned in the description, just to ensure it doesn't break your postbit plugins that are problem-free.

[TheFrienzNet]

Bump. :)

[LBmtb]

No change in query #'s for me. Good idea though!

[MissKalunji]

i lost 2 queries and it messed up my postbit that's it really what does that mean?