Skip to main content
Golden_Jet
Golden_Jet
Inspiring
December 11, 2008
Answered

Retrieving large result sets from Oracle DB

  • December 11, 2008
  • 2 replies
  • 797 views
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!



This topic has been closed for replies.
Correct answer JR__Bob__Dobbs-qSBHQ2
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.

2 replies

D
Dan_Bracuk
Inspiring
December 12, 2008
I'd be wondering why it's necessary to return so many records from the initial query.
J
JR__Bob__Dobbs-qSBHQ2Correct answer
Inspiring
December 11, 2008
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
Golden_JetAuthor
Inspiring
December 12, 2008
Bob,

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

Talk about having blinders on. Thanks for taking them off!
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices