The CFQUERY tag supports the optional "cachedWithin" attribute that allows you to cache query results for data that changes infrequently, in order to improve the performance of your application. Wouldn't it be useful if there was a "cachedUntilChange" attribute that would cache the query results and only re-execute the query if and when the database notifies you that the data has changed? For example:
<cfquery name="employees" datasource="myCompany" cachedUntilChange="true"> SELECT * FROM Employees </cfquery>
In this example, the "employees" query result variable would be cached in memory and subsequent renderings of the CFQUERY tag would return the cached result. The query would be re-executed only if and when the data in the Employees table changes. This would solve the basic problems with using the "cachedWithin" attribute: no more guessing at the right interval to refresh the data, and no more "stale" data in the query cache during the cache interval. Queries would be executed exactly when they were needed. If you had such a capability, it might be tempting to use it by default for all of your CFQUERY tags.
One of the things I learned last week at the Microsoft PDC05 developer conference is that the combination of SQL Server 2005 and ADO.NET 2.0 now provide exactly this sort of query caching and notification capability. In fact, this new feature works not only for SELECT statements, but also for stored procedures.
Therefore, it is possible to implement the "cachedUntilChange" attribute for CFQUERY and CFSTOREDPROC in a future version of BlueDragon.NET when used in conjuction with SQL Server 2005, and is a feature we're very likely to add. This is just a small example of the many benefits of the tight integration between BlueDragon.NET and the Microsoft .NET Framework.
UPDATE: The "cachedUntilChange" attribute for CFQUERY is implemented in BlueDragon.NET 7.0.