>>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