GROUP BY vs DISTINCT

"Warrio" wrote in
:

Were you the one who said that life is too short??

The test demonstrated exactly what he had forecast, that DISTINCT is
the logical way to get DISTINCT values, and that GROUP BY is for a
different purpose (and would thus very likely not be as fast).

He only did the test because you stop you from continuing to natter
on about it.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"Michel Walsh" wrote in
:

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]))

Er, that's a SQL Server query plan, not a Jet one.

Who said we were using SQL Server as the back end?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"Michel Walsh" wrote in
:

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]))

And if you run it in Jet, the SHOWPLAN gives this (Jet 3.5 or 4.0,
run both wiht LastName indexed and indexed (non-unique)):

--- Query1 ---
[SELECT DISTINCT tblPerson.LastName
FROM tblPerson;]

- Inputs to Query -
Table 'tblPerson'
- End inputs to Query -

store result in temporary table


--- Query2 ---
[SELECT tblPerson.LastName
FROM tblPerson
GROUP BY tblPerson.LastName;]

- Inputs to Query -
Table 'tblPerson'
- End inputs to Query -

01) Group table 'tblPerson'

This shows that the answer to the question depends on the database
engine being used, and, as expected, Jet handles the two completely
differently.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

it's not that I want to waste my time saying that you are right or write
saying the only way is testing...

but the good thing in computing is that everything has a reason, somewhere
hidden or invisible, but it does exist! and saying that testing will give
the right answer is like gambling! how many parameters can influence your
testing????

even if you make it on a new machine, without any connection and only access
running! you can't have the same result.

come on, computing is one of the rare exact science, don't make something
esle!


"David W. Fenton" a écrit dans le message de
news: ...
"John Spencer" wrote in
:

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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

If you are so convinced that Michel is right, on the basis of no evidence
whatsoever, I suggest that YOU better try it.

If someone here told you that the moon is made of green cheese I guess you
would believe that too.

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


"Baz" a écrit dans le message de
news:
...
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.

My God what a pair of idiots (I mean Walsh and Warrio, not you David).

Probably better to let these two fools massage each other's egos in French
while we go and do something more worthwhile.

"David W. Fenton" wrote in message
. 1...
"Michel Walsh" wrote in
:

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]))

Er, that's a SQL Server query plan, not a Jet one.

Who said we were using SQL Server as the back end?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

If you really believe that garbage then you have no chance of getting an
answer to your question. Oh, but I'm forgetting, you have already accepted
Michel's answer, even though he hasn't a clue what he's talking about.

"Warrio" wrote in message
...
it's not that I want to waste my time saying that you are right or write
saying the only way is testing...

but the good thing in computing is that everything has a reason, somewhere
hidden or invisible, but it does exist! and saying that testing will give
the right answer is like gambling! how many parameters can influence your
testing????

even if you make it on a new machine, without any connection and only
access
running! you can't have the same result.

come on, computing is one of the rare exact science, don't make something
esle!


"David W. Fenton" a écrit dans le message
de
news: ...
"John Spencer" wrote in
:

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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

It's a good thing he doesn't because speculation and misinformation are no
use to anyone.

"3stone" a écrit dans le message de news:
...
|
| 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)

Indeed, I assumed that Jet would solve these simple basic queries the same
way MS SQL Server would do, but it seems Jet optimizer fails to optimize the
second query.

In MS SQL Server, there is no difference between the queries plan, even if
there are multiple fields (indexed, or not) between the DISTINCT and the
logical equivalent GROUP BY.


Vanderghast, Access MVP

"David W. Fenton" wrote in message
. 1...
"Michel Walsh" wrote in
:

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]))

And if you run it in Jet, the SHOWPLAN gives this (Jet 3.5 or 4.0,
run both wiht LastName indexed and indexed (non-unique)):

--- Query1 ---
[SELECT DISTINCT tblPerson.LastName
FROM tblPerson;]

- Inputs to Query -
Table 'tblPerson'
- End inputs to Query -

store result in temporary table


--- Query2 ---
[SELECT tblPerson.LastName
FROM tblPerson
GROUP BY tblPerson.LastName;]

- Inputs to Query -
Table 'tblPerson'
- End inputs to Query -

01) Group table 'tblPerson'

This shows that the answer to the question depends on the database
engine being used, and, as expected, Jet handles the two completely
differently.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Half of the pair will humbly tell you that if it just happen that Jet is ...
crippled... in the particular case, that is surely not 'by design', since
clearly, other SQL optimizer achieve to find the SAME plan of execution for
a DISTINCT and the logically equivalent GROUP BY.


There is nothing 'built-in' the concepts of DISTINCT or of GROUP BY that may
leads you to think they logically 'differ', exception made for some syntax
particularities like DISTINCT which can be used with *, while GROUP BY
cannot (due to extra validations carried over when GROUP BY is explicitly
used).



Vanderghast, Access MVP.


"Baz" wrote in message
...
My God what a pair of idiots (I mean Walsh and Warrio, not you David).

Probably better to let these two fools massage each other's egos in French
while we go and do something more worthwhile.

"David W. Fenton" wrote in message
. 1...
"Michel Walsh" wrote in
:

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]))

Er, that's a SQL Server query plan, not a Jet one.

Who said we were using SQL Server as the back end?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/