How do I cache the result set of a cf_storedproc??

Forum|Forum|20 years ago
May 25, 2006
3 replies
695 views

I see that there's a parameter for CF_QUERY for cacheing but not for CF_STOREDPROC.
Why not? How would one cache this result set?

In the old days, I would use <cfquery> with
execute proc_mystored_proc

but that doesn't seem to work anymore.
thanks!

This topic has been closed for replies.

C

cf_bewilderedAuthor

Participant

They change daily. What I did was what -edgriffiths- suggested above. It works and is much quicker than that javascript method we were using before.

Thanks!

D

Dan_Bracuk

Inspiring

You can make the recordset an application variable. Then add a bit of logic so that if 23 hours has elapsed since you last refreshed the data, refresh it.

BKBK

Community Expert

I might be echoing Dan, I guess

<cfstoredproc procedure = "foo_proc"...etc>
<cfprocresult name = "Application.RS1">
<cfprocresult name = "Application.RS2">
</cfstoredproc>

A

Anonymous

Here's one way to do it. Alternatively you might be able to use CFQUERY to query-of-query the entire contents of your stored proc resultset - haven't tried it, and it'd be very inefficient to do so.

Use CFQUERY

C

cf_bewilderedAuthor

Participant

Hmm. Thanks for the idea but unfortunately, efficiency is what I'm after.

See, what I'm doing is returning a recordset of 2000+ values for a drop down - and it's taking too long. A major limitation is that I can *only* use stored procedures (our DB team is quite terratorial) hence, I can't create my own queries. I've tried simply running the stored procedure then immediately running a CACHED query of queries against that sp recordset but the processing time is increased. Silly, I know, but worth a try.

<cftry>
<cfstoredproc procedure = "get_all_vendors">
<cfprocresult name="getAllVendors"></cfstoredproc>
</cftry>

<cfquery dBtype="query" name="sub_vendors" cachedwithin="#CreateTimeSpan(0, 23, 0, 0)#">
select * from getAllVendors;
</cfquery>

Seems to me it would make most sense to just allow cacheing of stored procs.
I can't believe I'm the first one to request this.
Bueller? Anyone?

D

drforbin1970

Inspiring

Are these dropdown values static or do they change frequently? If static, perhaps you could create a server variable and load the values once and they are accessible to all apps.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded