cfquery caching question

[BreakawayPaul]

I have a question (actually two) about how caching works with cfquery so I can make sure that what I'm doing on our website makes sense. (We're on CF10)

I have a database that stores certain details about each of the web pages on our site.  Contact ID, title, date added/updated, whether it's a recent or featured item, etc.  The unique identifier for each page is the path (/folder/subfolder/page.cfm).  Since out site is organized in several major topic areas, there's a column to identify which topic the page is in (and that's the same as the folder name that set of pages resides in).

Some or topics get tons of hits, and some get very few, so I've been doing this:

<cfquery name="getalltopics" 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 getalltopics
WHERE page_id = <cfqueryparam value="#page_path#"  cfsqltype="cf_sql_varchar">
</cfquery>

So here's my question.  I know that caches only come into play if the query that's being run identically matches the one that's cached. So in my mind, the fist query would make an individual cache of each of our topics, as the pages were viewed during the day.  My second cache would grab the page details from the first, with several topics cached, how would the second one know which cache to find the page details in?  The query works, and is fast, but I'm just wondering if I need to specify the topic ID in the second query.

My second question is about cache time.  If I do createtimespan(0,1,0,0), does that cache last for an hour after it's created, or does it last for an hour after the last time that query is run?

Thanks!

[BreakawayPaul]

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.

[BKBK]

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.

[BreakawayPaul · Accepted solution]

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.

[BKBK]

BreakawayPaul, thanks for sharing that with us. Please mark it as the correct answer. It is the kind of information that will benefit others.

[BreakawayPaul]

Sure thing!