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.
set FK2 = at.PK2
(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
(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!