Performance issues when loading a text file.

Question

Performance issues when loading a text file.

Kenneth.Fisher

SSC-Insane

Points: 20396
More actions
April 1, 2008 at 2:24 pm

#154246

I'm trying to load a series of large text files using SSIS. The largest of them is around 30 million rows. The average will probably be 2-3 million.
I'm loading them into tables that are aprox 30 times the size of the file. So on average 70 million or so rows.
My first attempt was to do a lookup on the parent table (to test for foreign keys) and a lookup on the table (to test for insert/update) then a conditional split to decide which rows to insert and which rows update. And of course which rows to throw into the error bin.
Unfortunatly this took aprox. 3 days. Which in this case is unacceptable.
Does anyone have any suggestions of a faster way to do this?
Thanks
Kenneth Fisher
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

Viewing 4 posts - 1 through 3 (of 3 total)

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

VALEK SSCarpal Tunnel Points: 4206 More actions · Answer 1

Kenneth Fisher (4/1/2008)
I'm trying to load a series of large text files using SSIS. The largest of them is around 30 million rows. The average will probably be 2-3 million.
I'm loading them into tables that are aprox 30 times the size of the file. So on average 70 million or so rows.
My first attempt was to do a lookup on the parent table (to test for foreign keys) and a lookup on the table (to test for insert/update) then a conditional split to decide which rows to insert and which rows update. And of course which rows to throw into the error bin.
Unfortunatly this took aprox. 3 days. Which in this case is unacceptable.
Does anyone have any suggestions of a faster way to do this?
Thanks
Kenneth Fisher

Have a look at the destination element in your dataflow block.

There is Data Access Mode there, which should ideally be on of the two "* - fast load"

If not, it is snail-slow, otherwise it is sky-rocket.

M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 2

I'm using the fast load as it happens. The slow part is the lookup.

Thanks though

Kenneth

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

VALEK SSCarpal Tunnel Points: 4206 More actions · Answer 3

Kenneth Fisher (4/2/2008)
I'm using the fast load as it happens. The slow part is the lookup.
Thanks though
Kenneth

Load the whole lot and then do the insert/overwrite trick.

This will be much faster, but will waste DB storage resources

M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM