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.

Leave a Comment

Comments

  • Dan G. Switzer, II | October 12, 2010

    That’s a good find, I’m not sure I would have thought to add a name property.

    You should file a bug about the md5 hash of the SQL to use as the name–that would be a good enhancement.

  • AJ Mercer | October 13, 2010

    This is the way Railo currently works
    key for cache is md5(datasourcename+username+password+sql.trim())

  • Henry | October 13, 2010

    I wasn’t surprised, I remember the doc that describes how query caching work said it uses combination of SQL, query name, query params and app name as the ‘key’