0

I have a large partitioned table of 16 billion plus records. I am currently updating one of the columns to NULL using the partition number. The process is cumbersome in that it takes a lot of time to complete the operation. One of the partitions of 70 GB took 11 hours to complete the update. So my question is, is it possible to create a column within a partition so that I can use it to do the update in a faster way? Ideally I'd like to create the new column with NULL values, then delete the old column and rename the new one to the same name, rather than update billions of rows i.e. create NewColumn with null values rename CurrentColumn to CurrentColumn_delete rename NewColumn to CurrentColumn Delete CurrentColumn_delete but only on the partition and not whole table

3
  • This raises several questions. What version of SQL server are you using? Are you doing the update in one enormously long transaction or did you attempt to break the update procedure to batches, for example 100K rows per transactions? Breaking up the transaction to several smaller batches could yield faster processing.
    – Sranda
    Feb 2 at 8:48
  • 1
    "One of the partitions of 70 GB took 11 hours to complete the update." - This sounds like an architectural problem. 70 GB is not a lot of data and shouldn't take even an hour to update, let alone 11 hours. Kind of sounds like your real problem is understanding why that took so long, which we probably can help you figure out if you want to add details about your schema, the data, the UPDATE query used, it's execution plan if possible, and some examples.
    – J.D.
    Feb 2 at 11:29
  • @Sranda Thank you for your response. I am using SQL SERVER 2017.I am running the update in one enormously long transaction and this is per partition and the amount of data in each partition varies. In regards to your suggestion, how do I update the records in batches?
    – Cris
    Feb 3 at 6:12

1 Answer 1

0

To answer a question of "how do I update the records in batches" with brief comment:

DECLARE @i INT = 1;
/*here you say how many records would you like to update at once*/
DECLARE @batchsize = 100000;
WHILE EXISTS(SELECT TOP(1) * FROM [table] WHERE col_X IS NOT NULL)
BEGIN
    print @i
    BEGIN TRAN
        UPDATE TOP(@batchsize) [table] SET col_X = NULL WHERE col_X IS NOT NULL;
    COMMIT
    SET @i = @i+1
END

This is rather to demonstrate the concept than give you the solution tailored to your needs, because:

  • no error handling is in the code
  • as J.D. said, we'll need to get deeper insight to your data and schema
  • the less rows will meet the condition of not being NULL, the slower the update will be (it will be more difficult to find them)

Other thing that could be relevant to you is to check the indexes on the table - in how many indexes is the updated column used? Not only the table but each index has to be updated too and it goes single-threaded - it influences your performance a lot.

Regarding the rename - it is metadata operation only (but for the whole table) and you could use the following snippet:

ALTER TABLE [table] ADD colX_new int;
EXEC sp_rename '[table].colX' , 'colX_old';
EXEC sp_rename '[table].colX_new' , 'colX';

I don't think you can add column to individual partition only.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.