August 14, 2011 at 4:55 am
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
August 14, 2011 at 5:16 am
Have you checked the execution plan?
From the looks of things, it's going to be scanning the temp table because you don't have indexing there. As data sizes grow, performance will get worse. Without the actual exec plan though, it's hard to know based on what you've shown so far.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 14, 2011 at 5:32 am
i am adding the execution plan
thanks for any help:)
August 14, 2011 at 6:12 am
You're scanning almost 11 million rows. That's just going to be slow. No way to add some WHERE clauses to the query to limit the result set? Add in the parallelism and you're getting 16 executions on the scan of about 700k rows each. There's just not much to work with here. I can't suggest an index or something. You need to filter down the number of rows you're dealing with. The output is 3000 rows. Referring to 11 million to get 3000 is just problematic.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply