Update large table with millions of rows in Azure SQL Server, in Parallel

Question

We have a large ProductOrders table with many rows, and plan to update it. The table has range of ProductType Ids from 1-1000.

Table has around 2 million rows, 15 columns. Common ProductOrders table: ProductOrderId, ProductId, ProductTypeid, ManufacturerId, OrderDate, etc, indexes are placed properly on foreign keys and common queries.

Two options are proposed to update the table,

1. Setup 10,000+ multiple queries (grouped by range of Product TypeIds) which are sent out in Parallel, and update the ProductOrders Table all at once.

2. Or the second proposition, is using following method. Update them sequentially proposed here to avoid locking up the whole table. https://stackoverflow.com/a/35931214/12425844

I would assume option 1) having multiple update queries can lead to multiple Writer-Writer Deadlocks.

Is it safe to have multiple queries sent in parallel? Which option is best practice? Additionally, thoughts it best practice to let Sql automatically conduct DDL items in parallel with configured Maxdop setting, rather than user attempting.

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver15

first option is being proposed by our Application software team .

Currently using AzureSQl.

• user212533: Can you share the relevant DDL + any indexes that may be present?
• user212533: "Placed properly" doesn't exactly help here - is there an index with "ProductTypeId" as its first column? What column(s) are you updating and why?
• Artportraitdesign1: we are updating productStatusId, regionid, updateDate, and yes, there is index on ProductTypeId, ProductOrderId is primary key
• garam: Parallel updates are a big red flag! How did you go from updating a table to DDL items with MaxDOP?
• David Browne - Microsoft: Why not update the table with a single UPDATE statement?

Answer 1

Why not update the table with a single UPDATE statement?

If your database is set to READ COMMITTED SNAPSHOT and you can afford to block other sessions from writing to the table during the update, that's the simplest option.

Otherwise update them sequentially in batches (option 2). Running lots of parallel DML statements (Option 1) is too complicated and too prone to blocking and deadlocking issues. IE it's not going to solve all the problems of using a single UPDATE statement, and it introduces new ones.