If you have order set to something on any of the participating files in your SQL statement, that can slow it down tremendously (don't ask...). It is weird, but if you have order set on SCAN, LOCATE, REPLACE ALL, etc. data handling commands, VFP slows way down (comparatively) compared to operations with no ORDER set on any files. I've personally seen 100 -fold speed increases on large tables.

Also, look at the SQL showplan for your SQL statement. This is from VFP 9 help file:


Enables or disables display of Rushmore optimization levels for queries.


SYS(3054 [, 0 | 1 | 11 | 2 | 12] [, cMEMVAR])



Parameters
0
(Default) Disables the display of Rushmore optimization levels.

1
Enables the display of the Rushmore filter optimization levels.

11
Enables the display of Rushmore join optimization levels.

2
Includes SQL statement with the display of the Rushmore filter optimization levels.

12
Includes SQL statement with the display of Rushmore join optimization levels.

cMEMVAR
Specifies an undeclared or declared PUBLIC or LOCAL memory variable in which to store the showplan information. If you have not declared cMEMVAR, Visual FoxPro creates it as a PRIVATE variable.