The ColdFusion Query Cache Hammer

Posted At: September 17, 2008 11:41 AM

Posted By: Stephen Withington

Related Categories: ColdFusion

CFQuery Cache Hammer

I keep my copies of CFWACK readily available at my desk for those moments when my computer is busy (installing software, ftp large files) ... you know what I mean, right? So recently, during one of those moments, I flipped open Volume 2 to page 247. The heading that caught my eye was "Flushing All Cached Queries."

Now, I have to admit, I've not been very good with my "cache" to begin with. However, there have been times when I've used CachedWithin for a CFQuery here and there. I've also been frustrated at how refreshing the cache can be sometimes after inserting or updating a record.

Just in case you don't know, let's assume you have a query such as:

<cfset REQUEST.dsn="cfartgallery" />
<cfquery name="getArtists" datasource="#REQUEST.dsn#" cachedwithin="#createTimeSpan(0,6,0,0)#">
SELECT firstname, lastname FROM Artists
</cfquery>

As you can see, the query will be cached for a period of six (6) hours. However, if you update the Artists table during those six hours, those updates will not be seen until a) the ColdFusion application server has been restarted, b) you force the existing query cache to refresh by placing the same query just after any insert or update has been performed such as:

<cfquery name="updateArtists" datasource="REQUEST.dsn#">
INSERT INTO Artists (firstname, lastname)
VALUES ('Steve', 'Withington')
</cfquery>

<cfquery name="getArtists" datasource="#REQUEST.dsn#" cachedwithin="#createTimeSpan(0,0,0,-1)#">
SELECT firstname, lastname FROM Artists
</cfquery>

The issue(s) I've run into with this is that both SQL statements must be exactly the same, including tabs, spaces, etc. In addition, the CFQuery's attributes, (name, datasource, etc.), must also be identical. Yeah, good luck buddy. Have fun testing your "Cache Machine." I'm not implying you shouldn't employ this method, I just know how difficult (time-consuming and frustrating) it can be trying to get it to work (and test for!). The examples I've used above are not real world examples. As a developer, you know what your queries can look like and how extensive they can be, especially with a few INNER JOIN's and such. I really should mention that when this method is done right, performance gains can be worth the added effort.

Or there's c) CFObjectCache, or what I like to call the CFQuery Cache Hammer. I call it a hammer, because it pounds each and every cached query on the server (or server instance, I assume, in a multi-instance installation) into submission, then discards them like the trash they are (kidding, of course). There is only one attribute, "Action," which is also required. So, you can pound on all of your cached queries with one mighty hammer by simply placing this itsy, bitsy line just after your insert or update query:

I suppose this is the place where I should invoke that famous line from Spiderman, "With great power ..." but I won't.

Comments (7)

BFusion / BFlex 2008 Was a Hit!

Posted At: September 9, 2008 1:07 PM

Posted By: Stephen Withington

Related Categories: ColdFusion

I was listening to CFConversations awhile back (episode 11) and heard a guest on the show, Bob Flynn, mention something about two days of hands-on training on Adobe Flex and ColdFusion being held in Bloomington, Indiana. Then he dropped the bomb, it was FREE! I leaned forward in my chair and listened intently on where I could find out more information about this event. I was thinking, "hmm, Bloomington isn't too far away, at least I don't think it is, maybe I could road trip there. Did he really say the event was free?" I launched a browser and went to maps.google.com and plugged in Bloomington, IN. Then I threw Rockford, IL in the "To here" link and saw the estimated drive time of five and a half hours (approx.). "That's definitely doable," I thought. Bob just kept chatting away as I was getting anxious about trying to learn how to register.

I remember thinking to myself, "Man, this is gonna fill up quick, I better register as soon as I can." When Bob finally mentioned the web site to find out more information, bflex.info, I was relieved. "Alright, let's register and get this thing lined up," I mumbled. When I went to the site, I was furiously clicking every link, and couldn't find the registration page. "Aw, man, this sucks. I missed it." Actually, that wasn't the case. As many of us do when visiting a site, we fail to actually READ anything. Instead of reading, I was hell-bent on finding the "form" to register, that I had completely missed the fact that the registration wasn't even open yet. Of course, once I figured this out, I was relieved.

Anyway, once the registration did finally open, I registered right away. Then I learned about another "twist" in the registration. Just because I registered, I wasn't "fully" registered yet. I had to wait for a confirmation email, follow the instructions, and then I would be "confirmed." As the event drew near, I was getting anxious again, "What if I don't get the confirmation email? What if they only accept so many 'confirmations' and I miss out?" I couldn't take much more of this. I was way too excited about this event, and I didn't even know why. I just knew it was going to be an event "not to be missed." Then, as the event dawned, I started receiving emails from some of the event organizers.

August 30, 2008 (8:25 AM): "Thank you for registering for BFlex ... First of all, this email is not a confirmation of your registration, confirmation will be sent out soon, you must respond to hold your seat. ..."

Alright, make me have a heart attack. I thought I couldn't take much more of this. When was this going to be over already?

August 30, 2008 (12:53 PM): "Hello All, Thank you for registering for BFusion ..."

Ok, ok, one email for BFlex, and another one for BFusion. I understand. Sounds good, so where's the confirmation email?

August 30, 2008 (4:37 PM): "Action Required: Confirm Your Registration"

Here it is! Alright, let's do it. Just as I'm getting ready to click on the link, another email comes in:

August 30, 2008 (4:46 PM): "Stephen, Please disregard the previous email, it was sent in error and had an incorrect link. The following link should be valid. Thank you for registering ..."

So as you can see, there was quite a bit of anticipation on my part. Once I was finally and officially confirmed, I was ready to go.

The drive down wasn't too bad, but took a little bit longer than Google maps had predicted. I knew driving through Chicago on a Friday night during rush hour would slow me down anyway. After I arrived and got settled in, I went down to the lobby in hopes of meeting other attendees. I wasn't disappointed. I met several really cool people and shared a few adult beverages with some of them. It was nice to finally meet so many of the people I had been "Twittering" with as well as following around online via their blogs and such. (If you're a ColdFusion developer and you work by yourself, you can probably relate to how cool it was to be amongst so many other CF programmers in one place.) Just hanging out with other people who "understand" you makes all the difference in the world.

Day one was BFusion. I attended the Intermediate Track because I wanted to learn more about Mach-II. Matt Woodward, Peter J. Farrell and Kurt Wiersma spent the entire day walking us through both Object Oriented Programming and the Mach-II framework. I was one of the first people to win a Mach-II t-shirt, which I'm actually wearing right now as I write this. (Thanks guys!) They did a wonderful job of explaining the ins and outs of Mach-II and why one should consider using Mach-II in a ColdFusion environment. I picked up some very useful information and a was treated to a great introduction to the framework. I plan to spend more time learning about Mach-II in the future and hope to share my experiences with it on my blog.

At the end of the day, several of us walked down to Upland Brewing Company for dinner and a few beers. The food was great, the service was fantastic and the beer was even better!

Day two was BFlex. I attended the Beginner Track because, well, I'm a newbie to Flex. Zach Stepek led the day-long hands-on class and provided us with the means and knowledge to start our journey into Flex off on the right foot. Everyone enjoyed a few slices of pizza for lunch (also free!) and an awesome keynote presentation from Mike Labriola of Digital Primates.

Both days were filled with courses that span the gamut of ColdFusion and Flex all instructed by many of the leaders in these fields. I connected with several fantastic people whom I hope to meet again, and maybe even work with on a project one day! The BFusion/BFlex 2008 weekend will be remembered as one great trip!

Thank you to each and every user group, speaker, volunteer, coordinator and sponsor for hosting a first-rate program! Also thank you to the Kelly School of Business and Indiana University for providing a fabulous environment for learning. I truly appreciate the time and money each of you put into the event and look forward to coming again next year.

Comments (2)