I have a select query which gives me a huge output (number of records). I have to iterate through each record to do a certain action. Is it efficient to do it by manipulating the result set or should I define a cursor? I am using JDBC API and JAVA programs against an UDB database. Is there a rule of thumb for when to use cursors or when to manipulate result sets?
You may have overlooked a third option: do the manipulation directly in SQL.
Predicting which of several methods will be most efficient is always a crapshoot. So much depends on how the database system has been configured, whether proper indexes have been defined, and so on. To be absolutely certain, you would need to test each method, obtain CPU and elapsed time totals for several runs of large magnitude, and compare timings.
That said, here's my rule of thumb:
- application code
Please use discretion, because it's not an absolute rule. For example, if by "manipulate the result set" you intend simply to sort it, then obviously option 1 is best (use an ORDER BY clause). On the other hand, if complex statistical calculations are required, sometimes option 3 is best (extract the data and dump it into a statistical analysis application). Unless you are forced, by the nature of the desired manipulation, to use application code, then the choice between option 1 and option 2 is almost always to look for an SQL solution, because cursors are by nature slower.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.