Re: GROUP BY vs DISTINCT

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




"Baz" <bazz@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:46603f2f$0$31831$db0fefd9@xxxxxxxxxxxxxxxxx
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" <warrio@xxxxxxxxxxx> wrote in message
news:46603d13$0$3809$5402220f@xxxxxxxxxxxxxxxxxx
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" <spencer@xxxxxxxxx> a écrit dans le message de news:
uUQJnDGpHHA.3952@xxxxxxxxxxxxxxxxxxxxxxx
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
news:%23mwPg7FpHHA.4512@xxxxxxxxxxxxxxxxxxxxxxx
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" <warrio@xxxxxxxxxxx> wrote in message
news:466025fe$0$3806$5402220f@xxxxxxxxxxxxxxxxxx
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.











.

Relevant Pages

  • Re: GROUP BY vs DISTINCT
    ... "vision" of how memory is allocated. ... the test won't concern a big time difference, ... John Spencer ... since not only distinct will uselessly compare the telNumber field, ...
    (microsoft.public.access.queries)
  • Re: Invariant Recognition, Grandmother Cell, and Memory Hierarchy
    ... >Hawkins further writes about the importance of prediction and the need ... >to organize memory into feedback-driven hierarchies. ... Invariance is an important aspect of VISION, ...
    (comp.ai.philosophy)
  • Re: GROUP BY vs DISTINCT
    ... "vision" of how memory is allocated. ... the test won't concern a big time difference, ... John Spencer ... since not only distinct will uselessly compare the telNumber field, ...
    (microsoft.public.access.queries)
  • Re: GROUP BY vs DISTINCT
    ... "vision" of how memory is allocated. ... the test won't concern a big time difference, ... John Spencer ... since not only distinct will uselessly compare the telNumber field, ...
    (microsoft.public.access.queries)
  • Re: design dilemma
    ... inheritance unless there's a real strong and provable generalization ... memory, but you make vision depend on memory. ... You dont know what to do when you dont know what you're doing. ...
    (comp.object)
Loading