GROUP BY vs DISTINCT

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.

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

"Warrio" wrote in message
...
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.

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" a écrit dans le message de news:
...
It's a meaningless comparison because they do completely different things.

"Warrio" wrote in message
...
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.

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?

"Warrio" wrote in message
...
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" a écrit dans le message de
news:
...
It's a meaningless comparison because they do completely different
things.

"Warrio" wrote in message
...
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.

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.

"Baz" wrote in message
...
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?

"Warrio" wrote in message
...
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" a écrit dans le message de
news:
...
It's a meaningless comparison because they do completely different
things.

"Warrio" wrote in message
...
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.

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


"Warrio" wrote in message
...
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.

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" vanderghast@VirusAreFunnierThanSpam wrote in message
...
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


"Warrio" wrote in message
...
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.

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



"John Spencer" wrote in message
...
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
.

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.


"John Spencer" a écrit dans le message de news:
...
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" vanderghast@VirusAreFunnierThanSpam wrote in message
...
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


"Warrio" wrote in message
...
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.

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" wrote in message
...
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.


"John Spencer" a écrit dans le message de news:
...
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" vanderghast@VirusAreFunnierThanSpam wrote in message
...
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


"Warrio" wrote in message
...
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.