[Theforum] [---Dev] RE: [---tent] Article cleanup issue
John Handelaar
genghis at members.evolt.org
Tue Jul 23 18:16:11 CDT 2002
> -----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
More information about the theforum
mailing list