0

I have a table with 128 million records. There are no foreign keys or indexes, just a heap table. I need to update 3 fields in all the records based on a logic that depends on other fields in the same table. It takes ~1 hour to complete.

Is there any way to speed up this update?

This is the DDL:

create table table_1
(
    Column_1 int  NOT NULL,
    Column_2 int  NOT NULL,
    Column_3 smallint  NULL,
    Column_4 int  NULL,
    Column_5 smallint  NOT NULL,
    Column_6 bit  NOT NULL,
    Column_7 bit  NOT NULL,
    Column_8 int  NULL,
    Column_9 int  NULL,
    Column_10 varchar  NULL,
    Column_11 varchar  NULL,
    Column_12 varchar  NULL,
    Column_13 int  NULL,
    Column_14 bit  NOT NULL,
    Column_15 uniqueidentifier  NULL,
    Column_16 uniqueidentifier  NULL,
    Column_17 int  NOT NULL,
    Column_18 int  NOT NULL,
    Column_19 int  NOT NULL,
    Column_20 smalldatetime  NULL,
    Column_21 int  NULL,
    Column_22 smalldatetime  NULL,
    Column_23 smalldatetime  NULL,
    Column_24 int  NULL,
    Column_25 smalldatetime  NULL,
    Column_26 int  NULL,
    Column_27 datetime  NULL,
    Column_28 nvarchar  NULL,
    Column_29 bit  NOT NULL,
    Column_30 bit  NOT NULL,
    Column_31 int  NULL,
    Column_32 int  NULL,
    Column_33 int  NULL,
    Column_34 int  NULL,
    Column_35 int  NULL,
    Column_36 bit  NULL,
    Column_37 int  NULL,
    Column_38 int  NULL,
    Column_39 bit  NULL,
    Column_40 int  NULL,
    Column_41 datetimeoffset  NULL,
    Column_42 int  NULL,
    Column_43 datetime  NULL,
    Column_44 char  NULL)

This is the update:

update table1
set  
    Column_34 = Column_31,
    Column_35 = Column_32,
    Column_37 = Column_33
11
  • A few questions that would make answering easier: How many rows are you trying to update? Are you updating all at once or in batches? Can you add indexes to speed up finding the rows to update?
    – BCM
    Oct 27, 2021 at 16:48
  • 1
    Add your table DDL (including PK definition) and the update query to your question.
    – Dan Guzman
    Oct 27, 2021 at 17:02
  • 2
    Maybe a different perspective could help here, after reviewing your UPDATE statement why do you want to have two columns with the same exact data between them?...What is your ultimate goal with these other columns you're trying to update?
    – J.D.
    Oct 27, 2021 at 18:13
  • 1
    This seems like a one-time execution - so why is there a need to optimize it? You know how long it currently takes so you have run it once already. And obviously you should update rows that need no changes. If column_34 should always be the same as column_31, why not guarantee that using a computed column? Rename the existing column if needed to avoid dropping the column.
    – SMor
    Oct 27, 2021 at 21:50
  • 1
    @YvánEcarri Why not just rename the old columns to the new names instead?...or at least take up SMor's suggestion of just adding a computed column? You can easily rename columns either using SSMS or by calling the system stored procedure sp_rename which should be pretty instantaneous since it's just a metadata change.
    – J.D.
    Oct 28, 2021 at 11:19

1 Answer 1

0

Using sp_rename per my suggestion in the comments almost meets all of your requirements of leaving the columns in the same order and keeps them updatable, in addition to being an instantaneous change.

It looks like the only snag is you want to move Column_33 (which will now be called Column_37) to after Column_36, i.e. you want to change the previous column order for that specific column, agreed?

Alternatively you could create a View on top of the Table with the schema fixes you want instead, which would also be updatable and instantaneous.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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