Serious Query Problem

I have a serious Problem with the Querries in showthread.php. Everytime I post in an Thread, next time I open the thread it needs exactly 2 Querries more. As I have 25 Threads / Page this means 50 additional Querries on a full Page! As I already have about 25 Querries as default, i got between 30-80 Querries in a Thread

Can anybody explain to my why this happens and why other users on my board don't seem to add so many querries per post? I have access-masks enabled and Quick-Reply + Lesanes Award-System + Quick Delete installed.

[Xenon]

it seems you have a query call within a while loop

open admin/functions.php
got to function getpostbit

if you find any query in this function, you should take it out.

[TheSaint-AeD]

Found this

PHP Code:

if ($post[award]=="1")
    {
         $result=$DB_site->query("SELECT * FROM useraward WHERE userid='$post[userid]' AND awardsid=aw ");
       while ($awardsp=$DB_site->fetch_array($result))
        {
          eval("\$post[hasaward] .= \"".gettemplate("awards")."\";");

        }
   } 


This is the only query performed in getpostbit, may there be some messed codes in Lesanes Award-Hack? Me and some other Useres got even more Problems with it. Sometimes donating an Award doubles all DB-Entries which is only shown in user.php but not in awards.php.

[Xenon]

ok this is the problem which produces the high query ammount.

you should read the supportthread of lesanes hack, maybe there is a new method to save this query.

if this query couldn't be saved without changen a larg ammount of code, maybe a cache can help.

you should suggest that

[TheSaint-AeD]

Thx 4 your help, Xenon, but I don't know enough about PHP + MySQL to solve this Problem myself. I read the whole Support Thread (which is quite long ) 3 Times because I couldn't believe noone else notices the Problems with the Hacks, but it seems, as if I was the only one.

I don't think there's gonna be a fix of Lesane, as he already had to give up his "Tribute to 2Pac" Board due to his lack of Time. Could anybody else of the vb.org-Staff take care of that Thread? Missing the Award-Hack would be missing one of the most ruling Features of vB

[Lesane]

You can't take out that query. It's just a query wich is needed to get the awards from an user.

I'm no supporter for while loops in the posbit as well. You could move the awards to the userinfo page.

How many awards do you have?

[TheSaint-AeD]

I've got about 20 Awards in total and an average of 1-2 per User. Due to the other Problem (Awards get Duplicated when giving to certain users) I deleted all Awards, but still the same Problem.

Man, Lesane, that was fast . Are you the Neighbour of Xenon? *G*

[Lesane]

A new version of the awards hack is coming within a few weeks. The new version will have no query's in the postbit anymore.

First i am going to finish the new version of the store hack.

[TheSaint-AeD]

I'll be looking nervously forward to it, refreshing vb.org every 10 minutes ^^ Btw, Lesane: You had a great board, I'm missing it already though I had only few time to visit it.

[Xenon]

ahh, good to hear lesane

@Saint: hmm, you can try this, it will save at least some of the queries if you have more posts of the same user on one page:

change the postbit code to this:

PHP Code:

if ($post[award]=="1")
    {
         if(isempyt($awardresultcache[$post['userid']]) $awardresultcache[$post['userid']]=$DB_site->query("SELECT * FROM useraward WHERE userid='$post[userid]' AND awardsid=aw ");
       while ($awardsp=$DB_site->fetch_array($awardresultcache[$post['userid']]))
        {
          eval("\$post[hasaward] .= \"".gettemplate("awards")."\";");

        }
   } 


then in getpostbitfunction after

PHP Code:

    global $bbuserinfo,$session,$ignore,$cookietimeout; 


add this:

PHP Code:

 static $awardresultcache 


i think this should reduce the query ammount

[TheSaint-AeD]

I already deinstalled the Hack and went back to less than 40 Querries in showthread .

@ Xenon: Danke trotzdem vielmals f?r die schnelle und kompetente Hilfe

[Xenon]

you're welcome

hmm, du solltest nicht ?ber 35 kommen bei showthread, unter 30 w?re noch besser

[Erwin]

Originally posted by TheSaint-AeD
I already deinstalled the Hack and went back to less than 40 Querries in showthread .

40 queries is still significant. Try to reduce it a bit more - ideally to 25 or less, since showthread is the most commonly accessed page on a forum.

[TheSaint-AeD]

hrhr

I know that ~35 Querries is a lot on showthread, but my Servers are good enough to face this, and I don't wanna miss any of the Features causing these Querries. Visit me on http://foren.gslallianz.de if you want Still a lot under Construction, but already some interessting things to see

--------------------

Xenon, there seems to be another Query-Loop. Seems to have something to do with the Quick-Delete Post Hack, I'm investigating atm. and post the Results as soon as possible.

[Xenon]

yes this is important to put away

don't use queries in loops .

most of the time these queries can be avoided with good joined queries.... but not always