Tuning Indexes - Professional SQL Server 2005 Performance Tuning - page 384

Scan count 0, logical reads 1003, physical reads 0, read-ahead reads 0, lob logi-
cal reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'people'.
Scan count 1, logical reads 1793, physical reads 0, read-ahead reads 0, lob logi-
cal reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 32 ms, elapsed time = 44 ms.
(1000 row(s) affected)
Table '#girls'.
Scan count 0, logical reads 1003, physical reads 0, read-ahead reads 0, lob logi-
cal reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'people'.
Scan count 1, logical reads 1736, physical reads 0, read-ahead reads 0, lob logi-
cal reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 46 ms, elapsed time = 447 ms.
(1000 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table '#boys'.
Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logi-
cal reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table '#girls'.
Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logi-
cal reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'people'.
Scan count 2, logical reads 9, physical reads 0, read-ahead reads 0, lob logi-
cal reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'.
Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logi-
cal reads 0, lob physical reads 0, lob read-ahead reads 0.
CPU time = 78 ms, elapsed time = 494 ms.
That's reduced the CPU time for the warm run from 812 mSec to 78 mSec, although the elapsed time only
came down from around 1 sec to 500 mSec. Now see how fast it runs in a tight loop:
Married 20 people in 9860mS at a rate of 2.0284 per Second
Married 20 people in 8890mS at a rate of 2.24972 per Second