Sql-server – Most efficient way to insert data to MS SQL table from OpenQuery on DB2 linked server

db2linked-serversql server

after much researching and experimenting, I figured I would make an attempt to get some expert advice.

I am maintaining a stored procedure that is executed as a job. It creates a table with data nicely defined. It then uses an OpenQuery to collect some data and insert it into the table.

Example:

insert into LOCAL_TABLE 
select * 
from OPENQUERY(linked_server, 'select * from linktable')

The result of the openquery is 2.5 million rows. This takes roughly 2 hours to complete.

The DB server is an iSeries so I ran the query with Visual Explain and the query completes in about 1 second. I am running the query from MS SQL Server 2016. Both servers sit right next to each other connected through a gigabit switch.

I am trying to identify the bottleneck here and I believe it is the INSERT.

I have read about BULK INSERT and it would appear I cannot use BULK INSERT with an OpenQuery.

I have read about OPENROWSET(BULK… ) but I don't think I can use a linked server with this. I have to maintain using the linked server so that if the server changes the code does not need to be updated.

Finally, running this as SELECT * INTO may increase performance, but then the data types would be defined by the result set. I suppose I could work with it from there once it's local but I'd like to confirm this is worth the effort before embarking on it.

Any recommendations as to how I can improve performance here?

Best Answer

I ran the query with Visual Explain and the query completes in about 1 second.

Then it probably didn't fetch 2.5 million rows, which is likely your bottleneck.

I am trying to identify the bottleneck here and I believe it is the INSERT.

Then try just running

select * 
from OPENQUERY(linked_server, 'select * from linktable')

To eliminate the INSERT.

running this as SELECT * INTO may increase performance

Test that too. And test INSERT INTO with a Temp Table.

Using a 4-part name should not improve performance.

Related Question