Two ways I can think of in 4.5x: 1) Keep the results on a per-user basis in session variables 2) Write a custom tag wrapper for CFSTOREDPROC that mimics CFQUERY's caching abilities. -----Original Message----- From: Darren Nickerson [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 15, 2001 1:10 PM To: CF-Talk Subject: CF 4.5/5.0 & ORACLE stored procedures - how to cache results?? Folks, A key aspect of our site is a search function that allows users to specify criteria in several categories for a DSS-type query against the database. Since use of this feature is expected to be heavy, response time is critical. To optimize performance, we've designed this section to dynamically generate the SQL based on the user's selection. By dynamically generating the SQL, we can eliminate unneeded joins and tailor the criteria for faster throughput. To further improvement performance, we've designed the module that generates the SQL as an Oracle stored procedure that is called from the search page within a <CFSTOREDPROC> tag. To return multiple rows back to ColdFusion, we use an Oracle REF CURSOR from the stored procedure. While the stored procedure call from the <CFSTOREDPROC> tag works, we need the ability to cache large result sets (to allow paging through results) in the same way that the <CFQUERY> tag provides caching through the CACHEWITHIN option. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
