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. � we have tried the following two options unsuccessfully: (1) Add the CACHEWITHIN option to the <CFSTOREDPROC> tag (apparently not supported) (2) Call the stored procedure from within the <CFQUERY> tag - (REF CURSORS not supported) � What are the options in achieving this functionality?� Our platform is CF 4.5.1 (SP2) on Solaris against ORACLE 8.1.7 (Solaris) utilizing native drivers, but we're in a position to move to CF5 since we'd like to test that with Spectra 1.5.1 Release Candidate anyway. Thanks in advance for any information. -Darren ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
