>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
>
>*--- replacement code because can't get the above update statement optimized.
>Set Order To xchange In curaccount
>Go Top In curaccount
>If Seek("Y","curaccount","xchange")
> Do While Not Eof("curaccount") And curaccount.cchanged="Y"
> If Seek("curaccount.clineid","account","clineid")
> Replace cacc With curaccount.cacc, cid With curaccount.cid In account
> Endif
> Skip In curaccount
> Enddo
>Endif
How are you opening the account table?
Are there any filters in the index?
Anyone who does not go overboard- deserves to.
Malcolm Forbes, Sr.