You could use an Oracle stored procedure to write out the results to a file/files on the web server. Then you are just serving files from a web server--no big deal. You might need to be creative in how you re-assemble the files, or you might need to map a drive so the web server and your db server can share a directory.
If the result sets are persistent for all web users, I think a file-caching strategy is a good way to go. Ultimately it'll depend on just how often your data changes, how much data you're grabbing, and how up to date it needs to be, etc... If you have very expensive queries you can save your DB server from a severe thrashing, and keep wait times for your users to a minimum. If you do the cached files trick, you might want to cook up a mechanism to flag old data as dirty whenever there are updates, and schedule a cron job/Scheduled Task to re-publish all of the "semi-dynamic" data from your queries. Jeff Polaski Manager, Web Services Research & Graduate Studies University California, Irvine -----Original Message----- From: Troy Simpson [mailto:[EMAIL PROTECTED]] Sent: Friday, December 21, 2001 4:35 PM To: CF-Talk Subject: How do I handle Large Record Sets from the DB? (o8i) 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 ______________________________________________________________________ Why Share? Dedicated Win 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=coldfusionc 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
