Q
Problem solve Get help with specific problems with your technologies, process and projects.

Cursor or application code?

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:

  1. SQL
  2. cursor
  3. 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.


This was last published in October 2004

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close