SQL server to Oracle Data Update performance issue.
I have a query to update data from sql server table to oracle table. SQL server table have 145 records and Oracle table have 2.5 crores records. sample Query i have used provided below. Query running continuously 30 mins.While executing update i have performed a Select query on Oracle table i have shocked already data updated (in 5 mins) but still query runnin(30 mins...). UPDATE O SET O.Column=S.Column FROM OPENQUERY([LinkedServerName], 'SELECT ID,Column FROM OracleTable ') AS O ,SQLTable S WHERE S.ID = O.ID Please suggest me to run faster.
It is possible that update from the join between Oracle and SQL Server tables is the root cause of the problem with performance. It just takes long time to update 25 million rows from the distributed join like this. If this is a one-time action then it could be better to consider the following:
1. Create table in Oracle which includes just 2 columns (to store ID and Column values from the SQL Server table).
2. **From SQL Server**: use openquery to insert the data from SQLTable to newly created Oracle table
3. **From Oracle**: update OracleTable records from the join with this newly created table
This way, the actual update statement will be running in Oracle and it will be the update from the join of 2 Oracle tables. Here are the scripts:
From Oracle (create table):
/* use whatever appropriate types/names */
create table SQLTableInOracle (ID number, Column varchar2(100))
From SQL Server (insert data, this should be fast with only 145 rows to insert):
insert openquery([LinkedServerName], 'select * from SQLTableInOracle')
select ID, Column from SQLTable;
go
Finally, from Oracle, update from the join (I hope I have typed the Oracle flavour of the statement to update from join correctly):
update
(
select
o.Column as OldColumn, s.Column as NewColumn
from OracleTable o inner join SQLTableInOracle s
on o.ID = s.ID
) t
set t.OldColumn = t.NewColumn;
commit;
Hope this helps.
Oleg
