Where clause on non-clustered index Vs extra join and where clause on clustered index

Question

Hi all

I am trying to eak out a bit extra performance from some sql queries which have a where clause on a field which is a non-unique non-clustered index, it is also a foreign key in table A. That foreign key is the primary key on table B and is the clustered index.

What I'm wondering is, will I get a performance gain if I add a join from table A to table B and have the where clause on the field which is the clustered index (as opposed to the non-clustered index without the extra join)?

thanks

Answer 1

I would not think so, as adding another table would involve more reads. But you will have to test.

Answer 2

No as per Frank, this requires more page reads from Table B. You say you already have a NC Index on the Foreign Key on which the filtering is performed. You might also look at weighing up the pros and cons of clustering Table A by the FK (B's PK) - if you usually always fetch Table A by filtering on B, then this could reduce the number of pages fetched on A (but obviously, there may be other queries which could be impacted by this which could count against this)

Answer 3

To really eke out that extra bit of performance, you'd be better off making sure that your non-unique, non-clustered index is a covering index (i.e., the index includes all of the columns needed to satisfy the query) for the query you're executing. You can take advantage of the concept of included columns introduced in SQL 2005 to help you do this.

Answer 4

If you consider how the join is going to work - it's going to have to search for the FK column in table A in order to satisfy the join condition - which is the scan you seem to be trying to avoid. So I don't believe that there is any way that this could improve the performance.

Answer 5

I've made some test. I have two tables - tableA (~3000 rows) and tableB (~200 rows). both of them has column id. TableA: ID - pk, TableB: ID - fk, non-clustered index.

Select from one table:

SELECT 
    a.ID
FROM
    dbo.TableA a
WHERE 
    a.ID IN (1,5,7,9,23,45,56,546,67,32,54,676)

---

-- EXECUTION PLAN:

|--Index Seek(OBJECT:([Database].[dbo].[TableA].[IX_TableA_ID] AS [a]), SEEK:([a].[ID]=(1) OR [a].[ID]=(5)) ORDERED FORWARD)

-- STATISTICS IO:

Table 'TableA'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

---

Select using two tables (inner join)

SELECT 
    t.ID
FROM
    dbo.TableA a
INNER JOIN 
    dbo.TableB t  with(index(PK_TableBs)) ON t.ID = a.ID
WHERE 
    t.ID IN (1,5,7,9,23,45,56,546,67,32,54,676)

---

-- EXECUTION PLAN:

  |--Nested Loops(Inner Join, OUTER REFERENCES:([t].[ID]))
       |--Clustered Index Seek(OBJECT:([Database].[dbo].[TableB].[PK_TableB] AS [t]), SEEK:([t].[ID]=(1) OR [t].[ID]=(5)) ORDERED FORWARD)
       |--Index Seek(OBJECT:([Database].[dbo].[TableA].[IX_TableA_ID] AS [a]), SEEK:([a].[ID]=[Database].[dbo].[TableB].[ID] as [t].[ID]),  WHERE:([Database].[dbo].[TableA].[ID] as [a].[ID]>=(1) AND [Database].[dbo].[TableA].[ID] as [a].[ID]<=(5)) ORDERED FORWARD)

-- STATISTICS IO:

Table 'TableA'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableB'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Result:

The second query cost is 67% (relative to the batch) against to the first query (33%). Also, the second query requires more reads.

PS. This is dirty quick example, you shoud check your own.