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.
Code
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.
var returnQry = queryNew("");
SELECT * FROM product;
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.
INSERT INTO product (name, number, color) VALUES( , , )