Caching has been used for years to improve performance of applications. So it is very much important to control caching properly to get highest possible benefit out of it.
Please find the method below to manage query caching properly. First I will demonstrate the logic and then follow it up with code.
The problem with traditional way of query caching:
In general we use cachedAfter or cachedWithin parameter to cache query and to break cache forcefully we make use of cfobjectcache method. This tag(cfobjectcache) clears all queries from cache at a time. Here it became something unwanted. Basically it should be as, if we need to update a query in cache, then only that particular one should have been updated, not all queries.
The logic for managing caching of query result sets separately :
In place of using cachedAfter or cachedWithin for query caching, we need to put the query result set into cache. In cfcache tag, we have actions as get and put to retrieve and insert objects in cache respectively. Using action “put” we can put the query result set to cache with a specific name. So once the query is in cache, we don’t need to run the query to fetch it. We can get the same from cache itself. And when there is any change to DB(insert, update or delete), we need to remove the query from cache by flushing that particular query by name and again put the updated query to cache.
Description: The function below first check the existence of the query in cache. If found return the same else run the query and put the result set to cache for future use.
<cffunction name="getProduct" access="public" output="true" returnType="query" hint="Get product details" >
var returnQry = queryNew("");
<!--- Getting the query from cache with name testQry. --->
<cfcache action="get" id="testQry" name="qryObj">
<!--- If query exists in cache then return the same else run the query and put the result set in cache --->
<cfset returnQry = qryObj>
<cfquery name="qryObj" datasource=”some data source”>
SELECT * FROM product;
<!--- Put the query to cache with name: testQry --->
<cfcache action="put" id="testQry" value="#qryObj#">
<cfset returnQry = qryObj>
Description: While we make db changes, we need to break cache for the particular query. Here I have given example for insert operation. We need to follow the same for update and delete operations as well.
<cffunction name="insertProduct" access="public" returnType=”void” hint="Inserts records to product table">
<cfargument name="name" required="false" type="string" hint="Takes product name">
<cfargument name="number" required="false" type="numeric" hint="Takes product name">
<cfargument name="color" required="false" type="string" hint="Takes product name">
<cfquery name="insertProduct" datasource=”some data source”>
INSERT INTO product (name, number, color)
<cfqueryparam cfsqltype="cf_sql_varchar" value="#ARGUMENTS.name#">
, <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.number#">
, <cfqueryparam cfsqltype="cf_sql_varchar" value="#ARGUMENTS.color#">
<!---Clearing the query( testQry) from cache --->
<cfcache action="flush" id="testQry">