Table scan instead of index seeks happening when where clause filters across multiple tables in join using OR We have an application generated query using a view that has two tables joined on a LEFT OUTER join. When filtering by fields from just one table (either table) an index seek happens and it's reasonably fast. When the where clause includes conditions for fields from both tables using an OR the query plan switches to a table scan and doesn't utilize any of the indexes.
All four fields that are being filtered on are indexed on their respective tables.
Fast query plan where I filter on 3 fields from one table: https://www.brentozar.com/pastetheplan/?id=Hym_4PRSO
Slow query plan where I filter on four fields...three from one table and one from another table: https://www.brentozar.com/pastetheplan/?id=r1dVNDRHO
Ideally I would like to understand why this is happening and how to nudge the query engine to utilize all the indexes.
I considered a union but unfortunately this legacy system is using ntext values which can't be unioned. Also note the join between the two tables is one to one so I really expected the optimizer to utilize the indexes, but maybe it doesn't know that?
It looks to me like it’s doing a Scan because it may well need rows from T553 if the condition in T1011 holds. On the other hand, if any of the conditions on T553 hold, it’ll need rows from T1011.
So indexes would have to be able to handle finding rows in T553 and then pulling in the relevant rows from T1011, and also finding rows in T1011 and pulling in the relevant rows from T553. And the Query Optimizer figures that it’s probably best to get a good join happening and to filter the results.
To make it better, use a UNION of the two ways through the query. This should help it decide to do it the preferred way and you can optimise each side of the Union as you need.
Something like:
SELECT *, ROW_NUMBER....
FROM
(
SELECT *
FROM T1617
WHERE
  C1402001100 LIKE @P0
  OR C200000001 LIKE @P2
  OR C200000020 LIKE @P3
UNION
SELECT *
FROM T1617
WHERE C260100004 LIKE @P1
) t


Use SELECT CAST(ntextCol as nvarchar(max)), ... for something which can be UNIONed.
One thing to keep in mind is the UNION operator will remove dupes, not only between the two datasets, but within them as well. There are PKs in place here though, based on the objects involved in the seeks. If it did apply here, the answer would be to include something that’s unique in the view on each side of the UNION and then leave it out in the outer query.