I have two large tables containing a common value
ContactReference, and two
DateTime fields (amongst a lot of other fields). One table for
Contact and one for
I am trying to find an efficient query to find, for a
ContactReference AND between two dates, the
MAX(date) when one of the values was changed.
i.e. Regardless whether it was the
ContactAddress that changed between the dates, when did that happen?
I have tried:
SELECT c.ContactReference AS ContactReference , c.EditDate AS EventDateTime , ROW_NUMBER() OVER ( PARTITION BY c.ContactReference ORDER BY c.EditDate DESC ) AS rn FROM dbo.Contact AS c
SELECT * FROM contactCTE c WHERE c.rn = 1 UNION ALL SELECT * FROM contactAddressCTE ca WHERE ca.rn = 1
With CTEs and without.
BETWEEN predicate at the end, in the first select, after the union, etc. etc.
ROW_NUMBER was the worst at 21 seconds.
ALL of the others come back in 7 seconds...and the execution plan is pretty similar whatever I try (CTE is marginally less expensive).
The above are examples of what I have tried, not the full SQL which would be pages of "tried this, tried that".
Any ideas how I can speed this up?
There are indexes on the "key" value (
ContactReference) in both tables. I thought of a composite index on
(ContactReference, EditDate), but there are around 2mn rows in
Contact and 25mn in
Address and they both get edited regularly (by external processes) and I'm concerned about the size and performance hit of maintaining both indexes.