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:

        @BatchSize INT;      -- keep this below 5k

SET @BatchSize = 4000;

SET @Rows = @BatchSize;     

    WHILE (@Rows = @BatchSize)
        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


    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);

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?

  • \$\begingroup\$ What type of index are you trying to create? Clustered? Non-clustered? \$\endgroup\$ – Der Kommissar Dec 21 '17 at 20:03
  • \$\begingroup\$ @202_accepted clustered. It will also be the primary key, if it matters. \$\endgroup\$ – indigochild Dec 21 '17 at 20:05
  • \$\begingroup\$ Why are you updating in batches and a loop? UPDATE Master.VOUCHER_LINE SET tempBusinessUnit = BUSINESS_UNIT WHERE tempBusinessUnit = 'xxxxx' AND BUSINESS_UNIT IS NOT NULL \$\endgroup\$ – Der Kommissar Dec 21 '17 at 20:09
  • \$\begingroup\$ @202_accepted My logs were filling up, so I moved to batches. This was based on the lock escalation page on Microsoft's site. The threshold is 5000 locks. If rows are being locked, I wanted to come in under that limit. So 4000 rows at a time. \$\endgroup\$ – indigochild Dec 21 '17 at 20:13
  • \$\begingroup\$ Why don't you simply create a new table, insert/select the new data, then drop & rename? \$\endgroup\$ – dnoeth Dec 22 '17 at 9:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.