|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/14/2006 10:03:00 PM
Posts: 1,
Visits: 1
|
|
Hi All,
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
Step 1)
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
after this
Step 2)
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?
Thanks, Ajay
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 8/7/2007 2:20:00 PM
Posts: 40,
Visits: 1
|
|
The left-outrr join approach should be faster since you will avoid the transient logging that occurs for the update in step 1 of the original approach.
Make the PI of both table A and C the same as the joined columns (col1/cola, col2/colb) to keep the join amp local.
Also, it db2.tableA has the same PI, the insert into that table will laso be amp local.
|
|
|
|