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

Question

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

Answer 1

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.)

Answer 2

Hi ,

Please check if this link could help you?

Best regards,
Cathy

Answer 3

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.

Answer 4

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.