RE: SQL Update Performance

Even with ADDRINP being a primary dial, I would expect better performance.
V5R2 isn't supported, otherwise I'd advise pinging IBM.

As it stands and being that Mark said regular SELECT performs well, you can
try using updateable cursor inside an embedded SQL program (specifying FOR
UPDATE OF and then WHERE CURRENT OF). For example:

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/sqlp/rbafyupdati
ngasretrieved.htm


I think in this specific case something like this might work (forgive my
clumsy attempt at RPG):

DECLARE forupdateof CURSOR FOR
SELECT Addr1
FROM ADDRINP
WHERE akey in (select ctradr
from contrcp
where cinact = 'CC01')
FOR UPDATE OF Addr1;

EXEC SQL
OPEN forupdateof
END-EXEC.

/* fetch all (6) records in a loop and as you go update the field */

EXEC SQL
FETCH forupdateof INTO hvAddr1
END-EXEC.

while (sqlca.sqlcode is still good (i.e. >= 0 and not equal to 100))

EXEC SQL
UPDATE ADDRINP
SET Addr1 = 'this is a test'
WHERE CURRENT OF forupdateof
END-EXEC.

/* still inside the loop, fetch next record */
EXEC SQL
FETCH forupdateof INTO hvAddr1
END-EXEC.

/* outside the loop */
EXEC SQL
CLOSE forupdateof
END-EXEC.

Mark mentioned Java, so he can try it there instead of RPG or C or...

Let me know if this perform any better.

HTH, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: RE: SQL Update Performance

Elvis,

Since this a an update query and we're at v5r2, isn't the CQE always used?

It seems we're pretty much SOL. The query engine insists on joining ADDRINP
in position 1, since
there's an IN predicate and a subselect involved. At least at v5r2.
Perhaps the QE would be smarted
at v5r3, v5r4, v6r1.

Charles Wilt