CODEWITHSUNDEEP
sql-serverssislookup
Serdia
Serdia

Reputation: 4418

Why adding another LOOKUP transformation slows down performance significantly SSIS

I have a simple SSIS package that transfer data between source and destination from one server to another.

If its new records - it inserts, otherwise it checks HashByteValue column and if it different its update record.

Table contains approx 1.5 million rows, and updates around 50 columns.

When I start debug the package, for around 2 minutes nothing happens, I cant even see the green check-mark. After that I can see data starts flowing through, but sometimes it stops, then flowing again, then stops again and so on.

The whole package looks like this:

enter image description here

But if I do just INSERT part (without update) then it works perfectly, 1 min and all 1.5 million records in a destination table.

enter image description here

So why adding another LOOKUP transformation to the package that updates records slows down performance so significantly. Is it something to do with memory? I am using FULL CACHE option in both lookups.

what would be the way to increase performance?

Can the reason be in Auto Growth File size:

enter image description here

Upvotes: 0

Views: 933

Answers (2)

Gordon Bell
Gordon Bell

Reputation: 13633

Besides changing AutoGrowth size to 100MB, your Database Log file is 29GB. That means you most likely are not doing Transaction Log backups.

If you're not, and only do Full Backups nightly or periodically. Change the Recovery Model of your Database from Full to Simple.

Database Properties > Options > Recovery Model

Then Shrink your Log file down to 100MB using:

DBCC SHRINKFILE(Catalytic_Log, 100)

Upvotes: 2

asemprini87
asemprini87

Reputation: 347

I don't think that your problem is in the lookup. The OLE DB Command is realy slow on SSIS and I don't think it is meant for a massive update of rows. Look at this answer in the MSDN: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4f1a62e2-50c7-4d22-9ce9-a9b3d12fd7ce/improve-data-load-perfomance-in-oledb-command?forum=sqlintegrationservices

To verify that the error is not the lookup, try disabling the "OLE DB Command" and rerun the process and see how long it takes.

In my personal experience it is always better to create a Stored procedure to do the whole "dataflow" when you have to update or insert based on certain conditions. To do that you would need a Staging table and a Destination table (where you are going to load the transformed data).

Hope it helps.

Upvotes: 2

Related Questions