Insert using OPENQuery (from SQLServer into Oracle) -- slow performance

cos 21 Reputation points
2020-08-25T20:33:34.01+00:00

hi folks,
I am having a fairly poor performance inserting very little data, some 5k records of a one column, 10byte field, from SQLServer 2017 to Oracle 12:

for example:

INSERT INTO OPENQUERY([OraLinkedServer],'SELECT PilotID FROM Pilots') (PilotID)
select top 10000 PilotID from Pilots_sqlserver

so basically inserting 5k records into Oracle, from SQL Server, takes some 4 minutes.
This is very little data, was wondering if there are any LinkedServer connectivity properties that can be tweaked to improve the insert. (I cannot use SSIS)

many thanks for any thoughts,

Cos

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,588 questions
No comments
{count} votes

4 answers

Sort by: Most helpful
  1. Erland Sommarskog 68,536 Reputation points MVP
    2020-08-25T21:26:46.55+00:00

    Have you looked at the query plan?

    Did you try to use four-part notation:

    INSERT OraLinkedServer.catalog.sch.Pilots(PilotID)
    select top 10000 PilotID from Pilots_sqlserver

    Personally, I would look for alternatives, Either take the data over a client, or at least use the corresponding to a linked server on the Oracle side. (I assume they have something, but I don't know Oracle.)

    No comments

  2. CathyJi-MSFT 20,671 Reputation points Microsoft Employee
    2020-08-26T03:16:48.273+00:00

    Hi ,

    Please check if this link could help you?

    Best regards,
    Cathy

    No comments

  3. cos 21 Reputation points
    2020-08-26T19:42:04.653+00:00

    hi folks, I tried the four part insert, it was just as slow, because on the remote Oracle side apparently it does a commit after every inserted record.

    however, it was amazingly fast, when I converted the many insert statements, added them to an xml string, converted that to varchar(max) and pushed the entire long statement within a transactional BEGIN/END statement onto Oracle -- huge speed gain, some 10k records inserted within about 15 seconds.


  4. Cheong00 3,421 Reputation points
    2021-03-03T10:38:28.353+00:00

    If you need to sync huge amount of data some the XML based solution no longer fits, consider creating pipelined table function on Oracle side for your select query and use that for [insert into...select...] statement and see if it helps.

    See this and this for more information on why this kind of operation is so slow and some example query to fix that.

    No comments