I really don't know about what Jet does in the step "group" of its query
plan. MS SQL Server is more detailed and uses the index (if it exists, and
if the number of records is high enough) of the field to be DISTINCT/GROUP
BY.
If you implant that strategy yourself, in this case, and if the index
exists, since the reading of the records (note the query is covered by the
index) is done already sorted, so you can dump the result list onto a stack
rather than on a sorted list. Indeed, to find if a record is already in the
"selected/grouped" list, you only have to compare it with the top value on
the stack. If the index does not exist, you have to relay on stats (if they
are available): does the result will have much less rows than the initial
data, or not. If the number of row is much smaller in the result, may be
preferable to use a sorted list (faster to sort, a little bit longer to
search) than to sort the whole initial set first, then use a stack (longer
to sort the whole initial set, faster to 'search' for existence). Again, if
the index exists, the query being covered by the index, you don't need to
touch the data at all, just the index, so, you have less IO (since index
would be, generally, more compact, than the 'table', it will resides on less
'pages', so less requirements to hit the hard disk). You see, that is what
SQL is: it knows different way of doing the described set, and decide which
strategy it will take. If you want to do the same, clearly, you should
weight the similar alternatives, and 'hard code' multiple solutions, not
just one (in general).
Vanderghast, Access MVP