Mythbusters - Speed Comparison Tests - Having vs Where

[Isaac]

We often see OP's using aggregate queries without any aggregation rather than SELECT DISTINCT - do you have an example to compare a SELECT DISTINCT v GROUP BY with perhaps a criteria or two?

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!

 

[Minty]

Liverpool 3 - Watford 1 ?

 

[NauticalGent]

I have now .... what are your predictions before I make the results public?

I'm going with SELECT DISTINCT as being faster than GROUP BY....

 

[Isaac]

I'm going with SELECT DISTINCT as being faster than GROUP BY....

I should've guessed that, if @isladogs is prepping us for a shocker!

 

[The_Doc_Man]

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.

 

[Isaac]

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.

 

[Isaac]

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

 

[isladogs]

Sorry - you'll have to wait until after my presentation to Denver Area AUG. Its starts at 17:00 UTC

 

[Isaac]

Alright - that's in 40 minutes I think. Good luck and congrats on the doing of it!

 

[isladogs]

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

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