I used your suggested way but it still took 20 minutes on a server with minimal activity. It completed in 10 seconds when I used #temptable which was created based on where condition (select kw_id into #temp from triton_ss..DW_KWDM_KW_GRP_MAP where KW_GRP_ID = 6197)

select distinct a.kw_id

from StageDB.dbo.tt_wf_49b955a9_gtsang_2BC a inner join triton_ss..DW_KWDM_KW_GRP_MAP b on

a.kw_id <> b.kw_id and b.KW_GRP_ID = 6197