Query Caching with Script Based Queries

I could not get query caching to work for me with Query objects, and I couldn't work out why for the longest time.  For example:

<cfscript>
    local.query = new Query(sql="select * from holidays",
                            datasource="myDS",
                            cachedWithin=createTimespan(0, 1, 0, 0));

    local.result = local.query.execute().getResult();
</cfscript>

No matter how many times I refreshed this test script, the SQL result would never cache.

The reasons why didn't click until I kept looking at the query output:

qryname49118 (Datasource=myDS, Time=1ms, Records=8) in /opt/coldfusion9/CustomTags/com/adobe/coldfusion/base.cfc @ 16:19:14.014

select * from holidays

Since I didn't need to set a name for the query, as I had a query object, the Query.cfc was giving the underlying <cfquery> a unique name on each and every call.  Since the name was unique, when ColdFusion went to look to see if the query was cached, it never found anything for that query name.

Changing the above query to:

<cfscript>
    local.query = new Query(name="test",
                            sql="select * from holidays",
                            datasource="myDS",
                            cachedWithin=createTimespan(0, 1, 0, 0));

    local.result = local.query.execute().getResult();
</cfscript>

Suddenly means the query is cached, and all is well again in the world, as the name of the query is explicitly set.

It would be great if the name of the query was a md5 hash of the SQL that is being passed in, and then at least you wouldn't have to bother naming the query something specific if you want to use the query cache. In the mean time, if you run into this issue, you now know how to work around it.

Add Comment

Name * Email URL Code

Enter Code * Comment *