Update Statement takes forever to complete [closed]

Question

I have table around 1250000 records, and 80 columns

Table Structure

CREATE TABLE [dbo].[Table] (
[Column1] [int] IDENTITY(1, 1) NOT FOR REPLICATION NOT NULL, [Column2] [int] NULL, [Column3] [int] NULL, 
[Column4] [int] NULL, [Column5] [int] NULL, [Column6] [int] NULL, [Column7] [int] NULL, [Column8] [int] NULL, 
[Column9] [int] NULL, [Column10] [varchar](20) NULL, [Column11] [datetime] NULL, [Column12] [int] NULL, 
[Column13] [money] NULL, [Column14] [money] NULL, [Column15] [varchar](40) NULL, [Column16] [int] NULL, 
[Column17] [int] NULL, [Column18] [varchar](20) NULL, [Column19] [varchar](50) NULL, [Column20] [int] NULL, 
[Column21] [int] NULL, [Column22] [int] NULL, [Column23] [int] NULL, [Column24] [int] NULL, 
[Column25Date] [datetime] NULL, [Column26] [int] NULL, [Column27] [int] NULL, [Column28] [int] NULL, 
[Column29] [int] NULL, [Column30] [int] NULL, [Column75] [varchar](50) NULL, [Column31] [int] NULL, 
[Column32] [varchar](15) NULL, [Column33] [int] NULL, [Column34] [money] NULL, 
[Column35] [dbo].[T_Boolean2] NOT NULL, [Column36] [money] NULL, [Column37] [money] NULL, 
[Column38] [int] NULL, [Column39] [int] NULL, [Column40] [varchar](255) NULL, [Column41PrintDate] [datetime] NULL, 
[Column42PostDate] [datetime] NULL, [Column43FirstName] [varchar](30) NULL, [Column44LastName] [varchar](30) NULL, 
[Column45Email] [varchar](50) NULL, [Column46PrimaryPhone] [varchar](10) NULL, 
[Column47SecondaryPhone] [varchar](10) NULL, [Column48PrimaryPhoneType] [smallint] NULL, 
[Column49SecondaryPhoneType] [smallint] NULL, [Column50PrimaryPhoneType] [smallint] NULL, 
[Column51SecondaryPhoneType] [smallint] NULL, [Column52EndDate] [datetime] NULL, 
[Column53] [dbo].[T_Boolean2] NOT NULL, [Column54] [int] NULL, [InsertTimeStamp] [datetime] NOT NULL, 
[UpdateTimeStamp] [datetime] NULL, [Column55ID] [int] NULL, [Column56] [int] NULL, [Column57] [int] NULL, 
[Column58] [int] NULL, [Column59] [money] NULL, [Column60] [money] NULL, [Column61] [money] NOT NULL, 
[Column62] [money] NOT NULL, [Column63] [varchar](30) NULL, [Column64] [varchar](30) NULL, 
[Column65] [varchar](10) NULL, [Column66] [varchar](6) NULL, [Column67] [varchar](10) NULL, 
[Column68] [varchar](35) NULL, [Column69Date] [datetime] NULL, [Column70Date] [datetime] NULL, 
[Column71Date] [datetime] NULL, [Column72] [varchar](255) NULL, [Column73PIN] [varchar](6) NULL, 
[Column74Date] [datetime] NULL, [Column76Date] [datetime] NULL, 
CONSTRAINT [XPKColumn1] PRIMARY KEY CLUSTERED (
[Column1] ASC
 ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

It has 2 Update triggers also to perform background calculations which will update same Table when status changes and some other conditions

When I Update one is its column with normal where condition it is running more than 1 hour but still couldn't able to update the records so i am forced to kill the statement

Update Table Column5 = 12345 where Column5 = 6789

It has to update around 35000 records

Table does have proper indexes with this column and i manually update statistics of the table but still taking too much time to update

Around 10 indexes are there for table

Then I tried update in 2 batches it updated but took very time

I tried to update other column of same table it took few seconds to update

Update Table Column3 = 6789 where Column3 = 12345

how i can improve update statement queries speed fast and using above simple logic

Please suggest any solution,

Thanks in advance

Answer 1

It's difficult to say exactly what's causing it without seeing the code for the trigger, but I'd bet some non-zero amount that misuse of the INSERTED and DELETED virtual tables is to blame.

The thing about INSERTED and DELETED is that they have NO INDEXES. If you join them directly back to the base table (or to each other), you can end up with some very awful performance.

You can generally mitigate this by loading the two into temp tables and creating indexes for however you'll be joining them (usually on the primary key).

SELECT *
INTO #i
FROM INSERTED

CREATE CLUSTERED INDEX CX_i ON #i (primary_key)

SELECT *
INTO #d
FROM DELETED

CREATE CLUSTERED INDEX CX_d ON #d (primary_key)

Something like that, anyway. Then only use your temp tables, and don't touch INSERTED and DELETED.

Be aware that the temp table names have to be unique per session. If you have multiple triggers cascading, they need to use unique table names. You may want to use table variables instead if this becomes a problem, as they are scoped to the trigger/procedure they are running in, with no risk of name collisions. (However, the only way to index a table variable is to declare a clustered primary key in your DECLARE statement.)

Answer 2

You can improve the performance of an update operation by updating the table in smaller groups.

See example :

SET rowcount 10000

    Update Table 
      Column3 = 6789 
      where Column3 = 12345

    while @@rowcount>0

    Update Table 
      Column3 = 6789 
      where Column3 = 12345

    END

    SET rowcount 0

The above code updates 10000 rows at a time and the loop continues till @@rowcount has a value equal to zero. This ensures that the table is not locked.

Best practices while updating large tables in SQL Server

• Always use a WHERE clause to limit the data that is to be updated
• If the table has too many indices, it is better to disable them during update and enable it again after update
• Instead of updating the table in single shot, break it into groups as shown in the above example.

Another add - and this might be a bit more complex is when you will have big tables is good to partition then by a specific field and then distribute those partition across multiple disks.