Most efficient way to insert data to MS SQL table from OpenQuery on DB2 linked server?

Question

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?

Answer 1

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.

Answer 2

Instead of OPENQUERY, try using just a 4-part name...

insert into local_table
  select * from linked_server.MYIBMI.MYLIB.MYTBL

The only reason to use OPENQUERY would be if there was a WHERE clause on the select. OPENQUERY sends the query to the IBM i and just pulls back the results. Using a 4-part name, the entire table is pulled back and the where performed locally. Since you don't have a WHERE, no harm in just using the 4-part name.

Edit
David's correct in that the VE time probably isn't for all rows. By default VE optimizes for *FIRSTIO,meaning give me the first page of data as fast as you can; change that to *ALLIO and then save the results to a .CSV file. That will give you a better idea of how long it'd take to transfer all the rows.

After a bit of a refresher on MS SQL Server....and finding this paper, The Data Loading Performance Guide for MS SQL 2008. It appears your fastest method with just MS SQL and DB2 for i would be to export the data as CSV and then use one the available bulk insert methods.

Optionally you could make use of MS SQL Server Integration Services (SSIS) or another ETL tool, or even write your own bulk loading application.

Answer 3

Based on advice from @Charles and @David Browne - Microsoft, I have created and benchmarked four methods of creating and populating a table with the results from a DB2 OpenQuery.

As part of my tests, I did not attempt to export to CSV. After reading the link from @Charles, it would appear the SSIS from OLE DB Destination method would be the fastest method using Integration Services. If anyone disagrees, feel free to correct me.

To refresh, my goal is to transfer 2.5 million records from a DB2 database OpenQuery and insert the results into a table on MS Sql Server. My benchmarks were performed on July 3, a day which many people in the company took a day off so I expect server load would not have too much of an effect. Finally, I will mention that I am using the bulk-logged transaction log method for this database.

Results of my Benchmark Test:

1. INSERT INTO TABLE FROM OPENQUERY Method: 00:52:00
2. INSERT INTO TABLE WITH (TABLOCK) FROM OPENQUERY Method: 00:49:21
3. SSIS From OLE DB Destination Method: 00:39:34
4. SELECT * INTO TABLE FROM OPENQUERY Method: 00:46:58

It would appear using SSIS is the quickest method to bulk transfer from OpenQuery.