>>>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.
>
>Regarding Thomas' reply, the seek/replace code is orders of magnitude faster, so in this case the benefit I would get by using scan instead of do while not eof() wouldn't be significant, I don't think. Also, in other benchmarks I've done, I've found that in loops with replaces, the replace statements contribute to the bulk of the processing time, and the looping construct time is negligible. I find the old style do while not eof() more readable. Correct me if you think I'm off base on this.
>
>Regarding Mike's reply, the public was only in there for testing from the command line. Regarding the flock(), the application is not multi-user, for the most part, but even so it was sloppy for me to not have the flock(). But does not having it impact performance? Won't VFP try to lock the entire database in this case? At least I think read that somewhere in a tutorial on given commands and their locking effects.

Regarding Tamar's reply:
I try to right SQL in VFP so I can switch between it and SQL Server more easily. VFP's optimizer is good, but sometimes it's not great. You could do a subselect to grab the xchange="Y" records first to help it out.

UPDATE account 
  SET cacc = curaccount.cacc, cid = curaccount.cid 
  from account 
  join (select curaccount.clineid from curaccount where curaccount.cchanged='Y') thechanged 
  on account.clineid = thechanged.clineid

Regarding Thomas' reply:
Sometimes, you just can't beat xbase style. I really think the old do while not eof() stuff is not all that readable. You can get the same result more succinctly:

SELECT curaccount
scan for xchange="Y" and seek(clineid,"account","clineid")
	Replace cacc With curaccount.cacc, cid With curaccount.cid In account
Endscan

Regarding my reply:
I remember reading that the flock should speed the update quite a lot, but that isn't scientific of me at all. A quick test shows the flock does not help. In any case, VFP doesn't lock the whole file, just the header.

Your code sample has a PUBLIC cmemvar and later on a ?cmemvar. After the ?cmemvar what purpose does the cmemvar variable serve at the command window? My point is scope variables to the piece of code and rarely if ever public.