Computation time to compare 2 tables _ 100k rows each _ 150 columns each _ 6.5 mln rows as a result

[MasterSonic]

Hi Guys,

I found solution for my problem on this forum before.

Having 2 tables to compare ( 2 tables _ 100k rows each _ 150 columns each )
- I just unpivoted them on key column, inner joined on key and columns name  + added  cond. column to compare columns.

Key

Column names

Value table 1

Value table 2

value table1= value table2 yes/no

Problem is 6.5 mln of rows, and merge query loading 2hrs on deskopt ( sharpoint will be worse ?!!!)

Is there any other way to compare them ? Any idea

[v-yanjiang-msft · Accepted solution]

Hi @MasterSonic ,

Sorry maybe I'm not understanding your problem correctly, if you want to compare two tables, why not simply select Merge Queries.

If we select Full Outer join kind, after expand all rows, we can see all values in a single table.

If the two tables are all have 100k rows, the merged table will have less than 200k rows, which is a lot less than 6.5 mln rows.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.