Conditions with OR often gives the optimizer a head, and you often get better results if you use UNION, even if this means that you will need to repeat the rest of the query.
However, a condition like:
@start_date between A.from_date and A.to_date
is always problematic, even if there are indexes on from_date and to_date. Presumably the condition identifies a fairly small window, but the optimizer does not know this, and does not have any great way of checking this. So it will start on one of the indexes and read all rows in that direction. But the best index is probably on (from_date) INCLUDE (to_date).
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
No comments:
Post a Comment