Query Tuning

Question

Post reply

Query Tuning

Jeff Moden

SSC Guru

Points: 1004402
More actions
December 26, 2011 at 10:16 pm

#252604

Comments posted to this topic are about the item Query Tuning
--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)

Viewing 15 posts - 1 through 15 (of 49 total)

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

Ian_McCann SSCertifiable Points: 5163 More actions · Answer 1

Good question and not the answer I was expecting.

Could you expand on the explanation for option E

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 2

Got it wrong because I did not see that EventID was the PK.

But to be honest, i don't agree with the explanation for why the last option is not valid.

If you explicitly mention the set-up (1000 rows), I expect that the question is based on that the set-up.

And not on any possible future state.

But in the end my fault because I just did not see that PK 🙁

Best Regards,

Chris Büttner

free_mascot One Orange Chip Points: 27169 More actions · Answer 3

Thanks Jeff, good question.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Diogy SSC Journeyman Points: 97 More actions · Answer 4

I don't get it. LOL! ^^

My answer was E.

Can you help explain below statement?

"because a nullability check using ISNULL CAN be SARGable if the ISNULL is written correctly"

Probably, give us a sample of ISNULL not written correctly. ^^

As far as I know, once we use function in WHERE clause, it will be non-sargable and index seek will not be used anymore.

Thanks in advance.

TomThomson SSC Guru Points: 104773 More actions · Answer 5

Good question.

My hangover produced the wrong answer (blame isNull); must remember to drink fruit-juice and coffee before looking at QoTD the morning after a long, late, and liquid celebration. Kicked myself on seeing the answer - isNull is always eliminable by elemetary logic!

P(isNull(A, B)) == (A is Null and P(B*)) or (P(A) and not (A is Null))

(B* is either B or cast(B as A), depending on whether B and A have the same type or not)

for every predicate P. So unless either A or B is unsargable or getting B to the type of A involves the type conversion of a column (which it clearly doesn't when B is a constant) isNull(A,B) ought to be sargable.

edit: But it isn't sargable. I'm feeling stupid. :blush:

Tom

Thomas Abraham SSChampion Points: 10761 More actions · Answer 6

Nice Question! Thanks Jeff.

I'd like to add a "me too" for the NON-SARGable example of the ISNULL.

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

Britt Cluff SSCertifiable Points: 5083 More actions · Answer 7

Tough question. I got it wrong because I thought it was a trick and that was too obvious.

http://brittcluff.blogspot.com/

Chris Umbaugh Ten Centuries Points: 1334 More actions · Answer 8

Great question Jeff! I like you explanation for the last answer, option (I). I was going to be angry if that were one of the correct answers. We do not query tune in order to make fast queries over test data. We do it to insure "lights out" performance in production, to prevent contention, etc. Certainly the use of a SEEK or a SCAN does not matter in test on 1000 rows. However, I do not get paid to make fast queries in TEST.

Chris Umbaugh
Data Warehouse / Business Intelligence Consultant
twitter @ToledoSQL

tabinsc SSCommitted Points: 1812 More actions · Answer 9

Excellent question Jeff! I do have one question: If clustered index columns are auto-appended to nonclustered indexes, what if the nonclustered index is unique? Adding the clustered index column(s) to an unique nonclustered index would change the unique constraint on it, right?

Tony
------------------------------------
Are you suggesting coconuts migrate?

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 10

On my way to work... I'll have to answer your questions tonight.

--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)

GSquared SSC Guru Points: 260824 More actions · Answer 11

tabinsc (12/27/2011)
Excellent question Jeff! I do have one question: If clustered index columns are auto-appended to nonclustered indexes, what if the nonclustered index is unique? Adding the clustered index column(s) to an unique nonclustered index would change the unique constraint on it, right?

Edit (due to misread): Nope. Try it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Rose Bud Hall of Fame Points: 3147 More actions · Answer 12

There's a whole lot of learnin' to be had in this one Q and explanation. Thanks!

GSquared SSC Guru Points: 260824 More actions · Answer 13

I have to admit, I spent a couple of minutes reading and re-reading the possible answers, trying to figure some way it could possibly be something I was missing. There was no way this one could be "that simple". Finally decided to go with what I thought the first time, and it was, indeed, "that simple". (And got it right.)

First QotD I've even attempted in about a year, and only bothered because of the author. Good question, good explanations.

Jeff: Quick suggestion. The mentions of beer popsicles, dust bunnies, and flying pigs (porkchops) are fun, but they are inside jokes, and might obfuscate the otherwise great explanations to anyone not in on them.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 14

I got it right and skipped past E not because it could be SARGable, but because the index is keyed to StartDate and that IS SARGable, and I figured that the system would seek out the rows based upon that and then "scan" the index for EndDate, even if it has to look at all values in the index that meet the start date requirement (but still technically a seek). I will be curious to see how the ISNULL can be SARGable as well.