Query Tuning

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Jeff Moden » Query Tuning

49 posts, Page 1 of 5

1 2 3 4 5 »»»

Query Tuning

Rate Topic

Display Mode

Topic Options

Author

Message

Jeff Moden

Posted Monday, December 26, 2011 10:16 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:35 PM
Points: 32,930, Visits: 26,817

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1226743

440692 I am just a number

Posted Tuesday, December 27, 2011 1:23 AM

SSCommitted

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 3:46 AM
Points: 1,631, Visits: 2,031

Good question and not the answer I was expecting.
Could you expand on the explanation for option E

Post #1226796

Christian Buettner-167247

Posted Tuesday, December 27, 2011 1:57 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 6:12 AM
Points: 2,526, Visits: 3,620

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 Sad

Best Regards,
Chris Büttner

Post #1226803

free_mascot

Posted Tuesday, December 27, 2011 3:36 AM

SSCommitted

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 3:39 AM
Points: 1,768, Visits: 1,312

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

Diogy

Posted Tuesday, December 27, 2011 3:39 AM

SSC 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

L' Eomot Inversé

Posted Tuesday, December 27, 2011 5:48 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 11:54 AM
Points: 7,112, Visits: 7,188

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
Is minic a gheibheann béal oscailte dorn dúnta.
Is minig a cheapas beul fosgailte dòrn dùinte.

Post #1226859

Thomas Abraham

Posted Tuesday, December 27, 2011 5:50 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:58 AM
Points: 1,152, Visits: 1,457

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.

Post #1226861

Britt Cluff

Posted Tuesday, December 27, 2011 6:09 AM

SSCommitted

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 8:17 PM
Points: 1,558, Visits: 247

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

http://brittcluff.blogspot.com/

Post #1226868

Chris Umbaugh

Posted Tuesday, December 27, 2011 6:34 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, September 24, 2012 10:53 AM
Points: 207, Visits: 216

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 @chrisumbaugh

Post #1226878

tabinsc

Posted Tuesday, December 27, 2011 7:02 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 7:28 AM
Points: 362, Visits: 400

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 »

49 posts, Page 1 of 5

1 2 3 4 5 »»»

Permissions