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

.jeff jeff at members.evolt.org
Tue Jul 23 18:03:34 CDT 2002


john,

><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> From: John Handelaar
>
> > i'd like to restate *again* that returning the 800
> > records to coldfusion is *not*, i repeat *not*, the
> > issue here.
>
> If that were true, you wouldn't be trying to reduce the
> number.  (For the rest of the folks here, it's now 902.)
><><><><><><><><><><><><><><><><><><><><><><><><><><><><><

we'll have to address database platform issues in the near future.  i'm just trying to stop the bleeding for now.

><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> 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.

><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
>     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?

><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> 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.

><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> 3.  Same goes for average rating
><><><><><><><><><><><><><><><><><><><><><><><><><><><><><

already there as well as a column that stores the total number of ratings.

><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> 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.

><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> 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?

.jeff

http://evolt.org/
jeff at members.evolt.org
http://members.evolt.org/jeff/




More information about the theforum mailing list