Re: Astonished by embedded sql

On Tue, Oct 12, 2010 at 11:21 AM, <dieter.bender@xxxxxxxxxxxx> wrote:

- SQL is a set oriented language and that means a cursor

Nonsense, there's a huge difference between a internal to the DB
cursor and cursor that is surfaced to your program.

Sure, when I say
update myfile set myfield = myfield + 1

Internally the DB is (probably) using a cursor. But that's not the
cursor I'm talking about.

- positioned updates need a cursor and are mostly much faster, than
searched updates

Sure, if you're talking about searched updates that update one row at
a time. But the idea is not to do anything one row at a time,
remember RBAR (Row By Agonizing Row).

This:
update myfile set myfield = myfield + 1
where mykey = somevalue

Is always going to be better/faster than the equivalent positioned
update via cursor.

- working without commitment controll, you should use a cursor for all
updates, otherwise its not safe for anything

You'd have to provide an example of this. I don't see any differences
in "safety" between a cursor and set based processing. With or
without commitment control.

The only difference I can think of off the top of my head, would be if
there's a possibility that records may already be locked. With a
cursor, it's easy to skip the locked records. But as of 6.1, the SKIP
LOCKED DATA clause allows an easy way for the set based update to do
the same thing.

But in all honestly, the problem of locked data usually indicates
poorly designed applications; namely keeping data locked while waiting
on user input. It's never been a good idea and now-a-days it usually
a horrible idea. Your application should be designed to minimize the
time data is locked.

in other words: there is nothing wrong with a cursor!

From a performance standpoint, there's lots wrong with a cursor or

otherwise using SQL to handle one row at a time. It may not be
noticeable when your development (and/or production) environment has
1000 rows. But move it to a production environment with 100,000 or
1,000,000 rows and you'll get bit. It doesn't matter if you're on DB2
for i, DB2 for LUW, Oracle, or MS SQL Server.

If you're on the i, instead of using SQL to do thing row by row, you'd
be better off using RPG's native op-codes. That's what _they_ are
designed for.

Charles