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