Highlighted

cfquery caching question

Contributor ,
Mar 18, 2015

Copy link to clipboard

Copied

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!

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.

Views

290

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

cfquery caching question

Contributor ,
Mar 18, 2015

Copy link to clipboard

Copied

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!

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.

Views

291

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Mar 18, 2015 0
Contributor ,
Mar 18, 2015

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 18, 2015 0
Adobe Community Professional ,
Mar 19, 2015

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 19, 2015 0
Contributor ,
Mar 20, 2015

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 20, 2015 0
Adobe Community Professional ,
Mar 20, 2015

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 20, 2015 0
Contributor ,
Mar 23, 2015

Copy link to clipboard

Copied

Sure thing!

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 23, 2015 0