Requires Free Membership to View
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:
- SQL
- cursor
- 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 first published in October 2004
Join the conversationComment
Share
Comments
Results
Contribute to the conversation