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.
SQL Server 2008 R2
August 10, 2010 9:37 PM
August 13, 2010 3:10 PM