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.
asked Oct 11, 2017 at 10:23 AM in Default
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:
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):
From SQL Server (insert data, this should be fast with only 145 rows to insert):
Finally, from Oracle, update from the join (I hope I have typed the Oracle flavour of the statement to update from join correctly):
Hope this helps.