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.

more ▼

asked Oct 11, 2017 at 10:23 AM in Default

avatar image

61 2 2 7

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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;

Finally, from Oracle, update from the join (I hope I have typed the Oracle flavour of the statement to update from join correctly):

         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;

Hope this helps.


more ▼

answered Oct 11, 2017 at 01:13 PM

avatar image

20.5k 3 7 29

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 11, 2017 at 10:23 AM

Seen: 84 times

Last Updated: Oct 11, 2017 at 01:16 PM

Copyright 2018 Redgate Software. Privacy Policy