RE: SQL Update Performance

["Luis Colorado" <LuisC@xxxxxxxxxxxxxxxx>]

Another idea... create a view. I'm not sure if this view would be updateable, but you might want to give it a shot:

Create view myView AS
select * from contrcp ctr, ADDRINP ad1 where 
      ctr.cinact = 'CC01' and ctr.ctradr = ad1.akey

and then

UPDATE  myView 
SET myView.Addr1 = 'this is a test'               

And then (optional)

Drop view myView

 -----Original Message-----
From: 	midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx]  On Behalf Of Murphy, Mark
Sent:	Thursday, February 07, 2008 3:50 PM
To:	Midrange Systems Technical Discussion
Subject:	RE: SQL Update Performance

My indexes are good, the UPPER trick and FORCS_JOIN_ORDER made no difference.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Elvis
Budimlic
Sent: Thursday, February 07, 2008 3:23 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL Update Performance

Make sure the following is in place for your original query:

- index (keyed LF) over ad1.key?
- index (keyed LF) over ctr.CINACT,ctr.CTRADR?

This problem should be solved with judicious use of proper indexing.  You
should also try to verify if you get the same behavior with SQE & CQE engine
(AND UPPER('x') = 'X' trick), as that can tell you if you're hitting some
query optimizer bug.

You can try forcing the join order, but that is in no way guaranteed to
work, or if it works it is not guaranteed to work in the future.  It's a
workaround and that's never good as long term solution (hint: avoid it if
you can).

Something like this:
1) CRTDUPOBJ OBJ(QAQQINI) FROMLIB(QUSRSYS) OBJTYPE(*FILE) TOLIB(yourLib)
DATA(*YES)
2) UPDATE yourLib/QAQQINI SET QQVAL = '*PRIMARY 2' WHERE QQPARM =
'FORCE_JOIN_ORDER'
3) CHGQRYA JOB(*) QRYOPTLIB(yourLib)

Run your update statement now and see if you get a different join order
(i.e. 2ndary dial should become primary dial now).
Note that you have to tell the job where your update runs to use your
modified QAQQINI before you run the update statement.

Let us know what happens.

HTH, Elvis

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

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

I have an update that I am trying to do on V5R2:

 

UPDATE ADDRINP ad1 

SET ad1.Addr1 = 'this is a test'               

WHERE exists( 

select * from contrcp ctr  where 

      ctr.cinact = 'CC01' and 

ctr.ctradr = ad1.akey)                                             

 

This statement takes forever to run because of the join order the optimizer
is choosing.  ADDRINP is
in Join position 1 and CONTRCP is in join position 2.  This is most likely
because I am updating
ADDRINP.  Only problem is that there are 3,000,000 records in ADDRINP, and
putting it in join position
1 forces the whole file to be read in order to pick out 6 records to be
processed.  Can anyone think
of a way to force CONTRCP to join position 1?

 

Mark Murphy

Star Base Consulting, Inc.

e: murphym@xxxxxxxxxx