improving UPDATE time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

improving UPDATE time

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2010-05-05 : 03:56:46

i have a process which build reports, by breaking data apart to diffrent tables.
Step 1: use bcp to load files into a TempTable on the db.
Step 2: goes on every column and updates the relevent tables with new data.
(at this time i will add that i added to all the columns in the TempTable index, which has improved the insert time by 70%)
this is an example of an index in the TempTable :


CREATE NONCLUSTERED INDEX [IX_Recipient] ON [dbo].[TargetTable] 
(
        [Recipient] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TargetTable] ADD  CONSTRAINT [DF_TargetTableNEW_Recipient_1]  DEFAULT (N'') FOR [Recipient]
GO
[Recipient] [nvarchar](25) NOT NULL,

Step 3: i want to replace all the columns (like the one above) instead of a recipient value, to change it with the suitable id fron Table which contains all recipient.
the problem starts here.
in my load test i have 7.7 million rows.
when i try to do :


UPDATE t1       set t1.UserName=Cast(t2.Id  as NVARCHAR(20))
        FROM TargetTable t1 inner join Dyn_User t2 (NOLOCK)
        ON t1.UserName=t2.Name

it takes 1.5 hours (and this is on on column out of ~20).
any idea on how can i improve this update?
one thing i have noticed is that there is a "sort" in it the execution plan, which takes 41% - can i avoid this?
Thanks
Peleg

Kristen
Test

22859 Posts

Posted - 2010-05-05 : 04:27:38

I would add a new column of the correct type (INT I presume) - otherwise you are compounding the problem by holding INT as VARCHAR.

That new column will not be indexed, which will help the update speed.

When you are done you can drop the original column, and rename the new column to the old name (and recreate any indexes / FKeys etc)

If you have several of these to do I would create the new column in a temporary "staging" table with jsut the PK field(s) and columns for each of the new "clean-up" fields you need.

Once that table is fully populate modify the table to delete the original columns (which will remove the original data in those columns), and then add new columns with correct INT data type.

Then update the table's new columns by JOINing to your staging table

Then recreate Indexes etc. on the newly created columns