Can I use CACHEWITHIN attribute in my stored procedure??

[bnymk]

Hi all:

I have a stored procedure that queries tables and returns a resultset to the website. I know one can use CACHEWITHIN attribute in a query like this <cfquery name=&quot;queryName&quot; datasource=&quot;databaseName&quot; CACHEWITHIN=&quot;#CreateTimeSpan(0,0,15,0)#&quot;> but I don't know how to use it with <cfstoredproc>

Any ideas????

Thanks.

 

[pcorreia]

The documentation says that &quot;cachedwithin&quot; is only valid for the <CFQUERY> tag. However, you could roll your own caching mechanism by executing the procedure once, then copying the result set into a persistent memory store like the session, application, or server scopes. If you also stored the time when you put the result set in memory, you could compare it to the current time in your page to determine whether the cache needs to be refreshed.

Yes, it's a lot more effort than simply setting an attribute on the <CFQUERY> tag, but if you've identified the database traffic as a bottleneck, it may be worth it. Keep in mind, though, that most database servers already do some caching of stored procedures so you may already be getting a speed benefit.