My friend who is learning ColdFusion recently ran into the issue of not being able to use query caching since he was also using cfqueryparam. Since cfqueryparam is vital to any dynamic query, he wisely chose to lose the the caching and keep the cfqueryparam. I mentioned that he could roll his own caching and wrote up this quick example for him.
Consider this CFC method:
<cffunction name="getData" returnType="query" access="public" output="false"> <cfargument name="name" type="string" required="false"> <cfargument name="age" type="numeric" required="false"> <cfargument name="active" type="boolean" required="false"><cfset var data = queryNew("id,name,age,active","integer,varchar,integer,bit")> <cfset var x = ""> <cfset var q = ""> <cfset var go_to = "">
<cfloop index="x" from="1" to="20"> <cfset queryAddRow(data)> <cfset querySetCell(data,"id",x)> <cfset querySetCell(data,"name","User #x#")> <cfset querySetCell(data,"age",randRange(20,90))> <cfset querySetCell(data,"active",randRange(0,1))> </cfloop>
<cfquery name="q" dbType="query"> select id, name, age, active from data where 1=1 <cfif structKeyExists(arguments, "name")> and name = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.name#"> </cfif> <cfif structKeyExists(arguments, "age")> and age = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.age#"> </cfif> <cfif structKeyExists(arguments, "active")> and name = <cfqueryparam cfsqltype="cf_sql_bit" value="#arguments.active#"> </cfif> </cfquery>
<cfscript> go_to = createObject("java", "java.lang.Thread"); go_to.sleep(200); //sleep time in milliseconds </cfscript>
<cfreturn q>
</cffunction>
Obviously this is a contrived example - but it does show a pretty standard example of requesting data with a few potential filters applied. The "sleep" code is not standard and is just there to force the query to be slow.
Now let's look at a modified version:
<cffunction name="getDataBetter" returnType="query" access="public" output="false"> <cfargument name="name" type="string" required="false"> <cfargument name="age" type="numeric" required="false"> <cfargument name="active" type="boolean" required="false"><cfset var data = queryNew("id,name,age,active","integer,varchar,integer,bit")> <cfset var x = ""> <cfset var q = ""> <cfset var key = "">
<cfset var go_to = "">
<!--- generate key from arguments. ---> <cfset key = ""> <cfif structKeyExists(arguments, "name")> <cfset key = key & "NAME=#arguments.name# "> </cfif> <cfif structKeyExists(arguments, "age")> <cfset key = key & "AGE=#arguments.age# "> </cfif> <cfif structKeyExists(arguments, "active")> <cfset key = key & "ACTIVE=#arguments.active# "> </cfif>
<!--- do we have the cached version? ---> <cfif structKeyExists(variables.cache, key)> <cfreturn variables.cache[key]> </cfif>
<!--- continue on then... ---> <cfloop index="x" from="1" to="20"> <cfset queryAddRow(data)> <cfset querySetCell(data,"id",x)> <cfset querySetCell(data,"name","User #x#")> <cfset querySetCell(data,"age",randRange(20,90))> <cfset querySetCell(data,"active",randRange(0,1))> </cfloop>
<cfquery name="q" dbType="query"> select id, name, age, active from data where 1=1 <cfif structKeyExists(arguments, "name")> and name = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.name#"> </cfif> <cfif structKeyExists(arguments, "age")> and age = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.age#"> </cfif> <cfif structKeyExists(arguments, "active")> and name = <cfqueryparam cfsqltype="cf_sql_bit" value="#arguments.active#"> </cfif> </cfquery>
<cfscript> go_to = createObject("java", "java.lang.Thread"); go_to.sleep(200); //sleep time in milliseconds </cfscript>
<!--- store to cache ---> <cfset variables.cache[key] = q> <cfreturn q>
</cffunction>
Let me focus on what I changed. First - outside of the method my CFC's init method created a structure to store my cache. This was done like so:
<cfset variables.cache = structNew()>Inside my getDataBetter method, I create a key based on the arguments passed in. The point of this is to create a unique key based on the arguments passed in. This unique key is then checked to see if it exists in the structure. If it does - I simply return the query immediately. If not - I continue with creating the query and store the result in the cache.
Run a quick test and you will see it runs well:
<cftimer label="no arguments" type="inline"> <cfset data = mycfc.getDataBetter()> </cftimer> <p /> <cftimer label="active=true" type="inline"> <cfset data = mycfc.getDataBetter(active=true)> </cftimer> <p /> <cftimer label="no arguments" type="inline"> <cfset data = mycfc.getDataBetter()> </cftimer> <p /> <cftimer label="active=true" type="inline"> <cfset data = mycfc.getDataBetter(active=true)> </cftimer>And the results:
no arguments: 204msactive=true: 203ms
no arguments: 0ms
active=true: 0ms
There are a lot of problems with this cache though. First off - the number of possible key combinations is huge. Secondly - your typically want to build your API so you can force the method to get new data even if it does exist in the cache. I'd typically have an argument for that, and a completely separate method to re-initialize the cache.
To handle the size of the cache you can do a few things. You could do a simple structCount and see if the struct is too big. But how do you determine what to remove? You could either pick one by random, or add additional metadata to your cache that includes when the key was created, and how often it was used.
Anyway - this was just meant to be an example. Once things get complex you would probably want to move away from this solution into something more powerful. Check this blog entry for more suggestions: