I have query similar to the one below, it joins two tables by field which can have NULL value. The result matchs the data if both tables have the same data or consider the condition as optional.
SELECT B.X, A.Y FROM tableA A
INNER JOIN tableB B ON
ISNULL(B.level5, A.f05) = A.f05
AND ISNULL(B.level6, A.f06) = A.f06
The best index for table B I could think of is such which
CREATE NONCLUSTERED INDEX [Index_On_Table_B] ON B
( X ASC ) INCLUDE (level5, level6)
It helps a bit and from execution plan I can see the index is used but 90 % of the cost is spent in Nested Loops (Inner join).
Is there any way how to get this query working faster?
Best Answer
Any time you apply a function to a column in a query, you prevent SQL Server from being able to use an index on that column. Also, it may often not be possible for SQL Server to use one of its more powerful join operations: merge and hash joins, which require equijoins - a simple columnA = columnB. For efficient queries, as many terms in the
WHERE
clause, andON
clauses, as possible should be sargable.I think what you actually wanted is
Then I would expect that a non-clustered index on level5, level6 would do the job:
All your current index is doing is storing just the data from the columns in this query, which will cause less I/O than scanning tableB itself, assuming that there are other columns in tableB.