Cached query question

[BreakawayPaul]

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.

[WolfShade]

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,

^_^

[BreakawayPaul]

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?

[WolfShade]

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,

^_^

[BreakawayPaul]

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

[haxtbh]

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

[BreakawayPaul]

Thanks. That makes sense.

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

[BKBK]

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. 

[BreakawayPaul]

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?

[BKBK]

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.