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

rudy r937 at interlog.com
Tue Jul 23 23:26:56 CDT 2002


>>   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.
>
> I see Jeff's point about opening and closing DB connections

definitely, if you send them over as separate queries, you have this
potential connection problem, but what's worse, far worse, is that you
would have to re-execute (nearly) the same JOIN and WHERE logic to come up
with the count

by an amazing coincidence, i just wrote an article about how you can sneak
the count over in one large query using UNION (pardon my wrap) --

  Result set row count along with query results  (16 July 2002)

http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_ci
d480948_tax285649,00.html

not every optimiser is going to solve both result sets with the same pass
of the data, but if any can, oracle can

but then, with query-of-query in cf, you wouldn't run the count uery
against the database, would you   ;o)

> My turn to go 'huh?' then :-)

yes, john, coldfusion's query caching is a thing of beauty

perhaps we should emphasize that it is query *result* caching -- the data

i'm surprised nobody has mentioned query-of-query -- you know, with a more
generalized result set (e.g. not including recent comments or similar dodgy
stuff) the cache could last even longer (theoretically not until another
article changes status) and then the category lists, author lists, and
other stuff could be driven off the main cached article list without going
back to the database either...

... you can see my head's already in the next design

query caching *together with* query-of-query is a killer


rudy




More information about the theforum mailing list