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. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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. |
#17
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|