>>> I know how to speed up SQL with Rushmore (if it's
>>>simple), but I know nothing about how VFP handles Union clause or nested
>>>selects. May be it's my personal habit, but I like do calculation process
>>>step-by-step, it could be more understandable after two-three
>>month...;))),
>>>After all, this doesn't matter ;)
>>
>>It matters a lot. And I agree that sometimes you can/must trade performance
>>for readability. But this doesn't mean readability = performance. :)
>>
>>Vlad
>
>I've had some cases where I simply had to cut the select into two or
>three simpler selects to gain speed. It's the case when more than two
>tables are involved, and at least one of them is large. The first select
>is used to cut down the number of records from the big table involved,
>and the second does the joins etc.
>
>Also, I've found a case when I get better off avoiding Select - it's
>when I pull out a small set of data from a large table, and the set has
>a common key value. Then Select * from the_table where
>customer=the_customer may be slower than
>
>Select the_table
>set order to customer
>seek the_customer
>copy to cursor while customer=the_customer
>
>The last command doesn't look like this, of course, but the idea is to
>copy the structure of the table into a cursor, seek the key, then
>scatter/insert into cursor while the key. It crosses my mind now that
>the case when this was quicker was when I didn't really have a key on
>the customer field, but rather a compound key starting with customer, so
>Rushmore probably never fired. Nevertheless, I didn't have to touch the
>table design, and it started working at a snap, while the Select did
>take a couple of seconds to perform.

I suppose you close&reopen FoxPro between any 2 tests with SELECT-SQL. If you don't, the tests are irrelevant since FoxPro optimizes from one SELECT to the next when possible.

As for the case when SELECT is slower than COPY ... WHILE. This is understandable if the extracted set is very small compared with the complete table. You can have better results even if you have an index and an Rushmore expression.

As I said other time, this is why is good to do tests! You have better apps, isn't it? :)

Vlad