[Theforum] [---Dev] RE: [---tent] Article cleanup issue

[John Handelaar]

> -----Original Message-----
> From: theforum-admin at lists.evolt.org
> [mailto:theforum-admin at lists.evolt.org]On Behalf Of .jeff
> Sent: 24 July 2002 00:04
> To: theforum at lists.evolt.org
> Subject: RE: [Theforum] [---Dev] RE: [---tent] Article cleanup issue


Hi again Jeff

> ><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> > 1.  Your story query has no LIMIT clause, so you're
> >     effectively loading almost the entire DB into memory
> >     only to throw 98% of the result away.
> ><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
>
> the query doesn't get thrown away.  it gets cached by cf to be
> used for any subsequent database requests that have a matching sql string.

But no other sql string which 'matches' would require
the entire record set, unless the CF page is behaving
in the way you imply here...

> ><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> >     You're also continuing to load over 800 records into
> >     an array *after* it's already got all the records
> >     you need.
> ><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
>
> huh?  where am i loading those records in to an array?

Sorry, I'm assuming that a script which receives 902
rows as a result is processing them as an array once
they get there.  My turn to go 'huh?' then :-)

> ><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> > 2.  The story table could (but I assume does not, given
> >     the nature of the queries being run) contain a field
> >     in each row containing the total number of comments
> >     associated with that story, rather than calculating
> >     that number every time.
> ><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
>
> correct and should be changed.
>
> that doesn't keep us from having to run a subquery that returns a
> count of new comments for each article since the user last logged in.

I guess that means that somewhere we have a display which
says 'X comments, Y new' ?

I vote ditch it then...

> ><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> > 3.  Same goes for average rating
> ><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
>
> already there as well as a column that stores the total number of ratings.

Kewl.

> ><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> > 4.  To get your total (if it's required, but that's
> >     another story), I'd suggest a better approach would
> >     be to run a second query a la MySQL's SELECT
> >     COUNT(*) FROM stories, which is lightning fast and
> >     returns only one row as a result.
> ><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
>
> no offense, but yuck.  i'm willing to mess around with the source
> code and see if  this shows measurable improvement though.

Again, in the follow-up to this I posted, I wanted to find
out if we really are closing and re-opening connections as
opposed to using pconnects...

> ><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> > In addition, it'd be interesting to know how often under
> > the current procedure, this 10-second query actually
> > runs.  I had been led to believe that the maximum number
> > of times this would occur was once every 20 minutes for
> > a maximum of one user during that time frame?
> ><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
>
> with one additional constraint -- provided nothing about that sql
> string changes from one request to the next.  one of the things
> that can change is the last logged in date for grabbing new
> comments since the last visit.  that could be pulled out in to a
> separate query but would then have to be run inside the loop that
> displays the articles (triple yuck).
>
> ><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> > If that's still true, I'm almost not seeing the issue,
> > though I do respect your wish to remove even that
> > minuscule bottleneck.
> ><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
>
> isn't it making more sense now why it's a bigger issue to me than
> others seem to think it should be?

I'll take the obvious point here then.  Serendipity
showed me MartinB's article about cron as I loaded
the front page last time...

Perhaps we could run that 'heavy' query as a server
cron job every 10 mins to hide it from users?  If that,
combined with an unbothered-attitude towards the
(imho) unnecessary subdivision-by-login detail about
what's new since last logon, then maybe this ruck of
ours provides a solution within the day (yay!)...

/me is often of the opinion that bashing heads gets
results quickly providing neither party gets too
bothered about being polite to each other.  Which isn't
something we've worried about today >:-)

------------------------------------------
John Handelaar

T +44 20 8933 1494       M +44 7930 681789
F +44 870 169 7657   E john at userfrenzy.com
------------------------------------------
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.375 / Virus Database: 210 - Release Date: 10/07/2002