limit how many rows need to be JOINed

 

  1: use AdventureWorks2008
  2: go 
  3: CHECKPOINT; 
  4: GO 
  5: DBCC DROPCLEANBUFFERS; 
  6: GO
  7: 
  8: select FirstName ,EmailAddress  from Person.Person a inner join Person.EmailAddress b 
  9: on a.BusinessEntityID = b.BusinessEntityID

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'EmailAddress'. Scan count 1, logical reads 186, physical reads 4, read-ahead reads 182, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Person'. Scan count 1, logical reads 105, physical reads 2, read-ahead reads 103, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 32 ms, elapsed time = 301 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

Apply limitation

  1: use AdventureWorks2008
  2: go 
  3: CHECKPOINT; 
  4: GO 
  5: DBCC DROPCLEANBUFFERS; 
  6: GO
  7: 
  8: select FirstName ,EmailAddress  from Person.Person a inner join Person.EmailAddress b 
  9: on a.BusinessEntityID = b.BusinessEntityID
 10: where FirstName = 'Maria'

Table 'EmailAddress'. Scan count 64, logical reads 295, physical reads 3, read-ahead reads 20, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Person'. Scan count 1, logical reads 105, physical reads 2, read-ahead reads 103, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 15 ms, elapsed time = 155 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

Apply the Query Hint :

  1: USE [AdventureWorks2008]
  2: GO
  3: CREATE NONCLUSTERED INDEX [Person_FirstName]
  4: ON [Person].[Person] ([FirstName])
  5: 
  6: GO
  7: 

And Check

Table 'EmailAddress'. Scan count 64, logical reads 306, physical reads 3, read-ahead reads 20, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Person'. Scan count 1, logical reads 3, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 117 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

Conclusions :
Try hardly to limit the no of joined rows in order to increase performance

Resources

http://www.sql-server-performance.com/2006/tuning-joins/

http://www.mssqltips.com/sqlservertip/1360/clearing-cache-for-sql-server-performance-testing/