I found out some SQL queries that make the CPU hight, tempdb usage hight and I dont' know how to solve, please comment.
SELECT DISTINCT U.DD
FROM #Upload U
JOIN #IncomingDD I ON U.DD = I.DD
WHERE U.OfferDate > I.OfferDate
AND U.EffectiveDate <= I.EffectiveDate
now the in the execution plan, what I can see the high cost is from table insert, index scan, RID lookup and index seek, so how to solve it?