slow performance on update - 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)

slow performance on update

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2011-08-14 : 06:56:06

i got this query :


UPDATE tts
	set tts.[Content]=t3.IndexId
	from TargetTable_Staging tts
	inner join ( 
	select t1.id as PKid,t2.id as IndexId
	FROM TargetTable t1 inner join Tbl_Content t2 (NOLOCK)
	ON t1.[Content]=t2.Value  COLLATE SQL_Latin1_General_CP1_CI_AS )t3 on tts.id=t3.PKid

the structure of the Tbl_Content is :


CREATE TABLE [dbo].[Tbl_Content](
	[Id] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[Value] [nvarchar](200) NULL,
 CONSTRAINT [PK_Tbl_Content] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_Content] UNIQUE NONCLUSTERED 
(
	[Value] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

the TargetTable is a temp table to which i bulk upload data(where to Content column is in size of nvarchar(200) ) and then add an index to this column with :


CREATE NONCLUSTERED INDEX [IX_TargetTable_Content] ON [dbo].[TargetTable] 
(
	[Content] 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 [FG_INDEX]

this query is putting the ID of the content from Tbl_content, into a second TargetTable_Staging and updating its id. the problem it takes almost 3 minutes!!! while all other queries about 10-100 ms.
any idea?
thanks
peleg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources