Batch update in a large dataset

Question

We have 3 tables:

• Product (product)
• Stock (product_Stock)
• Price (product_price)

In each table, we have more than 290 bilions of rows. These tables have a compound primary key: product_fk and company_fk

We have to change the value of field product_fk in 1.2 million of records. Using an C#.Net app, the performance is too slow. About 19 rows per second

Is there another way to do it using directly the database to speed up?

The C# app exec a simple update.

UPDATE product SET product_fk = 'abc123' WHERE product_fk = '753xpto' AND company_fk = 'abc';
UPDATE product_Stock SET product_fk = 'abc123' WHERE product_fk = '753xpto' AND company_fk = 'abc';
UPDATE product_price SET product_fk = 'abc123' WHERE product_fk = '753xpto' AND company_fk = 'abc';

Answer 1

There are 3x different updates there, possibly different indexing strategies for each. Are you changing the content of a clustered index? Are product stock and product price the same size? Both 290b records? or 145b each?
Either way, you might well be trying move 1.2m records within your 290b if you are changing the clustered key. Depending on how full the index pages are (actual, not fill factor) you might also experience some performance hit from page splitting. You should definitely check the query plan for each, but if this is changing an index key, especially a clustered index key then you may well need to rebuild the index [or at least check it has sufficiently low fragmentation] Reorganising the index