Retrieving large result sets from Oracle DB

[Golden_Jet]

I have a webbased app which in a dev environment retrieves 41K records on the initial db query for display to the user. I cache the query as the user wants to be able to page through the result set, displaying 20 records at a time in the GUI.

The initial db query to retrieve 41K records takes around 25 seconds to move from the db box to the CF server box. We are expecting 10 times the amount of records in production!

The current method used is a CFC function containing the SQL query.

Does anyone have any ways to speed this up, or am I at the mercy of the network?

Any help will be greatly appreciated!

[JR__Bob__Dobbs-qSBHQ2 · Accepted solution]

I'd look at doing some db server side paging via a stored procedure rather than paging using CFML. This way you're only passing 20 rows at a time to the CF server.

[Golden_Jet]

Bob,

And I didn't think of this myself because...?

Talk about having blinders on. Thanks for taking them off!

[Dan_Bracuk]

I'd be wondering why it's necessary to return so many records from the initial query.