Ask a Jedi: Caching Stored Procedures

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 developer advocate. He focuses on JavaScript, serverless 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 https://www.raymondcamden.com

Comments