PostgreSQL DBA

no more blogging at this site as I begin learning the ropes as a new postgresql dba. cheers!

Posted in Uncategorized

Tammy

Today would have been my sister’s 43rd birthday. I love you Tammy!
Posted in Uncategorized

Time

There is an illness in the family which has been the focus of my time.
Posted in Uncategorized

Wednesday Weekly #sqlserver Links for 2011-40

Wednesday Weekly #sqlserver Links 

Grab a plate and load it up.

Current Feed Content

Posted in Uncategorized

T-SQL Tuesday #23 – Joins #tsql2sday

Joining

If you have experience with joining the military then thank you sir or ma’am but as a SQL Server DBA you are sure to have experience with joining tables using Transact-SQL. My first blog post was on this exact same subject and this post will be a condensed version of it, SQL Server Joins using Playing Cards There are three primary JOIN operators the Query Optimizer will pick from: Nested Loops, Merge Join and Hash Match.

 

Image004

Nested-Loops

Imagine you were asked to find all the Aces in a deck of cards. How would you accomplish that? You would probably flip them all over and scan across them looking for the Aces. You would likely do the same if you were told that this would repeat for three, four or five different ranks. What if you were told it would be for eleven, twelve or even all thirteen different ranks?

 

Image006

Hash Match

If you were going to match many different ranks then you would probably decide scanning would be too repetitive. Instead you might break the deck into a pile for each rank. The time it took to reorganize the cards will be made-up by not having to look at every single card when another rank is asked for. What if you were told to match all fifty-two cards one-to-one between two decks?

 

Image008

Merge Join

We need to take a small leap of faith to continue using this card matching analogy. For now image that you have the ability to perform a perfect shuffle, where exactly one card from each the left and the right stacks fall together through-out the entire shuffle. If you had to find all fifty-two card matches you would likely go with a Hash Match but now that you are bestowed with the gift of Perfect Shuffle you have a better option. You quickly sort each deck then perform a perfect shuffle and BAM! The analogy is more appreciable when the cards are already sorted or the number of them is much higher than fifty-two.

 

Image010

See Also

Physical Join Operators in SQL Server – Nested Loops – SQL Server Performance

Physical Join Operators in SQL Server – Hash Operator – SQL Server Performance

Physical Join Operators in SQL Server – Merge Operator – SQL Server Performance

Nested Loops Join – Craig Freedman’s SQL Server Blog – Site Home – MSDN Blogs

Hash Join – Craig Freedman’s SQL Server Blog – Site Home – MSDN Blogs

Merge Join – Craig Freedman’s SQL Server Blog – Site Home – MSDN Blogs

 

Image012

People

Stuart Ainsworth

Adam Machanic

Ami Levin

Craig Freedman