I currently have a table with about 29 million records. The table currently has no indexes. However, I would like to build a composite index built on three columns.
When I attempt to add the NON NULL constraint, my logs fill up. So my approach is to create three new columns with NON NULL, fill them up with data from the original three columns, and create my index on the three new columns.
This is the SQL I am using to update my new columns looks like this:
DECLARE @Rows INT, @BatchSize INT; -- keep this below 5k SET @BatchSize = 4000; SET @Rows = @BatchSize; BEGIN TRY WHILE (@Rows = @BatchSize) BEGIN UPDATE Top(@BatchSize) Master.VOUCHER_LINE SET tempBusinessUnit = BUSINESS_UNIT FROM Master.VOUCHER_LINE WHERE tempBusinessUnit = 'xxxxx'; /* This is a default value for the column. It identifies columns that have not been updated. */ SET @Rows = @@ROWCOUNT; /* This prints the time that each loop is completed. I use it to estimate how much time this script will take. */ DECLARE @Time nvarchar(100) SET @Time = CAST(CURRENT_TIMESTAMP as nvarchar) RAISERROR(@Time, 0, 1) WITH NOWAIT END END TRY BEGIN CATCH DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState); END CATCH;
With 29 million records (and about 100 columns), I expect this operation to take a while. After running for the first hour, the performance is 4,000 records about every 2 minutes. At that rate this single column will be done in approximately 10 days.
Is there something I can do to improve performance?