I'm trying to update a table with about 6.5 million rows in a reasonable amount of time, but I'm having some issues. We're adding a new column to an existing table and then setting a value for all rows based on the data in a column in another table.
UPDATE TOP (20000) c SET c.NewColumn = ISNULL(p.Col1, p.Col2) FROM dbo.Child c INNER JOIN dbo.Parent p on c.FKId = p.Id WHERE c.NewColumn IS NULL
in a loop, like this article. The update was still running after 2.5 hours. I'm wondering if disabling indexes on
dbo.Child would make an impact. NewColumn has no indexes, nor will it, but there are other indexes (about 5) on
Is SQL Server smart enough to see that it doesn't need to update the other indexes (as they are not part of the UPDATE), or would we benefit from temporarily disabling the indexes while we do the update statements?
This is SQL Server 2012 but the DB in question is in 2008 compatibility mode.