A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

GROUP BY vs DISTINCT



 
 
Thread Tools Display Modes
  #11  
Old June 1st, 2007, 04:52 PM posted to microsoft.public.access.queries
Warrio
external usenet poster
 
Posts: 38
Default GROUP BY vs DISTINCT

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.











  #12  
Old June 1st, 2007, 06:46 PM posted to microsoft.public.access.queries
Michel Walsh
external usenet poster
 
Posts: 2,404
Default 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" wrote in message
...
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.











  #13  
Old June 1st, 2007, 06:58 PM posted to microsoft.public.access.queries
Michel Walsh[_2_]
external usenet poster
 
Posts: 56
Default GROUP BY vs DISTINCT

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

  #14  
Old June 1st, 2007, 07:21 PM posted to microsoft.public.access.queries
Warrio
external usenet poster
 
Posts: 38
Default GROUP BY vs DISTINCT

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

  #15  
Old June 1st, 2007, 07:24 PM posted to microsoft.public.access.queries
David Cox[_2_]
external usenet poster
 
Posts: 57
Default GROUP BY vs DISTINCT

There isn't a button for DISTINCT :-


"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.



  #16  
Old June 1st, 2007, 07:28 PM posted to microsoft.public.access.queries
Warrio
external usenet poster
 
Posts: 38
Default GROUP BY vs DISTINCT


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

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

  #17  
Old June 1st, 2007, 07:47 PM posted to microsoft.public.access.queries
Michel Walsh[_2_]
external usenet poster
 
Posts: 56
Default GROUP BY vs DISTINCT

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


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



Vanderghast, Access MVP



"Warrio" wrote in message
...

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

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

  #18  
Old June 1st, 2007, 08:18 PM posted to microsoft.public.access.queries
Michel Walsh
external usenet poster
 
Posts: 2,404
Default GROUP BY vs DISTINCT

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


"David Cox" wrote in message
...
There isn't a button for DISTINCT :-


"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.





  #19  
Old June 1st, 2007, 08:32 PM posted to microsoft.public.access.queries
Warrio
external usenet poster
 
Posts: 38
Default GROUP BY vs DISTINCT

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

"Michel Walsh" a écrit dans le
message de news: ...
"Warrio", c'est un alias pour "3stone" ?


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



Vanderghast, Access MVP



"Warrio" wrote in message
...

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

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



  #20  
Old June 1st, 2007, 09:21 PM posted to microsoft.public.access.queries
David W. Fenton
external usenet poster
 
Posts: 3,373
Default GROUP BY vs DISTINCT

"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/
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:11 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.