Thanks to everyone who has made suggestions. I am in the process of testing of all the various suggestions and benchmarking them. and I will let you know the results when I finish. I will also compare update vs select as Tamar has suggested.

>>>>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
>>>
>>>It's telling you that there's no optimization for Account because there are no filters on Account. That line refers to filtering, not joins. For joins, if both sides are indexed, Rushmore chooses the side where it thinks the index gives a bigger bang. What are the relative sizes of the two tables?
>>>
>>>Tamar
>>
>>Thanks Tamar. After I got your reply, and I re-read some tutorials on sys(3054), I realized I didn’t have a complete understanding of the different parameters and interpreting the results. I think I understand it better now, but, I am still confused by the results.
>>
>>Here are the results using both 11 and 1 as the parameter to sys(3054)
>>
>>*sys(3054,1)
>>Rushmore optimization level for table account: none
>>Using index tag Xchange to rushmore optimize intermediate result
>>Rushmore optimization level for intermediate result: partial
>>
>>
>>*sys(3054,11)
>>Rushmore optimization level for table account: none
>>Using index tag Xchange to rushmore optimize intermediate result
>>Rushmore optimization level for intermediate result: partial
>>Joining table account and intermediate result using index tag Xline
>>
>>It looks to me like the 11 parameter is a superset of the 1 parameter – i.e. that it reports on both filter conditions and join conditions? So now I see that the first three lines apply to filter optimization and the fourth to join optimization.
>>
>>However, the fourth line about the join is still confusing to me. You said “Rushmore chooses the side where it thinks the index gives a bigger bang” In this case, curaccount is a cursor with the exact same number of rows as the table – account. But it is using the “xchange” index of curaccount, so there will only be a few filtered rows to deal with in curaccount, out of several thousand. But Rushmore chose to join using xline which is on curaccount, not account. If the intermediate result only has a few rows, and the index that Rushmore chooses to use on the intermediate is one with only a few rows, is that problematic - should it be using the clineid index on account? Why does Rushmore only choose one index – on a join – to tell the truth I have no idea what it is doing internally - so I'm not sure what I'm asking. In any event, this code takes about 10 seconds to execute whereas the seek/replace code executes in milliseconds. I have another query of the exact same form, which has 200K rows in the base table and the corresponding cursor – this one takes about 6 minutes to execute, and the equivalent seek/replace code is sub-second.. The short of it is if there is no way to fully optimize a query of this type then I have to go with the seek/replace code.
>
>I've never experimented with SYS(3054) on UPDATE and I'm not sure what special rules there might be there. I think it might be useful for you to do some testing with a query that just selects all the records to be updated and see what you get. If you get different results that way, then there may be something about UPDATE that changes the picture.
>
>Tamar