Re: GROUP BY vs DISTINCT

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
    ... since not only distinct will uselessly compare the telNumber field, ... will also reserve memory to keep it as 'group identifier'; ... matter of one millionth second. ...
    (microsoft.public.access.queries)
  • Re: GROUP BY vs DISTINCT
    ... GROUP BY will NOT be slower, since it forces you to specify which field ... since not only distinct will uselessly compare the telNumber field, ... will also reserve memory to keep it as 'group identifier'; ... of one millionth second. ...
    (microsoft.public.access.queries)
  • Re: New Linux Box
    ... For Steve's benefit, the BIOS does not reserve memory for devices, only ... That's an important distinction because the physical memory is still ...
    (comp.os.linux.hardware)
  • Re: EWF - How much RAM is Enough?
    ... When you reserve memory with VirtualAlloc. ... Have an opinion on the effectiveness of Microsoft Embedded newsgroups? ... > RAM, I shouldnt bother with a page file, correct? ...
    (microsoft.public.windowsxp.embedded)
  • Re: New Linux Box
    ... For Steve's benefit, the BIOS does not reserve memory for devices, only ... That's an important distinction because the physical memory is still ...
    (comp.os.linux.hardware)
Loading