The more I think about this, the more I think that my cached query is getting overwritten whenever someone visits another topic, because it's the same query name. I think I might try to dynamically name the query based on the topic name, then I should be guaranteed an individual cache per topic.
Cachedwithin comes into effect when the properties, SQL statement, datasource and query-name remain unchanged (Additionally, username and password, which also implicitly remain the same in your case). The createtimespan(0,1,0,0) value means that ColdFusion will cache the result of the first query for one hour.
If, within the hour, ColdFusion executes a subsequent query having the same properties, it will return the result-set from the cache, rather than make another trip to the database. Therefore, a cached query, by its very definition, is unsuitable for dynamically-changing data.
What I ended up doing was creating a variable-based query name, named after the current topic, like so:
<cfset qname = "q" & topic_name> <cfquery name="#qname#" datasource="dsn" cachedwithin="#createtimespan(0,1,0,0)#"> SELECT (columns I need) FROM pages WHERE topic = <cfqueryparam value="#topicname#" cfsqltype="cf_sql_varchar"> </cfquery> <cfquery name="getpagedetails" dbtype="query"> SELECT (columns I need) FROM #qname# WHERE page_id = <cfqueryparam value="#page_path#" cfsqltype="cf_sql_varchar"> </cfquery>
This way I (in theory) have a unique cache for each topic, and my second query draws from that cache.
Upon implementation, I've noticed a significant reduction in database hits (since we still use Access on this site, any hit creates an .ldb file, so I can easily tell if it's getting hit). Prior to the change, the ldb file was basically continuously there, appearing and reappearing a few times per second. Now, several minutes can go by with no ldb, even at the peak of the day, and pages are getting rendered almost instantly. If I add up all my query execution times for a page, I'm getting 5-7 ms total.
So it has to be doing something.
BreakawayPaul, thanks for sharing that with us. Please mark it as the correct answer. It is the kind of information that will benefit others.