0

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';
6
  • Can you post the execution plan?
    – Caleb Carl
    Nov 9, 2023 at 19:31
  • 1
    Please consider reading this advice
    – mustaccio
    Nov 9, 2023 at 19:51
  • 1
    Also posting the C# code wouldn't hurt
    – Xedni
    Nov 9, 2023 at 20:40
  • 2
    Just FYI, especially on a table of that size, you shouldn't be using foreign keys to fields that have the risk of being altered, for exactly this reason. If, say product has a natural key 'abc', have that be secondary to a surrogate primary key and have all other tables reference the surrogate primary key. That way, you can change the natural key to your hearts content, and all your relationships will still hold. Otherwise, you run into exactly this problem.
    – Xedni
    Nov 9, 2023 at 20:43
  • 1
    Can't you just insert the 1.2 million PKs into an indexed temp table and do a joined update? Nov 10, 2023 at 0:41

1 Answer 1

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.