Dave O wrote: > They call one of our stored procedures which dynamically builds a SQL > statement and returns to them via a RefCursor. The problem we are having is > that the query could possibly return hundreds of thousands, or maybe a > million or more rows.
> From a pl/sql point of view, after opening a cursor you must FETCH the rows > from the Cursor, using program logic to determine when to continue and when > to stop fetching(threshhold hit or end of cursor), then close the cursor. I don't know if Oracle or the Oracle JDBC drivers do something extra, but standard cursors can work the same with CF. See for instance: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:49577#265064 > They claim that once they open the cursor it automatically fetches all rows > until the end of the cu > rsor, then closes it. That is the default behaviour of the CF implementation of a stored procedure call. If you want something else, you need to write it yourself as a bunch of queries in a transaction instead of using the cfstoredproc tag. > Since they had issues with too much data crashing the Coldfusion server (out > of memory) they have code in the pls/sql procedure that builds the dynamic > sql statment for the ref cursor, that only fetches 100 rows at a time for > display, using a (rownum BETWEEN 1 and 100) where clause delimiter. When the > user clicks page 2, they call the stored procedure again and use (rownum > BETWEEN 101 and 200), click page 4 and another call using (rownum BETWEEN 301 > and 400). Unfortunately, oracle must process everything all over again > (group by, sort, etc.) just to return the next 100 rows. For the larger > queries that take up to 90 seconds to compute the results, this hit happens > for every page (100 rows a page) that is selected. I am in utter shock that > they are processing data like this. Could someone please let me know if its > possible to programatically fetch records from the ref cursor, fetching rows > as needed, say 100 at a time, then closing the cursor after > the user is finished browsing the data and exits. You are missing an important point of web applications: once the first page has loaded in the browser, the user is finished. There is no way of knowing whether there will be another request for the second page or not. Web applications do not have a persistent connection between the browser and the server like desktop applications, so every request stands on its own. Jochem PS Please use an email client that wraps your lines at some sensible number of characters, this is really hard to read. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Macromedia ColdFusion MX7 Upgrade to MX7 & experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:279189 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
