RE: SQL Update Performance -- MIDRANGE-L

When Mark said a regular select works well he was talking about:

Select ad1.*
From contrcp, addrinp ad1
Where cinact = 'CC01' and ctradr = ad1.akey

This select shows the same poor performance as the update:
select *
from addrinp ad1
WHERE ad1.akey in (select ctradr
from contrcp
where cinact = 'CC01'
)

Again, with the subselect, it seems the QE is bound and determined to process addrinp using a table
scan in join pos 1.

Charles Wilt
Software Engineer
CINTAS Corporation - IT 92B
513.701.1307

wiltc@xxxxxxxxxx

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of Elvis Budimlic
Sent: Friday, February 08, 2008 1:06 PM
To: 'Midrange Systems Technical Discussion'
Subject: 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/rbafyupda
ti
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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.