May 19, 2006 at 3:00 am
Does anyone know what is more efficient for selecting distinct rows...select distinct or using a group by? Assuming that all you want to return is one column
eg...
select distinct acctno from accts
or
select acctno from accts group by acctno
Many thanks!
May 19, 2006 at 3:28 am
The greatest factor is proper indexes.
I run the two questions against our production server with 90+ million rows.
DISTINCT took 66 seconds.
GROUP BY took 8 seconds.
N 56°04'39.16"
E 12°55'05.25"
May 19, 2006 at 7:30 am
I've asked this of multiple sources. The basic answer I've gotten is "It depends." It depends on the version of SQL Server you're running (apparently, they tweaked how DISTINCT optimizes at one point). It depends on what kind of index you have on the column. It depends on how many distinct values you have, versus how many rows you have.
Your best bet is just to run a couple comparison queries, and see which one works out better for you. Personally, I tend to avoid DISTINCT. But that's mostly because I can't visualize in my head how SQL Server is handling the data at that point. I can visualize GROUP BY very easily.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply