Need help re-organizing query - if possible - so it is more efficient

Question

Need help re-organizing query - if possible - so it is more efficient

Viewing 6 posts - 31 through 35 (of 35 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 1

Can't tell from your last post if your serious or not so I'll assume you're serious. 🙂

It's a clever bit of code that Peso wrote. Use of the "Dash Dash function" will autmatically make it super easy for everyone who has to read the code in the future...

GROUP BY coID

HAVING MAX(CASE WHEN mcT = 'Y' THEN 1 ELSE 0 END) = 0 --No row in a group has an mcT = 'Y'

😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 2

Jeff Moden (9/7/2008)
Can't tell from your last post if your serious or not so I'll assume you're serious. 🙂
It's a clever bit of code that Peso wrote. Use of the "Dash Dash function" will autmatically make it super easy for everyone who has to read the code in the future...
GROUP BY coID
HAVING MAX(CASE WHEN mcT = 'Y' THEN 1 ELSE 0 END) = 0 --No row in a group has an mcT = 'Y'
😛

Thanks Jeff,

This is exactly where I'm stuck, understanding how the left join of the original query and this last statement are equivalent.

I've always thought of a left join as way of pulling ALL records of 2 tables regardless of whether the table on the right has NULLS on the join column. Now in this case, the table on the right is defined using an extra criterion (mcT='Y'). So my confusion is this: why is the negation of this being used in Peso's query?

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 3

Because you want to filter out ALL records for same coID group if ANY of the records has an mCT = 'Y' value.

N 56°04'39.16"
E 12°55'05.25"

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 4

Peso (9/9/2008)
Because you want to filter out ALL records for same coID group if ANY of the records has an mCT = 'Y' value.

Is that what the original query with the LEFT JOIN was doing?

I thought in the original query, records with mCT='Y' would also be included in the resultset.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 5

Then you were wrong this time, I am sorry to say.

Read the query.

N 56°04'39.16"
E 12°55'05.25"

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 6

Peso (9/9/2008)
Then you were wrong this time, I am sorry to say.
Read the query.

I've been able to convince myself your query is right, based on test samples I've run.

However, my thick brain still cannot comprehend the fundamentals of how this works.

I'll get it eventually, I think... :w00t:

Thanks again!

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]