Matt
Yes it is not running on CFMX -
As for the length of the query meaning to much.. I am not sure it really
does - I know on one of my heaviest traffic sites 40,000 unique per week
- (still running an access database), Is set to cache every 20 minutes
and I see major performance improvements and overall database seems to
run better..
The query in question is like so -
<cfquery name="GetModuleFeatures" datasource="#dsndata#">
SELECT mf.iModule, mf.iApplication, mf.sModuleFeature,
mf.sFeatureIndent, mf.sFeatureSubHeading, mf.iModuleID, mf.LTC_Related,
mf.Reh_Related, mf.Inp_Related, mf.Out_Related,
am.smodulename
FROM ModuleFeatures mf, applicationmodules am
WHERE mf.imodule = am.iapplicationmoduleID AND #ProviderPage# = 1 AND
iApplication = #ApplicationPage#
ORDER BY iApplication, iModule, sFeatureSubHeading
</cfquery>
It's by no means complicated. It is nothing fancy or even "secure" it
simply takes 2 variables that i set on whatever page I want to control
this particular query and dynamically sets the query. However - I find
that it is running pretty slow or sluggish - more so then other
complicated queries I have - So I thought that if I extended the cache
- it would help. It's not a high traffic site at all.
Page really only needs to pull from database once - then anytime the
client changes a bullet point. - so NOT very often - they just wanted
this fancy page admin I built in. A change may happen a few days in a
row - then not for 4 months.
I am definately open to suggestions.
Actually a silly question - being not all that familiar with the details
of caching - Since that query is dynamic - how does the cache work?
dynamic piece-
....AND #ProviderPage# = 1 AND iApplication = #ApplicationPage#
It seems that if the query was set to
....AND Rehab = 1 AND iApplication = 2
it would cache for say 20 minutes with code like (
cachedwithin="#createTimeSpan(0,0,20,0)#" )
Now - someone comes by in 10 minutes and the dynamic query is reset to
....AND Rehab = 2 AND iApplication = 3
It would mean that those values are not queried nor stored in cache -
and database has to requery the db?
How does that work? In this case does cache become useless or cache
stores the different possibilities and uses them as needed?
I appreciate the assistance!
jay
Matt Robertson wrote:
But if this is pre-cfmx then you have to readonly cflock the query ,
since
it contains an app var, right? :(
For the sake of argument, does the length of the caching mean so much?
I
mean, if you set it to 10 minutes wouldn't even that pretty much take a
mountain of weight off the db? 1 hour would seem, on the surface and
with
admittedly little detailed info, to be more than plenty to assure a
well-rested db.
--Matt Robertson--
MSB Designs, Inc.
http://mysecretbase.com <http://mysecretbase.com>
----- Original Message -----
From: "S. Isaac Dealey" <mailto:[EMAIL PROTECTED]> <[EMAIL PROTECTED]>
To: "CF-Talk" <mailto:[EMAIL PROTECTED]>
<[EMAIL PROTECTED]>
Sent: Thursday, February 06, 2003 4:47 PM
Subject: Re: Really Long Cache Period- advisable?
I'd recommend using CachedAfter instead of CachedWithin...
<cflock scope="application" type="exclusive" timeout="30">
<cfparam name="application.contentcachedafter" default="#now()#">
<cfset request.contentcachedafter = application.contentcachedafter>
</cflock>
<cfquery name="rscontent"
cachedafter="#application.contentcachedafter#">
SELECT ...
</cfquery>
When you update the content, simply update the cachedafter variable
<cfquery > UPDATE ...</cfquery>
<cflock scope="application" type="exclusive" timeout="30">
<cfset application.contentcachedafter = now()>
</cflock>
Assuming the application never times out, the query will remain cached
indefinitely, so a day, 7 days, 30 days, etc.
s. isaac dealey 954-776-0046
new epoch http://www.turnkey.to
<http://www.turnkey.to>
lead architect, tapestry cms http://products.turnkey.to
<http://products.turnkey.to>
tapestry api is opensource http://www.turnkey.to/tapi
<http://www.turnkey.to/tapi>
certified advanced coldfusion 5 developer
http://www.macromedia.com/v1/handlers/index.cfm?ID=21816
<http://www.macromedia.com/v1/handlers/index.cfm?ID=21816>
Hello list - I have a question about caching results from
a SQL query.
Short question : I want to set a query cache for like 7
days.
very bad? or not a big deal?
Longer Question / Explanation:
I used a quick access database to setup content for a
site. I output 1
main grouped queries - which is dynamically set by page
names on 12
different pages - The code just switches a simple key in
the WHERE
statement.
I planned on using CFCOntent to create static pages BUT to
ease
development and content - I built in a set of admin
functions to add and
remove information and quickly reorganize it. Client loved
it so much
they want to have the ability to use that all the time.
Problem is I
don't want to maintain different pages.
I considered adding a long cache (like 7 days) to limit
the amount of
times the query is run.......And on the admin page -
having a button
that can immediately refresh that cache.
Adivisable or not? Is it best for me simply to do it the
right way and
have admin section changes publish everytime through
cfcontent?- I am
just nervouse because the pages are complex and use alot
of flash,
javascript and css code.
I hope I am clear - Any advice is much needed and greatful
Jay
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~|
Archives:
http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
<http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4>
Subscription: http://www.houseoffusion.com/cf_lists/index
<http://www.houseoffusion.com/cf_lists/index> .
cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
<http://www.thenetprofits.co.uk/coldfusion/faq>
This list and all House of Fusion resources hosted by
CFHosting.com. The place for dependable ColdFusion
Hosting.
Unsubscribe: http://www.houseoffusion.com/cf_lists/uns
<http://www.houseoffusion.com/cf_lists/uns>
ubscribe.cfm?user=633.558.4
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
