Mythbusters - Speed Comparison Tests - Having vs Where (1 Viewer)

isladogs

MVP / VIP
Local time
Today, 10:39
Joined
Jan 14, 2017
Messages
18,186
I have now .... what are your predictions before I make the results public?
 

Isaac

Lifelong Learner
Local time
Today, 03:39
Joined
Mar 14, 2017
Messages
8,738
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

AWF VIP
Local time
Today, 10:39
Joined
Jul 26, 2013
Messages
10,355
Liverpool 3 - Watford 1 ?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:39
Joined
Feb 28, 2001
Messages
27,001
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

Lifelong Learner
Local time
Today, 03:39
Joined
Mar 14, 2017
Messages
8,738
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

Lifelong Learner
Local time
Today, 03:39
Joined
Mar 14, 2017
Messages
8,738
@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

MVP / VIP
Local time
Today, 10:39
Joined
Jan 14, 2017
Messages
18,186
Sorry - you'll have to wait until after my presentation to Denver Area AUG. Its starts at 17:00 UTC
 
Last edited:

Isaac

Lifelong Learner
Local time
Today, 03:39
Joined
Mar 14, 2017
Messages
8,738
Alright - that's in 40 minutes I think. Good luck and congrats on the doing of it!
 

isladogs

MVP / VIP
Local time
Today, 10:39
Joined
Jan 14, 2017
Messages
18,186
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

 

Users who are viewing this thread

Top Bottom