Return to Answer

UPDATE: Please see my answer to a question that is related to this question for the full implementation of what is suggested above, including a mechanism to track status and cancel cleanly: sql server: updating fields on huge table in small chunks: how to get progress/status?

UPDATE: Please see my answer to a question that is related to this question for the full implementation of what is suggested above, including a mechanism to track status and cancel cleanly: sql server: updating fields on huge table in small chunks: how to get progress/status?

1. For your loop, doing WHILE (@@ROWCOUNT = @CHUNK_SIZE) is slightly better since if the number of rows updated on the last iteration is less than the amount requested to UPDATE, then there is no work left to do.

2. If the deleted field is a BIT datatype, then isn't that value determined by whether or not deletedDate is 2000-01-01? Why do you need both?

3. If these two fields are new and you added them as NULL so it could be an online / non-blocking operation and are now wanting to update them to their "default" value, then that wasn't necessary. Starting in SQL Server 2012 (Enterprise Edition only), adding NOT NULL columns that have a DEFAULT constraint are non-blocking operations as long as the value of the DEFAULT is a constant. So if you aren't using the fields yet, just drop and re-add as NOT NULL and with a DEFAULT constraint.

4. If no other process is updating these fields while you are doing this UPDATE, then it would be faster if you queued the records that you wanted to update and then just work off that queue. There is a performance hit in the current method as you have to re-query the table each time to get the set that needs to be changed. Instead, you could do the following which only scans the table once on those two fields and then issues only very targeted UPDATE statements. There is also no penalty from stopping the process at any time and starting it later since the initial population of the queue will simply find the records left to update.

5. Create a temporary table (#FullSet) that just has the key fields from the clustered index in it.

6. Create a second temporary table (#CurrentSet) of that same structure.

7. insert into #FullSet via SELECT TOP(n) KeyField1, KeyField2 FROM [huge-table] where deleted is null or deletedDate is null;

   The TOP(n) is in there due to the size of the table. With 100 Million rows in the table, you don't really need to populate the queue table with that entire set of keys, especially if you plan on stopping the process every so often and restarting it later. So maybe set n to 1 million and let that run through to completion. You can always schedule this in a SQL Agent job that runs the set of 1 million (or maybe even less) and then waits for the next scheduled time to pick up again. You can then schedule to run every 20 minutes so there will be some enforced breathing room between sets of n, but it will still finish the entire process unattended. Then just have the job delete itself when there is nothing more to do :-).

8. in a loop, do:

   1. Populate the current batch via something like DELETE TOP (4995) FROM #FullSet OUTPUT Deleted.KeyField INTO #CurrentSet (KeyField);
   2. IF (@@ROWCOUNT = 0) BREAK;
   3. Do the UPDATE using something like: UPDATE ht SET ht.deleted = 0, ht.deletedDate='2000-01-01' FROM [huge-table] ht INNER JOIN #CurrentSet cs ON cs.KeyField = ht.KeyField;
   4. Clear out the current set: TRUNCATE TABLE #CurrentSet;

1. For your loop, doing WHILE (@@ROWCOUNT = @CHUNK_SIZE) is slightly better since if the number of rows updated on the last iteration is less than the amount requested to UPDATE, then there is no work left to do.

2. If the deleted field is a BIT datatype, then isn't that value determined by whether or not deletedDate is 2000-01-01? Why do you need both?

3. If these two fields are new and you added them as NULL so it could be an online / non-blocking operation and are now wanting to update them to their "default" value, then that wasn't necessary. Starting in SQL Server 2012 (Enterprise Edition only), adding NOT NULL columns that have a DEFAULT constraint are non-blocking operations as long as the value of the DEFAULT is a constant. So if you aren't using the fields yet, just drop and re-add as NOT NULL and with a DEFAULT constraint.

4. If no other process is updating these fields while you are doing this UPDATE, then it would be faster if you queued the records that you wanted to update and then just work off that queue. There is a performance hit in the current method as you have to re-query the table each time to get the set that needs to be changed. Instead, you could do the following which only scans the table once on those two fields and then issues only very targeted UPDATE statements. There is also no penalty from stopping the process at any time and starting it later since the initial population of the queue will simply find the records left to update.

5. Create a temporary table (#FullSet) that just has the key fields from the clustered index in it.

6. Create a second temporary table (#CurrentSet) of that same structure.

7. insert into #FullSet via SELECT TOP(n) KeyField1, KeyField2 FROM [huge-table] where deleted is null or deletedDate is null;

   The TOP(n) is in there due to the size of the table. With 100 Million rows in the table, you don't really need to populate the queue table with that entire set of keys, especially if you plan on stopping the process every so often and restarting it later. So maybe set n to 1 million and let that run through to completion. You can always schedule this in a SQL Agent job that runs the set of 1 million (or maybe even less) and then waits for the next scheduled time to pick up again. You can then schedule to run every 20 minutes so there will be some enforced breathing room between sets of n, but it will still finish the entire process unattended. Then just have the job delete itself when there is nothing more to do :-).

8. in a loop, do:

   1. Populate the current batch via something like DELETE TOP (4995) FROM #FullSet OUTPUT Deleted.KeyField INTO #CurrentSet (KeyField);
   2. IF (@@ROWCOUNT = 0) BREAK;
   3. Do the UPDATE using something like: UPDATE ht SET ht.deleted = 0, ht.deletedDate='2000-01-01' FROM [huge-table] ht INNER JOIN #CurrentSet cs ON cs.KeyField = ht.KeyField;
   4. Clear out the current set: TRUNCATE TABLE #CurrentSet;

9. In some cases it helps to add a Filtered Index to assist the SELECT that feeds into the #FullSet temp table. Here are some considerations related to adding such an index:

   1. The WHERE condition should match the WHERE condition of your query, hence WHERE deleted is null or deletedDate is null
   2. At the beginning of the process, most rows will match your WHERE condition, so an index isn't that helpful. You might want to wait until somewhere around the 50% mark before adding this. Of course, how much it helps and when it is best to add the index vary due to several factors, so it is a bit of trial and error.
   3. You might have to manually UPDATE STATS and/or REBUILD the index to keep it up to date since the base data is changing quite frequently
   4. Be sure to keep in mind that the index, while helping the SELECT, will hurt the UPDATE since it is another object that must be updated during that operation, hence more I/O. This plays into both using a Filtered Index (which shrinks as you update rows since fewer rows match the filter), and waiting a little while to add the index (if it's not going to be super helpful in the beginning, then no reason to incur the additional I/O).

1. For your loop, doing WHILE (@@ROWCOUNT = @CHUNK_SIZE) is slightly better since if the number of rows updated on the last iteration is less than the amount requested to UPDATE, then there is no work left to do.

2. If the deleted field is a BIT datatype, then isn't that value determined by whether or not deletedDate is 2000-01-01? Why do you need both?

3. If these two fields are new and you added them as NULL so it could be an online / non-blocking operation and are now wanting to update them to their "default" value, then that wasn't necessary. Starting in SQL Server 2012 (Enterprise Edition only), adding NOT NULL columns that have a DEFAULT constraint are non-blocking operations as long as the value of the DEFAULT is a constant. So if you aren't using the fields yet, just drop and re-add as NOT NULL and with a DEFAULT constraint.

4. If no other process is updating these fields while you are doing this UPDATE, then it would be faster if you queued the records that you wanted to update and then just work off that queue. There is a performance hit in the current method as you have to re-query the table each time to get the set that needs to be changed. Instead, you could do the following which only scans the table once on those two fields and then issues only very targeted UPDATE statements. There is also no penalty from stopping the process at any time and starting it later since the initial population of the queue will simply find the records left to update.

5. Create a temporary table (#FullSet) that just has the key fields from the clustered index in it.

6. Create a second temporary table (#CurrentSet) of that same structure.

7. insert into #FullSet via SELECT TOP(n) KeyField1, KeyField2 FROM [huge-table] where deleted is null or deletedDate is null;

   The TOP(n) is in there due to the size of the table. With 100 Million rows in the table, you don't really need to populate the queue table with that entire set of keys, especially if you plan on stopping the process every so often and restarting it later. So maybe set n to 1 million and let that run through to completion. You can always schedule this in a SQL Agent job that runs the set of 1 million (or maybe even less) and then waits for the next scheduled time to pick up again. You can then schedule to run every 20 minutes so there will be some enforced breathing room between sets of n, but it will still finish the entire process unattended. Then just have the job delete itself when there is nothing more to do :-).

8. in a loop, do:

   1. Populate the current batch via something like DELETE TOP (4995) FROM #FullSet OUTPUT Deleted.KeyField INTO #CurrentSet (KeyField);
   2. IF (@@ROWCOUNT = 0) BREAK;
   3. Do the UPDATE using something like: UPDATE ht SET ht.deleted = 0, ht.deletedDate='2000-01-01' FROM [huge-table] ht INNER JOIN #CurrentSet cs ON cs.KeyField = ht.KeyField;
   4. Clear out the current set: TRUNCATE TABLE #CurrentSet;

1. For your loop, doing WHILE (@@ROWCOUNT = @CHUNK_SIZE) is slightly better since if the number of rows updated on the last iteration is less than the amount requested to UPDATE, then there is no work left to do.

2. If the deleted field is a BIT datatype, then isn't that value determined by whether or not deletedDate is 2000-01-01? Why do you need both?

3. If these two fields are new and you added them as NULL so it could be an online / non-blocking operation and are now wanting to update them to their "default" value, then that wasn't necessary. Starting in SQL Server 2012 (Enterprise Edition only), adding NOT NULL columns that have a DEFAULT constraint are non-blocking operations as long as the value of the DEFAULT is a constant. So if you aren't using the fields yet, just drop and re-add as NOT NULL and with a DEFAULT constraint.

4. If no other process is updating these fields while you are doing this UPDATE, then it would be faster if you queued the records that you wanted to update and then just work off that queue. There is a performance hit in the current method as you have to re-query the table each time to get the set that needs to be changed. Instead, you could do the following which only scans the table once on those two fields and then issues only very targeted UPDATE statements. There is also no penalty from stopping the process at any time and starting it later since the initial population of the queue will simply find the records left to update.

5. Create a temporary table (#FullSet) that just has the key fields from the clustered index in it.

6. Create a second temporary table (#CurrentSet) of that same structure.

7. insert into #FullSet via SELECT TOP(n) KeyField1, KeyField2 FROM [huge-table] where deleted is null or deletedDate is null;

   The TOP(n) is in there due to the size of the table. With 100 Million rows in the table, you don't really need to populate the queue table with that entire set of keys, especially if you plan on stopping the process every so often and restarting it later. So maybe set n to 1 million and let that run through to completion. You can always schedule this in a SQL Agent job that runs the set of 1 million (or maybe even less) and then waits for the next scheduled time to pick up again. You can then schedule to run every 20 minutes so there will be some enforced breathing room between sets of n, but it will still finish the entire process unattended. Then just have the job delete itself when there is nothing more to do :-).

8. in a loop, do:

   1. Populate the current batch via something like DELETE TOP (4995) FROM #FullSet OUTPUT Deleted.KeyField INTO #CurrentSet (KeyField);
   2. IF (@@ROWCOUNT = 0) BREAK;
   3. Do the UPDATE using something like: UPDATE ht SET ht.deleted = 0, ht.deletedDate='2000-01-01' FROM [huge-table] ht INNER JOIN #CurrentSet cs ON cs.KeyField = ht.KeyField;
   4. Clear out the current set: TRUNCATE TABLE #CurrentSet;
 

UPDATE: Please see my answer to a question that is related to this question for the full implementation of what is suggested above, including a mechanism to track status and cancel cleanly: sql server: updating fields on huge table in small chunks: how to get progress/status?