2

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?

  • a few seconds for 10k rows sounds like you have trouble already. Are you sure it isn't the SELECT statement causing the issue? Also, if you run an insert statement from oracle to oracle, how long does it take? select * from dual connect by level <= 1000000 will generate 1M rows. – Michael Kutz Apr 27 '18 at 18:50
  • One standard approach to this sort of problems is to break the large batch into series of smaller ones. – dean Apr 28 '18 at 10:08
  • 1
    I second @Max Vernon. A few years ago I had a developer attempt essentially the same thing. It was taking about 5 hours to push slightly less than a million rows from MSSQL to Oracle. I traced the activity on the O. side and saw the 'row by row, slow by slow' process. I wrote a PL/SQL proc on the O side to INSERT INTO .. SELECT FROM table@mssql, and it pulled the million rows in about 3 minutes. – EdStevens Apr 28 '18 at 20:48
3

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.

  • Is there any other faster method to do it using t-sql ? – Alexandr Apr 27 '18 at 20:06
  • "Is there any other faster method to do it using t-sql ? " See my previous comment. Instead of trying to push from MSSQL, pull it from Oracle. Of course you'll need to write a PL.SQL procedure, but it will be very minimal. I knocked it out in about 15 minutes. – EdStevens Apr 29 '18 at 21:54
  • @alexandr I think you're looking for a silver bullet where there is none. There is no magic "go faster button" you can press. – Max Vernon Apr 29 '18 at 21:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.