THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Page Free Space

See also my SQL Server technical articles on SQLperformance.com

Join Performance, Implicit Conversions, and Residuals

Published Tuesday, July 19, 2011 1:17 AM by Paul White

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

GrumpyOldDBA said:

OK I'm interested in the not null aspect. I'm aware of the size/performance issue when indexed columns are nullable ( I dislike nulls generally anyway! ) but now in joins - I am intrigued.

July 18, 2011 8:07 AM
 

Rob Farley said:

When I saw the title, I was hoping you were going to use the word "Residualiciousness", but you didn't. I'm not going to be doing too much on implicit conversions in my PASS talk, but I like the similarity in titles anyway. :)

Nice post.

Rob

July 18, 2011 8:44 AM
 

jamiet said:

Great post Paul. Here is some more advice, use datadude to write your code and turn on code analysis - it checks for sub-optimal code and may flag code that exhibits one of the issues you talk about here:

http://msdn.microsoft.com/en-us/library/dd193267.aspx

July 18, 2011 9:00 AM
 

Klaus Aschenbrenner said:

Hi Paul,

Really a nice posting :-)

2 questions:

1. What is a Probe Residual?

2. To which family belongs the REAL data type?

Thanks

-Klaus

July 18, 2011 3:00 PM
 

Paul White said:

Hi Rob,

Doh!  I was thinking while writing this that I should link to your previous post on 'residualiciousness' but forgot.  Corrected now, thanks for your comment.

Paul

July 18, 2011 6:17 PM
 

Paul White said:

Hi Jamie (and Colin),

Yes that's a good tip - I'm not too familiar with DataDude myself, but if it draws attention to common errors like the one shown in the link you gave, I'm all for it!

In this particular case, we would still need to go back and change the table definition to include NOT NULL as a constraint.  You'd think that adding a WHERE col1 IS NOT NULL condition to the WHERE clause would have the same effect - and logically it does - but the Query Optimizer is not yet smart enough to take advantage, and the residual predicate remains.

Paul

July 18, 2011 6:21 PM
 

Paul White said:

Hi Klaus,

Take a look at the link I have now added to one of Rob Farley's previous posts.  In essence, you can think of a probe residual as an extra condition that is evaluated after a join match is found.  IIRC Rob likens it to a residual predicate on an Index Seek - where the residual is applied as an extra condition to the rows qualified by the seeking condition(s).

REAL is a 4-byte floating point number.  I provided a link to the BOL topic in the text, but here it is again:

http://msdn.microsoft.com/en-us/library/ms173773.aspx

I chose REAL because it is the same size as an INTEGER.

Paul

July 18, 2011 6:25 PM
 

Greg Linwood said:

I agree this is a well written post Paul.

Your general advice for best join performance certainly make sense, but would be difficult for developers to follow post-design other than choosing to do explicit conversions.

This message really needs to reach DB modellers so they choose appropriate types / use surrogate keys (single column joins) so I hope there are few modellers reading your blog..

July 19, 2011 1:06 AM
 

Paul White said:

Klaus,

I just realized is misinterpreted your question about REAL, sorry about that, of course you know what the REAL data type is!

Types other than the ones I listed in 'families' stand alone, and always result in a CONVERT_IMPLICIT when compared with another type.  This is a little surprising - we might have expected REAL and FLOAT to share a 'family', but they do not.

Paul

July 19, 2011 2:54 AM
 

jamiet said:

"I'm not too familiar with DataDude myself, but if it draws attention to common errors like the one shown in the link you gave, I'm all for it!"

Don't worry, its all in Juneau too :)

July 19, 2011 4:09 AM
 

Davide Mauri said:

@Paul: that's why database should support custom types so that one can declare, for example, two new types, say TableAId and TableBId, both inherited from a base type (int, for example).

When someone creates a table, he will use the proper defined datatype to store that table id. Now, when you try to join two tables and you'll find yourself joining on TableAId = TableBId, the compiler can raise an error at "compile" time, even before running the query, cause you're trying to join two differnt things (the usual apples and pears...). If such equality (TableAId = TableBId) must work, the DB developer should "overload" the cast operator in order to explain how to go from one type to another.

Yes, as you may have noticed I'm asking for a full support of a full Type System :)

July 19, 2011 4:11 PM
 

Paul White said:

Hi Davide (and Greg),

I couldn't agree more.

Paul

July 19, 2011 8:46 PM
 

Paul White: Page Free Space said:

Most people know that a LIKE predicate with only a trailing wildcard can usually use an index seek: SELECT

January 17, 2012 8:57 AM
 

Alec said:

Is there no hit for a hidden conversion from int to smallint for example?  It wasn't in your test cases.

December 27, 2012 11:42 AM
 

Paul White said:

Alec,

Yes, there is a probe residual and a hidden implicit conversion. I didn't include smallint in the test script because it has too small a range for the numbers I used.

Paul

December 27, 2012 6:46 PM
 

Paul White: Page Free Space said:

This is a post for T-SQL Tuesday #43 hosted by my good friend Rob Farley . The topic this month is Plan

June 11, 2013 5:00 AM
 

Renato said:

Thanks!

Nice posting.

June 22, 2016 12:45 PM

Leave a Comment

(required) 
(required) 
Submit
Privacy Statement