>>Many years ago I discovered that if you use REST instead of ALL, VFP is much, much quicker, since it doesn't lock in the same way. And remember that ALL is the default for many commands when you use FOR. So DELETE REST FOR somecondition is much quicker than DELETE ALL FOR somecondition or DELETE FOR somecondition.
>>
>>NB! Make sure you GO TOP first!
>
>Good tip, Tore. I was not aware of that difference. Unfortunately, in this case it did not appear to make a significant difference. I killed the process after about five minutes, so don't know how long it might have taken. But I'm afraid this is a case where reducing the time from 6 hours to five wouldn't be all that useful.
>
>This code, however, ran in 9 seconds. It finally occured to my poor feeble brain that ZAP doesn't trigger RI delete code; bingo!
>I was hoping to avoid an exclusive access requirement, but hey, I can live with the inconvenience.
>
>
>SELECT * FROM clients WHERE clientid NOT IN (SELECT clientid FROM archiveclients) INTO CURSOR tokeep
>lcAlias = DBF()
>SELECT clients
>ZAP
>APPEND FROM (lcAlias)
>
>Thanks to both you and Naomi for your advice.
Add NOFILTER clause to your select. Also using LEFT JOIN with NULL could be slightly quicker.
If it's not broken, fix it until it is.
My Blog