SQL Server 2008 SSIS Update Command Performance Issue
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
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
ASKED:
August 10, 2010 9:37 PM
UPDATED:
August 13, 2010 3:10 PM
Answer Wiki:
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. <a href="http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=87571">The answer is here</a>.
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!
To see all answers submitted to the Answer Wiki: View Answer History.
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?