I'm definitely not recommending anything involving QoQ.
As you say, databases are designed specifically for this job.
However, we also use a code table, and will do stuff like this:
(paraphrased, because it's actually split into a function and app.cfc)
<cfif NOT StructKeyExists(Application,'Countries')>
<cfquery name="Application.Countries"
datasource="#Application.Datasource#">
SELECT code, code_desc
FROM code
WHERE codetype = <cfqueryparam value="CTRY"
cfsqltype="CF_SQL_VARCHAR"/>
ORDER BY code_desc ASC
</cfquery>
</cfif>
So the CFQP can store a QEP for selecting from code table (a common type of
query), so when we do that sort of query it's more efficient, but we also don't
have the overhead of running standard queries on every request.
We never do any QoQ on Application.Countries though - if we want to join with
(for example) currencies, we'll go back to the root DB (rather than joining
Application.Countries to Application.Currencies)
>On 3/16/07, Peter Boughton <[EMAIL PROTECTED]> wrote:
>
>
>
>I've actually found that caching queries like that for VERY commonly used
>queries can actually be a performance drain... For example, I used to load
>our "code table" into the application scope. I used this code table all
>over the web site.. anytime I needed to turn "MI" to "Michigan" or "010" to
>"Athletics"... I'd run a simple udf... getAdvanceCode(tablid, tablkey) and
>it would output the appropriate value.
>
>But the code table itself is VERY large... 78,000 records
>
>I found that querying the database directly actually took less time than
>doing a query of queries... particularly when doing large numbers of such
>queries.
>
>Databases handle queries of large record sets far better than coldfusion
>does.
>
>Rick
>
>--
>> Join the Open Source Coldfusion NCAA Pool for a chance to win a 2 gig USB
>> flash drive!
>> http://www.opensourcecf.com/forums/ncaapool.cfm
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Create robust enterprise, web RIAs.
Upgrade & integrate Adobe Coldfusion MX7 with Flex 2
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:272854
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
