>Hi Mike, > >>How many distinct records are being returned? If there are mostly distinct record, you'll be dealing with close to 100,000 records in the result. You could try this too.. > >Only about 50-60 records are returned. > >>Create permanent unique index(es) for the particular field(s). >>INDEX ON &lcField tag UNIQUE >> >>I don't expect you would want to do this index command each time <g>. > >I tried creating a UNIQUE tag, but it didn't help the SELECT with the ORDER BY. > >>Then >> >>SET ORDER TO TAG >>SCAN >> ...Update the treeview here... >>ENDSCAN > >That's true. I could just do that... The frustrating thing is that the SELECT is blazing fast without the ORDER BY.... > >Thanks, > >Steve > > >>>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: >>> >>>
>>> >>>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.
