Boost performance by caching your CFQUERY's

It is often overlooked, but in the majority of cases, the database is generally the slowest link in the chain, while ironically, providing one of the most important roles; managing the storage and retrieval of your applications data.

Communication to the database is usually performed over TCP/IP, so there is network latency/delay. The database may also have drastically different performance characteristics as it is put under load, so the less you can use it, the better your application will perform.

The easiest way not to use a database is to remember or cache the queries that you do often. As an application builds up over time, it is not uncommon for a single request to result in many 10's of SQL statements being executed, with the application server (the CFML engine) sitting waiting for the database to return its results.

The main interface to the database from within CFML is the CFQUERY tag. This tag lets you build up very complex statements that will be sent to the database for execution. This tag offers you a couple of gems in terms of how it can help you reduce the load on your database.

<cfquery ... cachedwithin=#CreateTimeSpan(d,h,m,s)#>

The easiest way to have CFQUERY cache your queries is to pass in a time span of how long a query will remain valid for. The uniqueness of the query is determined by using the content within the CFQUERY tag (including the CFQUERYPARAM values). So you can have two different queries, with different values, that will be cached separately. If the time span elapses, then CFQUERY will resubmit the query to the database for execution and recache it. The other derivative of this, is the cachedAfter=#Date# attribute, which lets you specify a fixed date.

Both of these techniques give you primitive mechanisms to control the caching of your queries.

Couple of things to note. Cached queries are held in memory and can quickly chew up your free memory. It won't try to cache everything and will happily chuck out the least recently used cache in favor of a more recent one. So if you have a lot of different SQL statements, then you may find caching not helping your database load in the slightest.

Open BlueDragon Caching Extensions

OpenBD adds a few more features to this caching layer that helps you manage your CFQUERY cache even more. These extensions have been part of the core BlueDragon for many years now and have proven very popular with its users.

The first extension is OpenBD allows you to logically name your query, using the CACHENAME attribute. This is a unique name that will be used as the cache key instead of working it out from the body of the CFQUERY tag. You can also group your queries into domains, by using the CACHEDOMAIN attribute.

<cfquery cachename="myDetails" 
            cachedomain="#session.user.id#" 
            name="qry" datasource="ds">
 select * 
 from userTable 
 where userid=<cfqueryparam value="#session.user.id#">
</cfquery>

<cfquery cachename="myTasks" 
            cachedomain="#session.user.id#" 
            name="qry" datasource="ds">
 select * 
 from userTasks 
 where userid=<cfqueryparam value="#session.user.id#">
</cfquery>

.. to later flush all the queries for this user group ..
<cfquery action="flushall" cachedomain="#session.user.id#"></cfquery>

The main advantage of this technique is that it allows you to quickly flush or delete caches based on either a single key or a group. For example, you may have a number of queries that are based on a specific user, and if they change their preferences, then you would want to simply nuke all the cached queries that was pertaining to that given user. You don't need to keep all the queries around just to delete them and nor do you have to completely flush the whole query cache just because of one event.

You control this using the ACTION="flush|flushall|flushcache" attribute of CFQUERY.

By grouping your queries together into domain's you can quickly build up a sophisticated caching structure without having to worry about the internal management of it. The CFQUERY tag will give you all the control you need.

You can use this in addition with the time based cached, or just leave it, forever, in the cahce until such times you perform a manual flush.

By removing the load from the database, the CFQUERY can make life much easier for your poor database server and give your users a much faster experience.

Later we'll cover another caching directive that OpenBD adds to the CFML language aimed at managing whole blocks of output.