Ask a Jedi: Caching Stored Procedures

This post is more than 2 years old.

Seth asks:

Just a quick question regarding Stored Procedures. So recently I have been dealing with some SP's that take up to 4 seconds to run , it can be annoying. So I wanted to know is there an effective way to cache SP's in CF 8, or do I have to convert them to CFC's and use a standard inline query.

So first off, the cfstoredproc tag does not have a cachedwithin or cachedafter attribute. You can convert your stored procs call like so:

<cfquery name="getit" datasource="foo" cachedwithin="#createTimeSpan(0,0,0,60)#"> exec doit 'a','b' </cfquery>

And the result is cached, but frankly, I'd suggest using your own caching system instead. It is much easier to manage. By that I mean simply use cfstoredproc and cache the result in either the Application or Session scope, whatever makes sense. You can store the time generated as well and get new data every hour (again, whatever makes sense).

You mentioned above 'convert them to CFCs' - I do hope you are using your stored procs in CFCs already. In general, all my database code exists in CFCs, whether I use stored procs or 'standard' queries.

Edit 10:32 AM
Thanks to reader Joel, I now know that cfstoredproc does indeed support cachedwithin. Apparently it's mentioned in some of the other docs, but not the core reference. (Maybe it ended up in the same place as the cffeed chapter of the dev guide. ;)

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA

Archived Comments

Comment 1 by joel posted on 4/25/2008 at 6:58 PM

In CF8, you can cache cfstoredproc results. From the Adobe docs:

CachedWithin and CachedAfter have been added to the cfstoredproc tag and can be used to cache the stored procedure results. Refer to the documentation on cfstoredproc for additional information.

Comment 2 by Raymond Camden posted on 4/25/2008 at 7:04 PM

Is this 801? In looking at my reference I do not see it (my ref is still 8 though).

If your right - and I'm sure you are - sorry folks!

Comment 3 by Raymond Camden posted on 4/25/2008 at 7:06 PM

I just checked the 801 release notes and I don't see this.

Comment 4 by joel posted on 4/25/2008 at 7:17 PM
Comment 5 by joel posted on 4/25/2008 at 7:24 PM

Documentation on this does indeed seem to be hard to come by. Trying searching for 'storedproc' in this document:

Comment 6 by Raymond Camden posted on 4/25/2008 at 7:31 PM

Wow, it totally missed the 'real' docs. You are right. I'm going to edit the entry (in a few minutes, I want to email the CF doc guy) in case folks miss the comments.

Comment 7 by Jason Dean posted on 4/25/2008 at 10:45 PM

Yes, I was very excited to see that cfstoredproc now supports stored procedures. We had some queries that perform thousands of stored procedures on some requests and it can take as much as 90 seconds for the page to display. But once the SP is stored the page refresh happens in less than 1 second.

Comment 8 by joel posted on 4/25/2008 at 10:46 PM

There is an issue with caching, though, if the stored procedure returns multiple result sets. Suppose

<cfstoredproc procedure="sp_test.get_some_data" datasource="mydsn" blockfactor="2" cachedwithin="#CreateTimeSpan(0,1,0,0)#">
<cfprocresult name="rs" />
<cfprocresult name="rs2" resultset="2" />
<cfdump var="#rs#">
<cfdump var="#rs2#">

An exception will be thrown that variable rs2 is undefined, although it might work perfectly well without the cachedwithin attribute.

Comment 9 by Jason Dean posted on 4/25/2008 at 10:46 PM

Doh! I meant that I was excited that cfstoredproc now supports caching. I think it has probably always supported stored procedures ;)

Comment 10 by Joshua Curtiss posted on 4/25/2008 at 10:54 PM

Good to know. I had been doing the cfquery route.

Comment 11 by Matt Turner posted on 4/27/2008 at 8:40 PM

What's really interesting is if you dig into the java where the queries are actually cached it uses the the syntax ray provided as the signature for the cache itself. It really looks as though adobe just sort of missed enabling the feature in previous versions. For those of us without CF8 there should be a way to use the cachedwithin/cachedafter functionality native to cfquery with a cfstoredprocedure by accessing the java class: lruClass and injecting your cache with the parameters you want to use manually. I say in theory because I havn't tested it on anything but CF8, perhaps things have changed. If anyones interested let me know and I will put togher an example.

Comment 12 by Raymond Camden posted on 4/27/2008 at 8:51 PM

@Matt- interesting. Although I still say - if you want to cache queries, you should probably just handle the caching yourself.

Comment 13 by Matt posted on 4/27/2008 at 11:24 PM

I totally agree way more flexibility in handling the cache yourself. I am working on a project with a large scale implementation of Data Management using LiveCycle DS and have been forced to go to extreme lengths to handle caching on my own due to some very key features that Adobe left out of the assembler for coldfusion. Bummer that the java implementation of LCDS work so much better then any coldfusion one can.

Comment 14 by Ben Davies posted on 5/2/2008 at 11:07 AM

I'm not sure I agree this - at least as general guideline. There are limitations with the query caching performed by ColdFusion on your behalf but at least it has a certain amount of consistency and reliability.

If you are to roll your own caching you are taking on a lot of responsibility on yourself to ensure you

a) have transparency into what is being cached (server-wide)

b) don't over-cache: Set a limit on the total number of cached queries. Ideally I think that limit would be based on the size of the queries, and not on the number of queries as used by CF caching.

c) intelligently age-out queries from the cache as you reach that limit or the maximum timespan. Not only do you want to select the best candidate to age out, you also want to pro-actively age items out between requests

d) have a *big* concern for the server memory. Test with your biggest possible datasets and multiple concurrent requests.

Caching is one of those areas that can look a lot different in production than it does in development, depending on the quality of your testing. It is especially easy to get a false sense of security from load-testing unless you insert a degree of randomness into your tests.

I wonder if anyone knows of any third-party components that take care of this?

The whole topic is probably worthy of a dedicated long-ish blog post.

I hope the above makes sense.


Comment 15 by Raymond Camden posted on 5/2/2008 at 3:39 PM

@Ben. Some very good points there. I -do- write my own caching all the time but I do not take that much care in making sure it plays well with others. Even on a box where your coe is the only code, it does make sense to at least plan out what your caching strategy will be across the server.

One nice thing is that the Server MOnitor in CF8 (and tools like SeeFusion and FusionReactor) can help you monitor the impact of your caching.

Comment 16 by Ben Davies posted on 5/8/2008 at 12:29 PM

Hi Ray,

Thanks for the reply. I guess my comments were from bitter experience dealing with huge datasets and heavily taxed servers :) ... and dealing with a mix of legacy service components with no caching, cf caching, roll-your-own query caching and full dataset/QoQ caching. Understanding what is going on in those situations is pretty hard.

If you haven't noticed my comments are always about 6 days behind the reply or blog post - I don't know how everyone else keeps up but I struggle.

Comment 17 by JC posted on 4/7/2010 at 12:20 AM

ooh, nice. Can you believe they *still* haven't updated the CF8 docs?

I have a stored proc that runs inside application.cfm that simply checks to see if the site is in online or offline mode (so we can take it down for maintenance) and redirects users to an offline message if we've taken it down for some reason. Setting it to cachedwithin 30 seconds should give effectively the same functionality but be a huge reduction in the number of DB calls and a slight improvement in performance.

Thanks for posting this. :)

Comment 18 by Raymond Camden posted on 4/7/2010 at 12:24 AM

@JC: The docs now allow for comments. Is it still wrong in CF9? If so, add a comment.