Query Tuning

Question

Query Tuning

Viewing 15 posts - 31 through 45 (of 49 total)

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

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

SQL Kiwi (12/28/2011)
(and no-one reads these comments anyway :-))

Just the important ones. 🙂 Glad you and Hugo stopped by. I made a pretty bad mistake by missing my own NOT NULL constraint on the StartDate. Thank you both for clearing things up.

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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 2

Jeff Moden (12/28/2011)
SQL Kiwi (12/28/2011)
(and no-one reads these comments anyway :-))
Just the important ones. 🙂 Glad you and Hugo stopped by. I made a pretty bad mistake by missing my own NOT NULL constraint on the StartDate. Thanks you both.

In addition (I only realised this after going to bed yesterday), the second column would never have been used for the seek anyway, even without the ISNULL. Since the first column is on an inequality predicate, the predicate on the second column can only be used as a residual predicate anyway.

(Makes sense, actually - just try to find all people with a surname greater than Moden and a first name smaller than Jeff in the phone book - you'll have to check all the names that follow the last Moden; you can't reduce the amount of pages in the phone book to scan based on the first name).

And please don't be too sorry about your mistake. We all make mistakes. I've stopped couting mine a long time ago. What makes this community so great is that there's always someone who'll point it out to you, so we can learn. As long as we are willing to listen to each other, to take others' comments serious, making mistakes and reading the corrections is just another great way to learn!

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Anipaul SSC-Insane Points: 24681 More actions · Answer 3

Anipaul

SSC-Insane

Points: 24681

December 28, 2011 at 6:06 am

#1426035

Great Question....

GSquared SSC Guru Points: 260824 More actions · Answer 4

Jeff Moden (12/28/2011)
My most sincere apologies folks. Hugo and Paul are correct. I missed my own NOT NULL constraint on the StartDate column. I'm sending an email to SSC to modify the answers without changing the ones that got it correct. :blush:
To be sure, I made a terrible mistake. [font="Arial Black"]ISNULL isn't SARGable.[/font]
It doesn't, however, change the utility of being able to use an ISULL on the second column of a StartDate/EndDate paired lookup and still get an INDEX SEEK out of the code.

That's what I get for skimming instead of perusing. I thought that's what you meant all along, and thought "IsNulls" (plural) was just a typo.

- 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

tabinsc SSCommitted Points: 1812 More actions · Answer 5

That's what I thought. You explained it perfectly. Thanks Hugo.

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

I appreciate the explanations. There is a lot of content worth reading from this thread.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Cliff Jones SSChampion Points: 10547 More actions · Answer 7

Yes I agree, an interesting question and great explanations, thanks.

Tom257 SSChampion Points: 11543 More actions · Answer 8

My word Jeff! A lot of thinking for a single point, considering I got 7 points for counting Santa's reindeer.

Keep 'em coming. I need the mental exercise.

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

Tom Brown (12/31/2011)
My word Jeff! A lot of thinking for a single point, considering I got 7 points for counting Santa's reindeer.
Keep 'em coming. I need the mental exercise.

I may have missed it but I didn't see a place on the QOTD entry form to assign a point value. If I did, I'd likely have given it 3 points even with the copyable code. 🙂

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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 10

Jeff Moden (1/1/2012)
Tom Brown (12/31/2011)
My word Jeff! A lot of thinking for a single point, considering I got 7 points for counting Santa's reindeer.
Keep 'em coming. I need the mental exercise.
I may have missed it but I didn't see a place on the QOTD entry form to assign a point value. If I did, I'd likely have given it 3 points even with the copyable code. 🙂

You didn't miss anything, Jeff. Steve decides the amount of points to assign to each question. And he always assigns ridiculously high amounts of points to the seasonal "humor" questions.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

TomThomson SSC Guru Points: 104773 More actions · Answer 11

Hugo Kornelis (1/1/2012)
Jeff Moden (1/1/2012)
Tom Brown (12/31/2011)
My word Jeff! A lot of thinking for a single point, considering I got 7 points for counting Santa's reindeer.
Keep 'em coming. I need the mental exercise.
I may have missed it but I didn't see a place on the QOTD entry form to assign a point value. If I did, I'd likely have given it 3 points even with the copyable code. 🙂
You didn't miss anything, Jeff. Steve decides the amount of points to assign to each question. And he always assigns ridiculously high amounts of points to the seasonal "humor" questions.

Yes, he's done that again today (for a seasonal simple arithmetic question).

Tom

Ian_McCann SSCertifiable Points: 5163 More actions · Answer 12

Jeff Moden (12/28/2011)
To be sure, I made a terrible mistake. [font="Arial Black"]ISNULL isn't SARGable.[/font]

That is just a perspective thing.

Your 'terrible mistake' challenged something I believed to be true and highlighted a lots of things I didn't know.

Three days into the new year and I now have a much better understanding of how indexes are used.

I think it is going to be a good year.

Many thanks to yourself, Hugo and Paul

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

Steve updated the explanation with the lessons learned from this thread for me yesterday.

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

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

440692 I am just a number (1/3/2012)
Jeff Moden (12/28/2011)
To be sure, I made a terrible mistake. [font="Arial Black"]ISNULL isn't SARGable.[/font]
That is just a perspective thing.
Your 'terrible mistake' challenged something I believed to be true and highlighted a lots of things I didn't know.
Three days into the new year and I now have a much better understanding of how indexes are used.
I think it is going to be a good year.
Many thanks to yourself, Hugo and Paul

Thank you for the kind words. And, I agree... once again, the discussions are what really paid off on these fine forums.

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

Sergiy SSC Guru Points: 110208 More actions · Answer 15

Explanation:
Nothing is stopping an INDEX SEEK from being used and answer "A" is the correct answer.

Actually something does.

It's named "bookmark lookup".

_____________
Code for TallyGenerator