In scenario 1, you are making one comparison for every record in table 1 (6500). In scenario 2, you are making one comparison for every record in Cursor2 (probably less than 10). That's a huge difference.
In addition, the SQL statement creates an intermediate table that holds all the records it is supposed to update. Scenario 2 uses the raw data tables to do the same thing.
>Note - It looks like all my whitespace got erased...
>
>Here's my situation: I have a cursor ("Cursor2") which contains the CustID of one or more customers from a multi-select listbox. When the user clicks a button, all of those customers should have a certain field ("Field") changed to "Y" in the customer table ("Table1"). Cursor2 and Table1 are both indexed on CustID.
>
>Here is the original code, which takes about 10 seconds to complete on a fast machine:
>
>
UPDATE Table1;
> SET Table1.Field = "Y";
> WHERE Table1.CustID IN ;
> (SELECT Table2.CustID FROM Cursor2)
>
>Table1 resides on a file server. It is 7.2MB in size, has a 1MB index, and has about 6,500 records, so it isn't huge by any means. After I re-wrote the code this way, the process is instantaneous:
>
>
SELECT Cursor2
>SCAN ALL
> IF Seek(Cursor2.CustID, "Table1", "CustID")
> REPLACE Table1.Field WITH "Y" IN Table1
> ENDIF
>ENDSCAN
>
>My question is: if I have both Table1 and Cursor2 indexed on the join field (CustID), then why is the second way so much faster than the first? I get the feeling that I'm missing something important here, so I'm looking forward to being enlightened. A general overview of the way Fox deals with network files would be very helpful too, if anyone can point me to it. Thanks.
Larry Miller
MCSD
LWMiller3@verizon.netAccumulate learning by study, understand what you learn by questioning. -- Mingjiao