May 5, 2010 at 12:40 am
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).
i am attaching the execution plan (Update.sqlplan).
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
May 5, 2010 at 9:14 am
A few points:
* The main reason this particular update is so slow is that you have a non-clustered index (IX_UserName) on the UserName column. The update would probably be much faster if you disabled this index before the update and enabled it again after the update.
* If you are updating many columns in the table you should do it with one single update statement. That way you only have to rewrite the table once.
* The best way to do this is probably to write a SELECT that returns the result exactly the way you want it by joining to the other tables, then use SELECT INTO to create a new table with the correct data. You can then drop the old table and rename the new table. If you are updating all rows in the table this method is often faster than using UPDATE.
/SG
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply