Extermely slow loading table from DB2 Linked Server

Question

Extermely slow loading table from DB2 Linked Server

Welsh Corgi

SSC Guru

Points: 116520
More actions
March 8, 2012 at 9:48 am

#265680

I'm loading a SQL Server Table from an AS400 DB2 Remote Database.
The table has over 13 million records and it is extremely slow.
I wanted to test it with the Linked Server before I tried SSIS because we just increase our bandwidth for the connection to the AS400.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Linked Servers are not intended for that kind of stuff. OLEDB has a great overhead and is not the ideal tool for massive data movement.

I would suggest trying with SSIS.

-- Gianluca Sartori

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

I had suggested SSIS and to stay away from Linked Server a week ago yesterday.

On Thursday, I temporary cut his permissions and created an SSIS Package.

The Linked Server Load did not finish after 14.5 Hours.

My SSIS Package completed in 1 hour 21 Minutes.

I had dropped the indexes prior and recreated after the load completed.

It took 10 minutes to recreate the Indexes.

He was trying to load that table for a week.

Case closed.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Great, glad you solved your issue.

-- Gianluca Sartori

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

Have you ever used SSIS for an incremental load into SQL Server with a data source other than SQL Server?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

No, I'm sorry.

Are you facing some issues?

-- Gianluca Sartori

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

I'm just trying to figure out the best way to do this.

Since the Data is not SQL Server, DB2 in this case, I can't use a merge or OUTER JOIN or CDC.

Most of the DB2 tables do not have DateTime Stamps.

I do not have a long enough of a Window to load the entire tables.

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

HowardW One Orange Chip Points: 29109 More actions · Answer 7

You could use one of the 3rd party SCD merge components[/url], but I'd tend to just insert everything into a staging table first, then join that to the main table in an execute SQL task

spaghettidba SSC Guru Points: 105732 More actions · Answer 8

HowardW (3/12/2012)
You could use one of the 3rd party SCD merge components[/url], but I'd tend to just insert everything into a staging table first, then join that to the main table in an execute SQL task

Agreed.

Many ETL / ELT suites include a native log reader that implements CDC on the source (ODI, Talend etc.) to cut down the number of rows to transfer. Other than that, you would end up using triggers, which could rapidly turn into a nightmare.

-- Gianluca Sartori

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

Thanks those 3rd party controls look nice but I was trying to get around not having to purchase it.

I have too much data and I doubt that I could get it to finished the load within the window.

I have moved this thread to a different topic at the following location since it is different that the original problem:

[/url]http://www.sqlservercentral.com/Forums/Topic1265061-364-1.aspx[/url]

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

spaghettidba SSC Guru Points: 105732 More actions · Answer 10

Welsh Corgi (3/12/2012)
Thanks those 3rd party controls look nice but I was trying to get around not having to purchase it.

Talend has a free community edition and a DB2 journal readed.

I don't remember if the journal reader is part of the community edition, though.

-- Gianluca Sartori

HowardW One Orange Chip Points: 29109 More actions · Answer 11

Pragmatic Works do do a packaged/supported version that's paid for, but the one I linked to is open source/free.

The problem is that most of the hit of this update won't be in the writing to SQL Server. If you don't have any timestamps/ways to filter down the data at the source, you're always going to have to scan the whole dataset and pull it across the network. I'd try to get some changes made on the source if you need to make this run really quickly...

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 12

Unfortunately we do not hist this system. It is licensed to it and it is going away but not before I start performing the incremental loads.

So changing the source with date/time stamps is not going to happen.

Thanks for all of the help everyone.:-)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Extermely slow loading table from DB2 Linked Server

Cookies on SQLServerCentral