Caching the query with CF's built-in caching may be more reasonable than you think; with 4.5.1 SP2 the limit of queries is not 100 and apparently is limited only by available memory. I think it's the easiest method to implement though, of course, you don't have as much control.
> -----Original Message----- > From: Jennifer Larkin [mailto:[EMAIL PROTECTED]] > Sent: Sunday, December 23, 2001 4:57 AM > To: CF-Talk > Subject: Re: How do I handle Large Record Sets from the DB? (o8i) > > > 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 > ______________________________________________________________________ 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
