Cursor performance, FOR or DECLARED

[Tim.D]

People,

I've inherited a lot of SP embedded SQL code that makes substantial
use of FOR cursors rather than DECLARED, and I am wondering whether
there is a significant difference in the performance of either.

With the declared cursor, perhaps opening, and using a 'while' loop to
fetch each record, what would be the main differences in the author
using a FOR statement in lieu thereof ? The results of the FOR
statement are seemingly to perform a significant number of inserts or
updates after modifying several variables.

I know the insert /update performance will depend on many other
underlying factors, but for management of the overall record set,
which would be the preferred manner ? I could see it being done either
way to be honest.

As an aside, the author obviously never chose to use any dynamic SQL
or to use parameter variable inserts [ ? ] as they are all presently
'values' and variables either determined by function call or set from
select statements.

My recent readings lead me to believe this code is somewhat [very]
inefficient in its present state and could do with a serious overhaul.

Any insight or guidance for overall efficiency would be appreciated.

Many thanks,

Tim

[Serge Rielau]

Tim,

A FOR loop is defined as a cursor internally.
There are in fact some advantages of using FOR loops, especially when
you add a NOT FOUND conditionhandler and teh needed IF THEN ELSE logic
to break out of a driving for a declared curor.
Letting DB2 set all this up (when using a FOR) is more efficient.
Also a FOR is easier to maintain since you don't need to declare the
variables to fetch into. You simply refer to the FOR loop variable.

regarding values vs. hostvariables for embedded SQL I would actually
prefer using the values since thsi way you give the optimizer the best
information to chew on, and little do you care if the procedure compiles
a tad longer or how big the package is.

The interesting quetsion is:
Is the logic smart or dumb?
Can the cursor/FOR be rewritten as a single SQL Statement (searched
update/insert from subselect)? Can insert statements be combined?
Note that DB2 supports multi row VALUES:
INSERT INTO T VALUES <row1>, <row2>, .....;
This is a lot faster than the typical single row inerts you see often.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab