Hi David

>Can someone help me figure out why the following update statement is not fully optimized? Thanks!
>
>*** sys(3054) tells me that it is using the curaccount.cchanged index, and the curaccount. xline index,
>**but it doesn't say it is using the account clineid index, and it says the optimization for table account is none.
>*-- as a result I had to abandon this code and replace it with the seek/looping code below it
>*** I have numerous updates statements of the exact same form, they all run slow, haven't tested them all yet.
>
>
>
>
>*-- there is an index on account.clineid called clineid
>*-- there is an index on curaccount.clineid called xline
>*-- there is an index on curaccount.cchanged called xchange
>
>SET ENGINEBEHAVIOR 80 && tried with and without this
>PUBLIC cmemvar
>=SYS(3054,11,"cmemvar")
>UPDATE account SET cacc = curaccount.cacc, cid = curaccount.cid from account join curaccount on account.clineid = curaccount.clineid where curaccount.cchanged='Y'
>?cmemvar

Is the index xchange built like this: INDEX ON UPPER(cchanged) TAG xchange. That is my general practice and I would advise you to do that in this case unless cchanged = 'y' means something different than cchanged = 'Y' :). If it is also your practice then you need to change the above where clause to where upper(curaccount.cchanged)='Y'

You should have an FLOCK on the account table before the update command and an UNLOCK afterwards.

BTW PUBLIC declarations should be your last choice for a variable. Local first, private second.