Wants your ideas on update vs Left Outer Join wrt Performance
I have 2 tables Table A and Table C in two databases.
Database 1:- Table_A (daily Full refresh)
Database 2: Table_A (Incremental Loading) and Table_C
So total 3 tables
table_A having col 1 , col 2 and col 3 table_C having col a , col b and col c
Currentlty I am doing
Update database1.table_A A, database2. table_C C set A.col3 = C.col c where A.col1 = C.col a and A.col2 = C.col b
insert into database2.table_A as select * from database1.table_A
But updates are taking really very longtime.. I am getting almost 10 Million records every day for database1.table_A and also very huge number records in database2.table_C as well (approx 40 million) . So step 1 taking much time.
So I am thinking of using following SQL to combine step 1 and stpe 2 and removing update
insert into database2.table_A ( col 1, col 2, col 3 ) select (A.col 1, A.col 2, C.col c ) from database1.table_A A LEFT OUTER JOIN database2.table_C C ON A.col1 = C.col a and A.col2 = C.col b;
Is this OK? or any other ideas to improve performance?