cfquery caching question

Question

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 · Accepted Answer

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 · Answer

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.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded