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

>Is it just as slow when you use the literal field name, i.e. do not use the "&" operator in your ORDER BY clause? I wouldn't think it should be so slow to order...

>Results are the same with the literal field name.

Hi Joe,

>Can you try two queries? Do the select distinct and then select from the intermediate cursor to apply your order. At least it saves making a temp index and possibly have to worry about lingering temp CDX files... If you do have to use the temp index remember to DELETE TAG ALL on your cursor so that a temp CDX doesn't hang around...

The two queries approach works at about the same speed as the SELECT / INDEX pair I was using. I had been worried about leftover .CDX files as well. This may be a better approach.

Thanks,

Steve