[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: 23 July 2002 22:27
> To: theforum at lists.evolt.org
> Subject: RE: [Theforum] [---Dev] RE: [---tent] Article cleanup issue
>
> 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.)

> how would you go about accomplishing the querying and still
> retain the existing functionality?  ie, return the most recent 10
> articles, give the user a way of knowing how many total available
> pages to browse though, and the ability to browser to them.
> please be as detailed as possible -- i wouldn't want to miss anything.

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.  You're also continuing to load over
    800 records into an array *after* it's already got all the
    records you need. MySQL does this with LIMIT - what
    does Oracle have?   (Granted, bad/no indexing may also
    contribute to this, but it's still better to have both than
    only one, or neither).

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.

3.  Same goes for average rating

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.

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?  If that's still true, I'm almost not seeing
the issue, though I do respect your wish to remove even that
minuscule bottleneck.

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