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