At 07:34 PM 12/21/01 -0500, you wrote: >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.
Solution 3: Store the query in the session scope. Whether or not you can do this will depend on how many users will be on the system and how much memory the system has, but it does solve some of the problems you've mentioned. It requires only one trip to the database, it will be easy to manage the record display, and it times out according to usage instead of on a time-limit or when there are too many recordsets. If memory is going to be an issue, you might want to store the search query but not the data for the details page. Now available in a San Francisco Bay Area near you! http://www.blivit.org/mr_urc/index.cfm http://www.blivit.org/mr_urc/resume.cfm ______________________________________________________________________ Get Your Own 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=coldfusionb 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
