I would say that, despite your existent TAGs, Rushmore is NOT operating here at all! You don't have a WHERE clause (or a join) to give Rushmore anything to work with.

The command without the ORDER BY would no doubt return faster, but I don't think that it would necessarily have finished by the time you get SOME rersult back. Without the ORDER BY any record will do as some result getting back to you.
With the ORDER BY you force it to get all records, then sort them, THEN return the result to you.

At least that's the way I see it.

Jim Nelson


>>>Hi everyone,
>>>
>>>I need to do a SELECT DISTINCT from a table with about 100,000 records to populate the top level nodes of a treeview control, with the user able to select different fields to populate the treeview with.
>>>
>>>I have index tags on the fields that the user can specify and UPPER() of the same fields.
>
>>>
>>>The following query takes about 36 seconds:
>>>
>>>

SELECT DISTINCT &lcField FROM DocsMast ;
>>>   ORDER BY &lcField ;
>>>   INTO CURSOR TreeInit

>>>
>>>The following code runs in less than 1 second:
>>>
>>>

SELECT DISTINCT &lcField FROM DocsMast ;
>>>  INTO CURSOR TreeInit
>>>
>>>INDEX ON UPPER(&l_cField) TAG Sorted

>>>
>>>Why? Is there a way to get the result ordered without having to do a manual INDEX or wait for 30+ seconds? I gather that VFP is applying the ORDER BY to the entire table before applying the DISTINCT clause.
>>
>>How about select &lcField group by &lcField instead of select distinct?
>
>Hi Nadya,
>
>No difference. The problem appears to be only related to the ORDER BY. The SELECT DISTINCT runs very fast without the ORDER BY.
>
>Steve