SQL Server2008 INNER JOIN practices in multiple ways

These days of study, only to find that the SQL originally was so not understanding. Have always thought that they can easily deal with a variety of complex SQL queries, but once mentioned efficiency, may be more dumbfounded, and sometimes complain that the client server is not working.

As for the inner join three ways: Loop,merge, hash, there are many articles in the garden, say to see many times than the actual operation of their own. Let's try it today. Note: There are about 46,000 rows of data in the T (4) table, and about 41,000 rows of data in the T (1) Table

First Type: Merge

DECLARE @begin datetimedeclare @end datetimeset @begin = GETDATE ()
SET STATISTICS IO onSelect * fromdbo. [T (4)] inner merge join dbo. [T (1)] ON dbo. [T (4)].keyword0 = dbo. [T (1)].keyword0
Set STATISTICS IO offset @end = GETDATE () print DateDiff (millisecond, @begin, @end)

The second type: hash

Declare @begin datetimedeclare @end datetimeset @begin = GETDATE ()
SET STATISTICS IO onSelect * fromdbo. [T (4)] inner hash join dbo. [T (1)] ON dbo. [T (4)].keyword0 = dbo. [T (1)].keyword0
Set STATISTICS IO offset @end = GETDATE () print DateDiff (millisecond, @begin, @end)

The results of the two SQL executions are shown separately:

(239657 rows affected)
Table ' T (4) '. Scan Count 10, logical read 3,236 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' T (1) '. Scan Count 10, logical read 3,064 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' worktable '. Scan Count 12, logical read 430 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Time: 22006

(239657 rows affected)
Table ' T (4) '. Scan Count 10, logical read 3,236 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' T (1) '. Scan Count 10, logical read 3,064 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' worktable '. Scan count 0, logical read 0 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Time: 17026

From the above can be seen, the merge is more than the hash of the operation, that is, the operation of the worktable, more time-consuming should be consumed here.

The CPU time is as follows:

(239657 rows affected)

SQL Server Execution Time:
CPU time = 12763 milliseconds, elapsed time = 24520 milliseconds.

(239657 rows affected)

SQL Server Execution Time:
CPU time = 11702 milliseconds, elapsed time = 17587 milliseconds.

I wanted to look at the memory overhead, and I didn't know what a good way to look at the memory overhead. We'll be here for the time being today.

SQL Server2008 INNER JOIN practices in multiple ways