I'm beginning to watch the free 30 minute training videos on Brent Ozar's site (now Brent Ozar PLF).
Todays was entitled Let’s talk about joins by Jeremiah Peschka and served as a revision as to how sql processes joins. Obviously wqtch it yourself, I'm placing the following notes here to remind myself what they are all about...
Nested Loop Join
Reads every row from each table
Fastest for small tables
Performance governed by number of rows, gets slower as row counts increase.
Both tables must be sorted (by the join key) for this to occur.
Performance governed by number of pages retrieved.
To optimise, look at indexes, memory & disk speed.
Use Hash functions
Hash function applied to join key (turning variable length keys to fixed)
Internally SQL sorts works on small ‘buckets’ of data when comparing data for joins.
Best for large tables, tempdb is used though if memory spills to disk.