Friday, April 17, 2009

Query Caching Enhancements in Open BlueDragon

I was IMing with Corey Gilmore today about his recommendations to the CFML Advisory Committee, and as I was reading through his wish list I was surprised at the number of times I could say "that's already in Open BlueDragon."

Things like being able to var scope anywhere are nice features, but one of the big ones a lot of people may not know about is something that's been around since BlueDragon 6.2.1, and that's the ability to use named caches in queries.

In the CFQUERY tag in Open BlueDragon there are a few additional attributes as compared with Adobe ColdFusion, namely BACKGROUND, which allows you to easily run a query in a background thread, and CACHENAME, which allows you to cache a query and give the cached query a name. This allows you to flush specific cached queries much more easily.

For example, the following code would cache the query under the name "myCachedQuery" until you flush it:

<cfquery name="myQuery" datasource="myDSN" cachename="myCachedQuery>

An ACTION attribute has also been added to the CFQUERY tag so you can easily flush the cached query. To flush the "myCachedQuery" query you would run the following:

<cfquery action="flushcache" cachename="myCachedQuery">

And if you want to flush all cached queries, you can use the FLUSHALL action:

<cfquery action="flushall">

The CACHENAME attribute can also be used in combination with CACHEDWITHIN and CACHEDAFTER to create time-based cached queries that also have the advantage of being cached with a specific name.

For more information about the enhancements to CFQUERY that are available in Open BlueDragon, check out the OpenBD wiki. And thanks to Corey for prompting me to discover that while I had documented this information in the Compatibility section of the wiki, it wasn't on the page for the CFQUERY tag.


Another fanastic feature I've used with and since BD 6.2 is cacheduntilchanged or something likewise. Is that still in there in OpenBD?


Yes it is still there. For those of you do not know:

The query cache in fact stores result sets that are cached when using the CFQUERY attributes CACHEDWITHIN and CACHEDAFTER, and CACHEDUNTILCHANGE. There is a "cachecount" element that specifies the maximimum number of queries that will be stored in the cache. When the query cache becomes full, queries are removed from the cache using a least-recently used (LRU) algorithm to make room for newer queries.

Unless I'm mistaken, CACHEDUNTILCHANGE is only supported on BD .NET with SQL Server. My understanding is that is relies on specific .NET/SQL Server functionality. In the OpenBD code for CFQUERY if you use CACHEDUNTILCHANGE it throws an invalidAttributeException.

That is a GREAT feature of BD .NET though!

Matt, I think you might be incorrect, cause I'm running BD 6.2 with MySQL 4 on a W2K machine! I know, very old stuff, but why change a winning team?

@Sebastiaan-CACHEDUNTILCHANGE wasn't even introduced until BD 7, and it is only in the BD .NET version. You sure we're talking about the same thing?

The docs confirm this: "BlueDragon 7 introduces the CACHEDUNTILCHANGE attribute as a superior alternative to the CACHEDWITHIN attribute when using BlueDragon .NET in conjunction with Microsoft SQL Server 2005. ... BlueDragon for the Microsoft .NET Framework (BlueDragon .NET), when used in conjunction with SQL Server 2005 offers a better alternative to CACHEDWITHIN for query caching. The new CACHEDUNTILCHANGE attribute allows you to do 'perfect' query caching. That is, BlueDragon caches the query results until notified by the database server that the data has changed."

I suspect we must be talking about two different things here.

@Matt, seems you're right ;-) I use CACHEDWITHIN in BD 6.2 - I was planning to make use of CACHEDUNTILCHANGE, but I guess it'll only work in a version that can leverage Windows DLL's - a .NET version ;-)

No comments: