Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Query Tuning Expand / Collapse
Author
Message
Posted Monday, December 26, 2011 10:16 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:48 AM
Points: 36,759, Visits: 31,214
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1226743
Posted Tuesday, December 27, 2011 1:23 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 1:26 AM
Points: 1,657, Visits: 2,083
Good question and not the answer I was expecting.
Could you expand on the explanation for option E
Post #1226796
Posted Tuesday, December 27, 2011 1:57 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:45 AM
Points: 2,826, Visits: 3,866
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
Post #1226803
Posted Tuesday, December 27, 2011 3:36 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
Thanks Jeff, good question.



---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Post #1226819
Posted Tuesday, December 27, 2011 3:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, November 18, 2012 8:54 PM
Points: 37, Visits: 29
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.
Post #1226821
Posted Tuesday, December 27, 2011 5:48 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 1:49 PM
Points: 8,562, Visits: 9,058
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.


Tom
Post #1226859
Posted Tuesday, December 27, 2011 5:50 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:07 AM
Points: 1,765, Visits: 2,139
Nice Question! Thanks Jeff.

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


Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Post #1226861
Posted Tuesday, December 27, 2011 6:09 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:45 PM
Points: 1,589, Visits: 253
Tough question. I got it wrong because I thought it was a trick and that was too obvious.

http://brittcluff.blogspot.com/
Post #1226868
Posted Tuesday, December 27, 2011 6:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 8:57 AM
Points: 228, Visits: 236
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
Post #1226878
Posted Tuesday, December 27, 2011 7:02 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 12:43 PM
Points: 557, Visits: 726
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?
Post #1226886
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse