All, This is what our environment looks like: Microsoft IIS5 on Windows2000 ColdFusion (4.5.1 SP2) Enterprise Edition Oracle8i (8.1.7) Utilizing the Oracle Native Driver.
The Task at hand: Users will be quering the database and in some cases receiving back large recordsets. These large recordsets could be from 0 to 3,000 maybe 4,000. If the recordset exceeds 20 records, it will be broken up into pages of 20 (20 results per page) Does anyone have any thoughts about the best way to go about this? Here are some possible solutions that I have thought of: Solution 1: I could use <cfquery> to query the database and cache the query in server memory. Possible problems with this solution: The CF Server can only cache 100 resultsets at one time. After that, the oldest ones get bumped out of the CF Server's memory. If the result set gets bumped out of memory, then the database has to be queried again and the data has to go across the network wire again. Solution 2: Use <cfstoredproc> to call an Oracle Procedure. The Procedure would return 20 records at a time. Have the procedure store the queried result set into a temp table for a period of time. Send 20 records to the CF Server. Provide the CF Server a unique ID to identify the result set when the CF Server request the next 20 records. The Primary key of the result set table would be the combination of a ResultSetID and RowNumber (rownum column from the original result set in Oracle8i). Possible problems to this solution: During the initial query, I have to wait on the database to write the results to the temp table before sending the first 20 results to the CF Server. The first 20 record talk longer to get. The temp table grows unless we periodically remove the old record sets. I do not believe that I can use Oracle Temporary Tables because the CF Server acts like a single user with a single session on the Oracle Database. The result sets are persistant for all Web Users. When the session closes the data in the temporary tables is removed. The temporary tables are truncated when the session is closed. What should I do? MMmmmmm. Thanks for taking the time to read this and for responding. Sincerely, Troy (tds) -- Troy Simpson | North Carolina State University NCSU Libraries | Campus Box 7111 | Raleigh | North Carolina ph.919.515.3855 | fax.919.513.3330 It is better to be hated for what you are than to be loved for what you are not. -- Andre Gide ______________________________________________________________________ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation � $99/Month � Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
