I have a query that I'm doing some tuning on and I'm getting some odd execution plan/speed results:
Same exact query except they have a slightly different join qualifier.
CASE A:
--Using a date field (D.Date1) as the data filter
SELECT D.ID, D.Identifier1, D.Date1, D.GroupID, D.SubgroupID
FROM
Table1 D INNER JOIN
Table2 S ON D.N = S.N and D.SubgroupID = S.SubgroupID and D.Date1 BETWEEN S.Date1 and S.Date2 AND D.Date1 >= '20200101' LEFT OUTER JOIN
Table3 H ON H.SubgroupID = D.SubgroupID and H.RuleName = 'Ignore'
WHERE H.ID IS NULL
CASE B:
--Using the primary key of Table1 (D.ID) as the data filter
SELECT D.ID, D.Identifier1, D.Date1, D.GroupID, D.SubgroupID
FROM
Table1 D INNER JOIN
Table2 S ON D.N = S.N and D.SubgroupID = S.SubgroupID and D.Date1 BETWEEN S.Date1 and S.Date2 AND D.ID >= 123456 LEFT OUTER JOIN
Table3 H ON H.SubgroupID = D.SubgroupID and H.RuleName = 'Ignore'
WHERE H.ID IS NULL
When looking at the estimated execution plans, CASE A has a missing index that has a 78% impact, and it has a Clustered Index Scan on Table 1 with an 83% cost. CASE B has no missing indexes, and the Table 1 has changed to a Clustered Index Seek, and the bulk of the query cost has shifted to the same index seek on Table2 that is in both queries.
So I would assume that CASE B would be faster, but it takes 2+ hours to run. CASE A returns 10k rows in a second.
I know it's impossible to tell without detailed review of data and indexes and statistics and all sorts of other things, but my query A with the green suggested index and index scan is performing a thousand times better than query B using the primary key field and an index seek.
Any thoughts?