Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

closed as unclear what you're asking by Max Vernon, ypercube, Paul White, RolandoMySQLDBA, Mark Storey-Smith Mar 15 at 13:53

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

2  
Can you add the CREATE TABLE script for the table and what indexes you have? –  ypercube Mar 14 at 12:43
1  
Are there any Blocked processes in Activity Monitor when your query is running? (You can also use sp_WhoIsActive to check this.) –  Andomar Mar 14 at 13:38
    
Maybe my answer to another question couple days ago can help you. –  Yaroslav Mar 14 at 13:47
    
Sorry friends none of the suggestions worked for me –  sairam Mar 14 at 14:08
1  
If you're trying to update 35'000 rows at once, you might run into the problem that SQL Server will use a lock escalation and will attempt to lock the entire table for an update. If something else is already happening at the same time, SQL Server might just not be able to get that table-wide, update/exclusive lock - so it'll wait until it does get that lock... you can get around this by updating no more than 5000 rows at once –  marc_s Mar 15 at 20:14
show 2 more comments

2 Answers 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 greater than 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.

share|improve this answer
    
Note: technet.microsoft.com/en-us/library/ms188774.aspx seems to indicate that @@rowcount isn't a viable long-term solution for updates. –  Mat Mar 14 at 14:09
    
Good point, but that is from SS 2013 up ! i think –  Up_One Mar 14 at 14:14
2  
@Mat, that is in refference to SET ROWCOUNT not @@ROWCOUNT. The way i have always avoided SET rowcount is to use UPDATE TOP(N) and never mess with the rowcount. This is after forgetting to reset it to 0 once and wondering why only x rows were affected. –  Wind Raven Mar 14 at 14:23
add comment

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.)

share|improve this answer
add comment

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