Best practise to detect changes between two tables
893411Dec 3 2011 — edited Dec 4 2011Hi,
I try to write a query, that shows me the differences between a table in my DWH and the table in the source system. It should show me new, deleted and updated rows.
My approach is to do a full outer join based on the key and then check if any of the columns changed (source.A!=DWH.A or Source.B!=DWH.B, etc.) to get the updated rows.
My problem is now that my table has millions of rows und more than 100 columns (number, nvarchar, etc.). So the query takes hours.
Is there any best practise solution to optimize that query, by rewriting it, setting indexes or using hash code? I played around with hash code, but it wasn't really faster.
(BTW: CDC, etc are not allowed)
Thanks for any ideas!