cfquery cachedwithin not always working in CF 10

Question

ColdFusion 10, update 8. Running on Windows 2008 R2, IIS 7.5, Java 7, update 17. Microsoft SQL Server 2005. Maximum number of cached queries: 1000

Here is my query from a cffunction:

<cfquery name="local.qryDOTCensusPubSByCCStAlpha" datasource="#variables.dsn#" cachedwithin="#CreateTimeSpan(0, 1, 0, 0)#">
SELECT
    dot.CENSUS_NUM, dot.PHY_CITY, dot.[NAME]
FROM
    tblDOTCensusPub dot
WHERE
    dot.PHY_NATN = <cfqueryparam value="#arguments.PHY_NATN#" cfsqltype="CF_SQL_VARCHAR">
    AND dot.PHY_ST = <cfqueryparam value="#arguments.PHY_ST#" cfsqltype="CF_SQL_VARCHAR">
    AND dot.cUseForTD = 'Y'
    AND NOT EXISTS (SELECT * FROM tblDOTDisabled WHERE CENSUS_NUM = dot.CENSUS_NUM)
<cfif arguments.vcAStart EQ arguments.vcAEnd>
    AND LEFT(dot.[NAME], 1) = <cfqueryparam value="#arguments.vcAStart#" cfsqltype="CF_SQL_CHAR">
<cfelseif NOT (arguments.vcAStart EQ "0" AND arguments.vcAEnd EQ "Z")>
    AND LEFT(dot.[NAME], 1) >= <cfqueryparam value="#arguments.vcAStart#" cfsqltype="CF_SQL_CHAR">
    AND LEFT(dot.[NAME], 1) <= <cfqueryparam value="#arguments.vcAEnd#" cfsqltype="CF_SQL_CHAR">
</cfif>
ORDER BY
    dot.[NAME]
</cfquery>

This query used to work with the cachedwithin argument in ColdFusion 9.01. In ColdFusion 10, it is random on whether it caches or not. Most of the time it does not cache. At first I thought that perhaps the "AND NOT EXISTS (SELECT * FROM tblDOTDisabled WHERE CENSUS_NUM = dot.CENSUS_NUM)" might exempt the query from being cached because the results from that sub-query may vary. However, even if I remove that clause the query still refuses to cache.

I have two servers that I'm working with; production and development.

The production server may get enough hits to clear the queries out of the cache (but not that likely).

The development server has almost no hits so this query should be cached.

Any ideas on why this query might not cache in CF 10?

UPDATE:
Below are snippets from the debug output on the ColdFusion server:

Query in this question:

local.qryDOTCensusPubSByCCStAlpha (Datasource=tdDOTCensusPub, Time=62ms, Records=3962) in ~~~~hidden~~~~\cfc\DOTCensusPub.cfc @ 12:28:41.041

Another query on the same page that is successfully getting cached:

local.qryTop5JobPosters (Datasource=truckdriver, Time=16ms, Records=5, Cached Query) in ~~~~hidden~~~~\cfc\tdJobs.cfc @ 12:28:41.041

Notice that the debug output will include "Cached Query" in the debug output if the query is cached.

Also, I am certain that the parameters are not changing. This is the query string that I'm using to reload the page:

?PHY_NATN=US&PHY_ST=MN&vcAStart=M&vcAEnd=M&SR=1&MT=2

All of the arguments passed to the CFFunction are from the URL. I'm simply reloading the page and the query should be cached after the first page load.

UPDATE:

I just noticed some really odd behavior. I modified the “local.qryDOTCensusPubSByCCStAlpha” query (updated in question as well) to be slightly more efficient when the start and end letter were the same letter. When I refreshed the page, the debug section displayed the old query. I had to press Ctrl-F5 to get the debugging section to display the updated query. When I pressed Ctrl-F5 again, it then displayed that the query was cached. So I’m now thinking that CF is telling the browser that nothing has changed on the page (even though the query in the debugging section DID change) so the browser displays the cached page including the OLD debugging output. Using Firebug seems to verify this as the page is returning a “304 Not Modified” status, even though the debugging section should have changed. It looks like whenever normal page reloads are used, the page will be displayed from the browsers cache and not have the correct information for debugging.

Can someone else verify this? Just create a simple cfm template that calls a CFC function that executes a query with cachedwithin argument, load the page (making note of the debug info), change the query in the cfc and reload the page. Did the query in the debug section change with a normal page reload? Does it change with Ctrl-F5?

I don't think that a 304 status should be used with any page that includes debugging info. The timing and other debug info would often be out-of-date.

Note: Ctrl-F5 forces the browser to reload a page and all of the pages objects (images, javascript, etc.).

Answer 1

Though I am not sure but can you tell that it has not exceeded the limit Maximum number of cached queries: 1000

Answer 2

I've actually had the opposite of this issue before. We had a project that would cache the database structure and throw undefined errors when the structure was updated. Our problem was this:

Select *
From tbl_data

Our fix to prevent caching was this:

Select t.*
From tbl_data AS t

I'm not saying this is the cause of the issue, I'm just saying that it may not be working because you are using the dot. and the Select * methods. Try either removing the dot. aliases or updating your SELECT * to something like SELECT t.*

Here is some more information about this issue:

http://www.bennadel.com/blog/194-ColdFusion-Query-Error-Value-Can-Not-Be-Converted-To-Requested-Type.htm