Problem with data insertion in Oracle table from SQL Server table

Question

We have SQL server and Oracle server, and have configured a linked server from SQL Server to Oracle.

I'm trying to insert 10,000 rows from SQL Server into a table in Oracle.

When I insert 100,000 rows it takes 2 minutes, when I try to insert 1 million rows it can run for more than 10 hours and at the end of this I need to stop the insert because nothing is happening.

Could you please give me answer why it takes so long for 1 million rows? It should take approximately 1 hour if it runs the same speed as 10k rows.

---

What I'm trying to do:

INSERT INTO [linksrvname]..[schemaname].[oracletablename ] (
  column1,
  column2
)
SELECT top 10000
  column1,
  column2
FROM sqlservertable 
;

...completed in few seconds. After that I have deleted all rows from the Oracle table. When I'm trying to insert 100,000 it is completed in few minutes. When I'm trying to insert 500,000 it is running for a few hours now; SQL Server hangs with OLEDB wait type. Unfortunately, I don't have access to the Oracle server.

What could the problem be?

Answer 1

SQL Server inserts across linked servers use row-by-row insert statements. If you run this:

INSERT INTO [oracledb].database.schema.table
SELECT *
FROM dbo.LocalTable

SQL Server will use a cursor to fetch each individual row from dbo.LocalTable and run an INSERT INTO ... statement against the linked server for each row.

Essentially, the insert gets turned into this:

DECLARE cur CURSOR
FOR 
SELECT *
FROM dbo.LocalTable;
OPEN cur;
FETCH NEXT FROM cur INTO @vars...
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO [oracledb].database.schema.table (cols...)
    VALUES (@vals);
    FETCH NEXT FROM cur INTO @vars...
END
CLOSE cur;
DEALLOCATE cur;

You'll find that as the number of rows increases, the time required to run the insert will increase linearly. This increases the chance that blocking on the target side may interfere with the inserts. The OLEDB wait type indicates SQL Server is waiting for code in the OleDB provider to complete. It occurs when SQL Server calls the SQL Server Native Client OLE DB Provider. This wait type is not used for synchronization. Instead, it indicates the duration of calls to the OLE DB provider.

The "best practice" for loading data from SQL Server to a link-server target is to run that either from the target side, or via a bulk-load app designed for that purpose.

You mentioned in comments that you do not have access to the Oracle server. Without being able to see what's happening at the Oracle server side, it will be difficult to diagnose where the problem lies.