tracker issue : CF-3036833

Problem:

I've not seen this mentioned in the 2 alpha release notes: are there any plans to improve query cache management in CF9? I'll drop this onto the feature request forum as well, to hear if others might have thoughts about this proposal.

As things stand now, it's really difficult to flush the query cache for a given query. Yes, I know (but I bet most don't) that you can use <cfobjectcache action="clear">, but that clears all cached queries (which is horrible on a shared host or a server with multiple unrelated applications).

And no, using a 0 time for the cachedwithin is NOT the answer. That only works for whatever SQL statement is created within that CFQUERY while that time value is set to 0. If you have variables in the query (such as getting all employees in a department, where department is passed in as a URL, form, or session variable), then each previously executed query will still be cached and will NOT be affected by this "reset to 0", unless you think to somehow re-run all the previously executed cached queries.

My proposal would be to simply add a cachename attribute for CFQUERY. Then all cached queries generated for that name could be managed as one, and a new tag could be created to permit flushing the query cache for that/those queries, such as CFQUERYCACHE action="clear" cachename="xxx".

And speaking of that horribly named cfobjectcache tag, almost no one I ever ask realizes this is what it does, clearing the query cache. I'd propose rolling its functionality into this new tag, to work with action="clear" when no cachename is provided.

It would also be nice, then, to provide an interface in the Server Monitor (on the query cache page) to permit us to clear individual cached queries. This is not only for completeness, but to offer a solution that the above cannot easily provide.

One might wish we could let this cfquerycache tag provide for clearing the cache for some given sql statement, but that would be very difficult to do (in a tag) because we'd have to recreate the exact SQL statement, with carriage returns, tabs, spaces, and case all exactly the same). But in the Server Monitor, we can see each exact cached query, and so it could create such an exact replication of the SQL statement to be cleared.

Someone might propose that it would be nice if this cfquerycache tag also permitted access to the info now only in the Server Monitor (or available via the Admin API), such as to list all cached queries for a given cachename (or perhaps all cached queries), and how many times they've been hit, etc.  But there's a security risk there: since it shows the exact SQL statement, and that may have info about queries in one app that folks in another app shouldn't see. So it does make sense for that info to be available only in the Admin API. The shame is that is Enterprise-only, so tracking the effectiveness of query caching is impossible in Standard.

Still, this is all the more reason to permit us to more easily clear the query cache in a tag that's available on all editions. (Granted, one may argue that being able to clear the query cache should be admin-limited, but the cfobjectcache tag right now is not so limited. But this could be added, like it, to the list of things that can be sandboxed in Enterprise, or controlled by Resource Security in Standard.)
Method:


Result:

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	3036833

External Customer Info:
External Company:  
External Customer Name: charlie arehart
External Customer Email: 03D0090C44723473992015D5
External Test Config: 11/26/2008

Attachments:

Comments: