GROUP BY vs DISTINCT

[Warrio]

Hello!

Which of GROUP BY and DISTINCT is the quickest? and why?

Thanks for any relevant answer!

PS: Please don't tell me that there is no difference because it's a matter
of one millionth second.

 

[Baz]

It's a meaningless comparison because they do completely different things.

 

[Warrio]

Hi Baz!

So Would you please Tell me if there is difference in the result between
these 2 queries:

"SELECT DINSTINC Field1 FROM Table1"
and
"SELECT Field1 FROM Table1 GROUP BY Field1"

??? in this case, the result will be the same, but I'm asking which one is
the most efficient?

 

[Baz]

What I would tell you is that is a pointless way to use GROUP BY. If you
are really bothered about which of those is quicker why not create a table
with a lot of records in it and test it?

 

[Baz]

Just to explain further, suppose that Table1 has 50 columns, and you do
this:

SELECT DISTINCT * FROM Table1

You *could* get the same results with your GROUP BY trick, but it's not a
query I would want to create, life's too short. Your little academic
exercise might seem meaningful to you with a query that returns only one
column, but it doesn't have much practical use. Not what GROUP BY is
designed for, you see.

 

[Michel Walsh]

GROUP BY will NOT be slower, since it forces you to specify which field(s)
you really need to define 'unique-ness', it will be faster, or take the same
time, at worst, than DISTINCT, but note that GROUP BY is more rigid than
DISTINCT, since any expression in the SELECT clause must then be either in
the GROUP BY, either aggregated.


SELECT DISTINCT lastName, firstName, telNumber FROM somewhere

will be slower than

SELECT lastName, firstName, MAX(telNumber) FROM somewhere GROUP BY lastName,
firstName



since not only distinct will uselessly compare the telNumber field, but it
will also reserve memory to keep it as 'group identifier'; the second
solution neither does the comparison, neither has to reserve that extra
memory.



Vanderghast, Access MVP

 

[John Spencer]

I am not sure you are correct in your evaluation. I think the only way to
know would be to test with data and to run the tests multiple times.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Michel Walsh]

Unless a DISTINCT is effectively implemented as a GROUP BY ...



SELECT DISTINCT lastName, firstName, telNumber FROM somewhere


is 'evaluated' as if it was:


SELECT lastName, firstName, telNumber FROM somewhere
GROUP BY lastName, firstName, telNumber




Vanderghast, Access MVP

 

[Warrio]

Great Thanks Michel for your answer! exactly what I was looking for!

I'll have probably to think about it again once or twice so I can really
have a vision of how the memory is allocated!

John, the best way would be to have the code that's behind these GROUP BY
and DISTINCT

the test won't concern a big time difference, plus there is too many
variables within the same machine, you'd have only an average.

thanks again.

 

[Baz]

Except he's wrong, and so are you. I just tried it (as you easily could too
if you wanted) on a table with 100,000 records where the field being
selected is a single containing random numbers. DISTINCT is significantly
faster if the field is not indexed, and an order of magnitude faster if the
field is indexed.

So now what?

You must have a very special kind of insight if this is going to give you a
"vision" of how memory is allocated.

 

[Warrio]

Were you the one who said that life is too short?? appearently you have a
lot of time to waste
and about your test, maybe when you were testing the query with GROUP BY,
you had your windows update downloading files

try it 100 times during different times a day and please get back to me

 

[Michel Walsh]

Both queries plan being exactly the same, with:


SELECT DISTINCT au_fname FROM authors

and

SELECT au_fname FROM authors GROUP BY au_fname


*if* you experience some difference in time execution, that is probably due
to some exterior cause.



Vanderghast, Access MVP

 

[Michel Walsh]

and here is the query plan, again, the same, for both statements (excuse the HTML format, I suspect that without it, it turns out un-readable):

|--Sort(DISTINCT ORDER BY[pubs].[dbo].[authors].[au_fname] ASC))

|--Index Scan(OBJECT[pubs].[dbo].[authors].[aunmind]))




Vanderghast, Access MVP

 

[Warrio]

Thanks again!

I'll try to explain to the girls that I'll find tonight the diffrence between GROUP BY AND DISTINCT, but I guess that I'll have to make them drink so they'd understand something...

have a nice weekend!

"Michel Walsh" <[email protected]> a écrit dans le message de %[email protected]...
and here is the query plan, again, the same, for both statements (excuse the HTML format, I suspect that without it, it turns out un-readable):

|--Sort(DISTINCT ORDER BY[pubs].[dbo].[authors].[au_fname] ASC))

|--Index Scan(OBJECT[pubs].[dbo].[authors].[aunmind]))




Vanderghast, Access MVP

 

[David Cox]

There isn't a button for DISTINCT :->

 

[Warrio]

|
| Vanderghast, Access MVP
|

Un grand bonjour amical à Michel !

(dommage qu'il ne vient plus sur ce forum...)

--
A+
Pierre (3stone) Access MVP
Perso: http://www.3stone.be/
MPFA: http://www.mpfa.info/ (infos générales)

"Michel Walsh" <[email protected]> a écrit dans le message de %[email protected]...
and here is the query plan, again, the same, for both statements (excuse the HTML format, I suspect that without it, it turns out un-readable):

|--Sort(DISTINCT ORDER BY[pubs].[dbo].[authors].[au_fname] ASC))

|--Index Scan(OBJECT[pubs].[dbo].[authors].[aunmind]))




Vanderghast, Access MVP

 

[Michel Walsh]

"Warrio", c'est un alias pour "3stone" ?


Je renvoie le grand bonjour à Pierre, en tout cas.



Vanderghast, Access MVP

|
| Vanderghast, Access MVP
|

Un grand bonjour amical à Michel !

(dommage qu'il ne vient plus sur ce forum...)

--
A+
Pierre (3stone) Access MVP
Perso: http://www.3stone.be/
MPFA: http://www.mpfa.info/ (infos générales)

"Michel Walsh" <[email protected]> a écrit dans le
message de %[email protected]...
and here is the query plan, again, the same, for both statements (excuse the
HTML format, I suspect that without it, it turns out un-readable):

|--Sort(DISTINCT ORDER BY[pubs].[dbo].[authors].[au_fname] ASC))
|--Index Scan(OBJECT[pubs].[dbo].[authors].[aunmind]))



Vanderghast, Access MVP

 

[Michel Walsh]

but there is a property in the query's property sheet: "Unique Values", set
to no, by default. {:?)
(the property is reset to no if you set "Unique Records" to yes.)


Vanderghast, Access MVP

 

[Warrio]

Non du tout!
warrio c'est le méchant sur nintendo...

"Warrio", c'est un alias pour "3stone" ?


Je renvoie le grand bonjour à Pierre, en tout cas.



Vanderghast, Access MVP

|
| Vanderghast, Access MVP
|

Un grand bonjour amical à Michel !

(dommage qu'il ne vient plus sur ce forum...)

--
A+
Pierre (3stone) Access MVP
Perso: http://www.3stone.be/
MPFA: http://www.mpfa.info/ (infos générales)

"Michel Walsh" <[email protected]> a écrit dans le
message de %[email protected]...
and here is the query plan, again, the same, for both statements (excuse
the HTML format, I suspect that without it, it turns out un-readable):

|--Sort(DISTINCT ORDER BY[pubs].[dbo].[authors].[au_fname] ASC))
|--Index Scan(OBJECT[pubs].[dbo].[authors].[aunmind]))



Vanderghast, Access MVP

 

[David W. Fenton]

I am not sure you are correct in your evaluation. I think the
only way to know would be to test with data and to run the tests
multiple times.

Well, surely the way to tell if they are parsed the same is to use
SHOWPLAN and find out. If they are parsed the same, then there isn't
any need to test with data. If they aren't, then you can only answer
the question with testing.