Rob I have asked a similar question on this forum - I think the best bet is to cache the query results and then use the "query a query" functionality (assuming you are using CF5 or above). How best to cache the query will depend on whether the results are specific to users or not. If not then you can cache in the Application scope. If it is specific to users then I think you can cache in the session scope although my questio (which I am still awaiting responses to) asks how best to do this.......
Andy -----Original Message----- From: Edwards Robert (air0rae) [mailto:[EMAIL PROTECTED]] Sent: 28 August 2002 14:43 To: CF-Talk Subject: Slightly OT: CFQuery, Oracle and Limiting rows I have a query that returns about 5000+ rows of data. Currently it takes about 51 seconds to execute (it's a very complex query). I'm only displaying 100 records per page using start and end rows in a cfloop. The problem is that the query gets executed every time I go to a page and it take 50 seconds or so each time. I know I can use MAXROWS in my cfquery tag to limit the return to 100 rows, but that will only get me the first 100. Does anyone know how to set it up so I can get the second 100, or third or fortieth? I would guess this needs to be done in oracle instead of CF, but there may be a custom tag I don't know about. (About the data: There is no iterator or counter on the rows so I cannot do a select where rowid between 1 and 100 or 101 and 200 and so on. Each row has a unique value in it, and can be added to or deleted from at any time. Also, the data needs to be real-time so caching a query is out of the question as well.) Any suggestions would be greatly appreciated. My phone is ringing off the hook with complaints of the slow response. Thanks, Rob Edwards Phone: (502) 359-1627 Systems Management Tools Pager: (502) 478-1116 United Parcel Service Fax: (502) 359-0094 EMail: [EMAIL PROTECTED] (2B || !2B) == ? ______________________________________________________________________ Get the mailserver that powers this list at http://www.coolfusion.com 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
