Highlighted

Cached query question

Contributor ,
Apr 04, 2017

Copy link to clipboard

Copied

We have a fairly large site that's divided into distinct topics. The site runs off of a single database that contains some page details, like the contact person for that page, along with some other info.

Since I don't want to query the entire database every time someone hits a page, I use a cached query, and I do one for each topic (since some topics get a lot of traffic, and some get almost none).

What I have looks something like this:

<cfset request.qname = request.topicname> (this topic name is set in application.cfc)

<cfquery name="#request.qname#" datasource="myDSN" cachedwithin="#createtimespan(0,8,0,0)#">

SELECT....

FROM...

WHERE topicid = <cfqueryparam value="#request.topic_id#" cfsqltype="cf_sql_numeric"> (this topic ID is set in application.cfc)

</cfquery>

That pulls the info for all pages in the topic and holds it for 8 hrs.  Then for each subsequent page visit in that topic, I have this:

<cfquery name="details" dbtype="query">

SELECT ...

FROM #request.qname#

WHERE page_name = (current page)

</cfquery>

Now, I've always assumed that if the first query is cached, only the second query will run.  But after wrapping some code around the first one (using cfcache) to see if the cache exists, but so far it looks like it always runs no matter what.  Is this the expected behavior?  I always thought that the cached one is ignored until the cache expires, but maybe I'm not understanding how this works.

Views

904

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

Cached query question

Contributor ,
Apr 04, 2017

Copy link to clipboard

Copied

We have a fairly large site that's divided into distinct topics. The site runs off of a single database that contains some page details, like the contact person for that page, along with some other info.

Since I don't want to query the entire database every time someone hits a page, I use a cached query, and I do one for each topic (since some topics get a lot of traffic, and some get almost none).

What I have looks something like this:

<cfset request.qname = request.topicname> (this topic name is set in application.cfc)

<cfquery name="#request.qname#" datasource="myDSN" cachedwithin="#createtimespan(0,8,0,0)#">

SELECT....

FROM...

WHERE topicid = <cfqueryparam value="#request.topic_id#" cfsqltype="cf_sql_numeric"> (this topic ID is set in application.cfc)

</cfquery>

That pulls the info for all pages in the topic and holds it for 8 hrs.  Then for each subsequent page visit in that topic, I have this:

<cfquery name="details" dbtype="query">

SELECT ...

FROM #request.qname#

WHERE page_name = (current page)

</cfquery>

Now, I've always assumed that if the first query is cached, only the second query will run.  But after wrapping some code around the first one (using cfcache) to see if the cache exists, but so far it looks like it always runs no matter what.  Is this the expected behavior?  I always thought that the cached one is ignored until the cache expires, but maybe I'm not understanding how this works.

Views

905

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
Apr 04, 2017 0
LEGEND ,
Apr 04, 2017

Copy link to clipboard

Copied

It's my understanding that if the EXACT same query is run, it pulls from the cached query, but if anything is different (like variable value), then that's considered a brand new query and goes to the database.  So if you refresh the same page over and over and over and never use a different variable value, then you should be getting data from the cached query, until the 8 hour limit is reached.

V/r,

^_^

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...
Apr 04, 2017 0
Contributor ,
Apr 04, 2017

Copy link to clipboard

Copied

This was also my understanding.

What I did to test (and maybe this code is wonky) is this:

<cfcache name="cachecheck" action="get" id="queryname">

<cfif StructKeyExists(variables,"cachecheck")>

<p>Query Runs</p>

<cfquery name="queryname" datasource=".....> (cached query)

</cfquery>

</cfif>

I can refresh that page a hundred times, and the query re-runs.  At least using that code it does.  Maybe there's a better way to see if it's really running? Or maybe it runs the code but doesn't hit the db?

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...
Apr 04, 2017 0
LEGEND ,
Apr 04, 2017

Copy link to clipboard

Copied

BreakawayPaul  wrote

Or maybe it runs the code but doesn't hit the db?

That's what I'm thinking; but I am not aware of any method to check if a query is coming from db or memory.  You could use getTickCount(), I suppose, to see if there is a difference in overhead?

V/r,

^_^

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...
Apr 04, 2017 0
Contributor ,
Apr 04, 2017

Copy link to clipboard

Copied

I suppose I could also make a change to the database then reload the page and see if the change shows up!

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...
Apr 04, 2017 0
Advocate ,
Apr 04, 2017

Copy link to clipboard

Copied

The code will always run, it will just go via CFs cache first to get the results and wont hit the database.

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...
Apr 04, 2017 2
Contributor ,
Apr 04, 2017

Copy link to clipboard

Copied

Thanks. That makes sense.

I did change the db and reloaded the page.  The changes didn't show, so it was getting the cache.

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...
Apr 04, 2017 0
Adobe Community Professional ,
Apr 05, 2017

Copy link to clipboard

Copied

BreakawayPaul  wrote:

I can refresh that page a hundred times, and the query re-runs. 

-----------------------------------

A cached query will (re)run. Not to the database, but to memory. 

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...
Apr 05, 2017 0
Contributor ,
Apr 06, 2017

Copy link to clipboard

Copied

BKBK  wrote


A cached query will (re)run. Not to the database, but to memory. 

So there's probably no point in me trying to figure out code to keep it from running before the cache expires, correct?

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...
Apr 06, 2017 0
BKBK LATEST
Adobe Community Professional ,
Apr 06, 2017

Copy link to clipboard

Copied

Yes, given your original question, I do believe there's no point figuring out code to keep the cached query from running before its timespan expires. The cached query is essentially a variable whose value is stored in memory. Therefore, fetching it incurs practically the same cost as fetching the value of any comparable object stored in memory.

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...
Apr 06, 2017 2