hi @TimGarrod
I have attached the query plan for this query.
i was also wondering whether instead of having multiple union all and the same E1 table wouldn't it better to have those in the same join. I changed that and it got completed in a 1 sec for 144 records other took about 12 min.
SELECT [ID]
FROM ( SELECT [E1].[ID] [ID]
FROM [ODS_COMPOSE_DW].[ods].[TDWH_UDT_HUB] [E1]
WHERE [E1].[RUNNO_UPDATE] > 100
UNION ALL
SELECT [E1].[ID] [ID]
FROM [ODS_COMPOSE_DW].[ods].[TDWH_UDT_HUB] [E1]
INNER JOIN [ODS_COMPOSE_DW].[ods].[TDWH_MEMBER_HUB] [E2]
ON [E1].[MEMBER] = [E2].[ID]
AND [E2].[RUNNO_UPDATE] > 100
INNER JOIN [ODS_COMPOSE_DW].[ods].[TDWH_PRACTITIONER_HUB] [E3]
ON [E1].[PRACTITIONER] = [E3].[ID]
AND [E3].[RUNNO_UPDATE] > 100
INNER JOIN [ODS_COMPOSE_DW].[ods].[TDWH_SUPPLIER_HUB] [E4]
ON [E1].[SUPPLIER] = [E4].[ID]
AND [E4].[RUNNO_UPDATE] > 100
INNER JOIN [ODS_COMPOSE_DW].[ods].[TDWH_SUPPLIER_LOCATION_HUB] [E5]
ON [E1].[SUPPLIER_LOCATION] = [E5].[ID]
AND [E5].[RUNNO_UPDATE] > 100
INNER JOIN [ODS_COMPOSE_DW].[ods].[TDWH_AUTHORIZATION_HEADER_HUB] [E6]
ON [E1].[AUTHORIZATION_HEADER] = [E6].[ID]
AND [E6].[RUNNO_UPDATE] > 100
INNER JOIN [ODS_COMPOSE_DW].[ods].[TDWH_CLAIM_HUB] [E7]
ON [E1].[CLAIM] = [E7].[ID]
AND [E7].[RUNNO_UPDATE] > 100
WHERE NOT [E1].[RUNNO_UPDATE] > 100
) [QQ]
GROUP BY [ID]
