Anticlue

Query Result Caching

Query result caching is ideal for querys that don't change often, normally the ideal is to obtain values from a lookup table or when dealing with the next n records.

One can specify caching withing the <CFQUERY> tag via the CACHEDWITHIN and CACHEDAFTER attributes. A side note is that one should use either the cachedwithin or cachedafter not both simultaneously in the same query.

I like using the CACHEDWITHIN attribute and setting the applications query timeout parameter in the application.cfm with a url override if we need to immediately update criteria.

Yesterday Macromedia released a tech note regarding the behavior of cached queries when maximum number is set to 0. Basically setting the value to zero in the administrator causes no upper limit to be set, and all queries with caching indicated are cached. This will eat up all server memory (ram and pagefile). It use to be in CF5 that this value represented the number of queries, I think it now represents the memory size.

So if you use query result caching alot, one probably needs to set the Maximum number of cached queries parameter intelligently. One of the goals is to keep the cached queries in memory (ram) but not on the pagefile. The reason being is that the pagefile will affect response time. Also keep in mind that if the number is set too low then the oldest cached query is just dropped beforehand.

First figure on times of high load. So determine the memory calculation of the record sets (number or rows returned * bytes per row). Add in 1024 just for overhead and wiggle room. Now you have an intelligent number one can play with via the 100 default. Also as new applications are brought online, determine if this parameter needs to be reset or new hardware needs to be purchased. Just another one of those things to add to the proactive monitoring list.