Re: Why DISTINCT is costly?

Noons wrote:
> Robert Klemme wrote:
>
> > There's not much difference in those plans. Do you have an explanation
> > why SORT GROUP BY is cheaper than SORT UNIQUE in this case? Thx!
>
> There is a good difference. The other thing of course is that
> DISTINCT applies to all columns in the select list whereas group
> by only applies to columns in the group by list. If the group by
> list is smaller than the select list, you have a need to sort a
> lot less bytes. Which translates into a faster overall operation.

If the group by list is smaller than the select list,

Please explain HOW you get this condition for the case we are discussing (ie there are no group functions in the select list). It isn't significantly faster.
>
> But the best solution is always to avoid the DISTINCT.

Agreed. DISTINCT is best left for adhoc queries. Even then doing a COUNT(*) is better since you see whether you really needed the extra filtering.

>
> Too often it's just taken by developers/designers as an easy fix
> to an incorrectly specified query in the first place.

EXACTLY.
> Usually
> for fear of including more tables in a join in the mistaken assumption
> that it it is bad for performance. In most such cases, DISTINCT will
> turn out to be a worse solution.

As the original poster found out.

  Ed Received on Tue Sep 13 2005 - 12:50:25 CDT