Why is SQL server not using my index? (Filtering over joined indexed views)

Question

I have two indexed views, v_First and v_Second. These views work great when I have a WHERE clause that filters only based on one of these views, however as soon as I have filter criteria based on both views I get two clustered index scans and poor performance as a result.

My query is:

SELECT * FROM dbo.v_First (NOEXPAND)
JOIN dbo.v_Second (NOEXPAND)
ON dbo.v_First.id = dbo.v_Second.id
WHERE 
dbo.v_First.Firstname = 'JUSTIN'
OR dbo.v_Second.Surname = 'JUSTIN'

If I comment out either one of the above two WHERE clauses then I get lookups and the query performs so I know that individually I have the correct indexes defined.

Why does the query not perform when filtering based on multiple indexed views, and what can I do to fix it?

(Sorry, I can't post execution plans, they are trivial anyway - just two clustered index scans on the two respective views clustered index and a merge join)

Update:

v_First columns:

• ID (bigint, clustered index)
• FirstName (varchar(254), nonclustered index)

v_Second columns:

• ID (bigint, clustered index)
• Surname (varchar(254), nonclustered index)

All of the indexes contain only the single column.

Update, the second:

I've found that if the OR clause is changed to an AND clause, the query performs fine. I've also found that if I change the query to use a UNION statement instead of a OR the query performs fine:

SELECT * FROM dbo.v_First (NOEXPAND)
JOIN dbo.v_Second (NOEXPAND)
ON dbo.v_First.ID = dbo.v_Second.ID
WHERE dbo.v_First.Firstname = 'JUSTIN'
UNION SELECT * FROM dbo.v_First (NOEXPAND)
JOIN dbo.v_Second (NOEXPAND)
ON dbo.v_First.ID = dbo.v_Second.ID
WHERE dbo.v_Second.Surname = 'JUSTIN'

As far as I'm aware these two queries should be equivalent?

Finally, I've also found that using a subquery instead also has an odd effect, the following query performs fine:

SELECT * FROM dbo.v_First (NOEXPAND)
-- JOIN dbo.v_Second (NOEXPAND)
-- ON dbo.v_First.ID = dbo.v_Second.ID
WHERE dbo.v_First.ID IN
(
      SELECT ID FROM dbo.v_Second (NOEXPAND)
      WHERE dbo.v_Second.Surname = 'JUSTIN'
)
OR dbo.v_First.Firstname = 'JUSTIN'

However if I uncomment the JOIN (so that I can obtain the columns from the second table in the results of my query) then I get a table scan on the v_Second clustered index (note however that is still better than the original query as it involves only 1 scan, instead of 2).

I'm very confused - what's going on? It seems that I can work around these issues by "refactoring" my query, however I'm concerned that I don't understand what's going on here - I'd rather avoid making changes that I don't fully understand.

Answer 1

Observations

• You have an "OR" condition which is not SARGable

• You may need to add ID to each index so it's available without scans/lookups

• I'd like to see the plans anyway. Use SET SHOWPLAN_TEXT

To answer your question, I'd probably have one index on the base table with both text columns and let it scan that. The OR does not give you many options and 2 indexed views is pointless IMHO. After your update, you have some contrived SQL constructs: do you really need 2 indexed views and a fancy derived table or UNION?