Mythbusters - Speed Comparison Tests - Having vs Where
- Thread starter isladogs
- Start date
Isaac
Lifelong Learner
- Local time
- Today, 07:31
- Joined
- Mar 14, 2017
- Messages
- 9,930
Well, SQL Server has been able to make the performance of Select Distinct virtually the same as Group By, over the years and by now.
Thus, if I had to just make a guess, I'd say the difference is not much in Access, if they've put any effort into it.
But who knows. I'm ready to be very wrong!
PS as I've removed all my AWF notifications, I'm Watching this thread so I can learn something - fire away!
NauticalGent
Ignore List Poster Boy
- Local time
- Today, 10:31
- Joined
- Apr 27, 2015
- Messages
- 6,598
I'm going with SELECT DISTINCT as being faster than GROUP BY....
- Local time
- Today, 09:31
- Joined
- Feb 28, 2001
- Messages
- 28,744
SELECT DISTINCT has a chance of being faster because there would be fewer records to render for display.
With GROUP BY you still have rendering for all records.
With GROUP BY you still have rendering for all records.
Isaac
Lifelong Learner
- Local time
- Today, 07:31
- Joined
- Mar 14, 2017
- Messages
- 9,930
select distinct was always considered the 'lazy way' in sql server (just too easy/but not performant), for people who hadn't figured out how to properly group or group and join to a subquery), until relatively recently when their processing ninja power and algorithms became good enough to make it virtually as good as group by.
but maybe it is the opposite in access, waiting for isladogs to get us out of suspense.
why does Chrome say performant is not a word. haven't they been around since databases were invented.
but maybe it is the opposite in access, waiting for isladogs to get us out of suspense.
why does Chrome say performant is not a word. haven't they been around since databases were invented.
Isaac
Lifelong Learner
- Local time
- Today, 07:31
- Joined
- Mar 14, 2017
- Messages
- 9,930
@The_Doc_Man
Maybe, to be fairer to ourselves, we should be comparing in more specific terms.
What I'm thinking of (not sure of everyone else) is a situation where Select Distinct and Group By - with enough columns included - would, indeed, render the same actual records. I used to have people slap my hand for just "distinct'ing everything out" instead of taking the time to get with colleagues or business partners and figure out the join predicates or grouping that would have eliminated my dupes the right way--and made the business/process rules that my code was enforcing, more plain. Or so they said. But have read articles suggesting at least in SS, it no longer makes much difference. Now, it may just be the clarity benefit rather than performance.
Maybe, to be fairer to ourselves, we should be comparing in more specific terms.
What I'm thinking of (not sure of everyone else) is a situation where Select Distinct and Group By - with enough columns included - would, indeed, render the same actual records. I used to have people slap my hand for just "distinct'ing everything out" instead of taking the time to get with colleagues or business partners and figure out the join predicates or grouping that would have eliminated my dupes the right way--and made the business/process rules that my code was enforcing, more plain. Or so they said. But have read articles suggesting at least in SS, it no longer makes much difference. Now, it may just be the clarity benefit rather than performance.
isladogs
MVP / VIP
- Local time
- Today, 15:31
- Joined
- Jan 14, 2017
- Messages
- 18,549
Apologies for the delay in posting the results....
As this topic seems to have got the attention of several members., I have now posted the results in another thread in the Code Repository
As this topic seems to have got the attention of several members., I have now posted the results in another thread in the Code Repository
Mythbusters - SELECT DISTINCT vs GROUP BY
Hi all I updated my Mythbusters: WHERE vs HAVING thread yesterday https://www.access-programmers.co.uk/forums/threads/mythbusters-speed-comparison-tests-having-vs-where.302225 When I did so, @CJ_London reminded me of a previous request to test the following: We often see OP's using aggregate...
www.access-programmers.co.uk
