SQL Server 2008 SSIS Update Command Performance Issue

60 pts.
Tags:
SQL Query Analyzer
SQL Server
SQL Server 2008
SQL Server 2008 R2
SSIS
Hello, In SQL Server 2008 R2, I have created an SSIS package to pull from an ODS and feed a Data Mart. To build the OLAP cube, I am using identity keys as the primary keys to the tables. TableA has 4,000,000 rows and I am updating each row with a foreign key from TableB with 500,000 rows using a where clause. The data flow component selects the foreign key and where clause parameters (numbering 3) from TableB which are passed to a sql component which does the update of TableA. With this many rows in TableB, it takes over 48 hours to update TableA (and another 4 million row table) with the foreign key. This is not acceptable for a number of reasons. Another table with 30 rows takes about 2 minutes to update TableA with foreign keys.
Update TableA Set FK1ID=? Where Parm1=? And Parm2=? And Parm3=?
Has anyone ever had this issue and come up with a resolution? Or does anyone have any tips for improving the update of TableA? I've used Query Analyzer to create an index but that has not helped. Thanks.

Software/Hardware used:
SQL Server 2008 R2

Answer Wiki

Thanks. We'll let you know when a new response is added.

Are you doing row by row updates? What does the execution plan for the update statement look like? SQL Server is more efficient when doing larger bulk operations. Instead try dumping the data into a staging table, then updating all the rows (or larger batches of rows say 1000 or 5000 at a time) from the staging table to the production table.

From sflatbush: Sorry mrdenny, that’s not applicable to what I asked. The answer is here.

update TableB
set FK2 = at.PK2
from
TableB b
join
(select PK, FK1, FK2, seq = (select count(*) from TableB b2 where b2.FK1 = b.FK1 and b2.FK2 <= b.FK2) from TableB b) bt
on b.PK = bt.PK
join
(select PK1, PK2, seq = (select count(*) from TableA a2 where a2.PK1 = a.PK1 and a2.PK2 <= a.PK2) from TableA a) at
on at.PK1 = bt.FK1
and at.seq = bt.seq

I modified it for one foreign key and it works great!

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Sflatbush
    Mrdenny, Yes, it is row by row updates since it is through an SSIS SQL Task component. How would you like to view the execution plan? I'm not sure I understand what you mean by updating the rows in batches when I don't really know how many rows are going to be updated in TableA. Have you implemented this in SSIS?
    60 pointsBadges:
    report
  • Sflatbush
    [...] Sflatbush was having a SQL Server 2008 SSIS update command performance issue. Mr. Denny provided an approved [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following