CODEWITHSUNDEEP
sqlperformancet-sqlindexing
user3284707
user3284707

Reputation: 3341

Index scan on SQL update statement

I have the following SQL statement, which I would like to make more efficient. Looking through the execution plan I can see that there is a Clustered Index Scan on @newWebcastEvents. Is there a way I can make this into a seek? Or are there any other ways I can make the below more efficient?

declare @newWebcastEvents table (
    webcastEventId int not null primary key clustered (webcastEventId) with (ignore_dup_key=off)
)

insert into @newWebcastEvents
select wel.WebcastEventId
from WebcastChannelWebcastEventLink wel with (nolock)
where wel.WebcastChannelId = 1178

Update WebcastEvent
set WebcastEventTitle = LEFT(WebcastEventTitle, CHARINDEX('(CLONE)',WebcastEventTitle,0) - 2)
where
WebcastEvent.WebcastEventId in (select webcastEventId from @newWebcastEvents)

Upvotes: 1

Views: 1165

Answers (1)

marc_s
marc_s

Reputation: 754538

The @newWebcastEvents table variable only contains the only single column, and you're asking for all rows of that table variable in this where clause:

where
   WebcastEvent.WebcastEventId in (select webcastEventId from @newWebcastEvents)

So doing a seek on this clustered index is usually rather pointless - SQL Server query optimizer will need all columns, all rows of that table variable anyway..... so it chooses an index scan.

I don't think this is a performance issue, anyway.

An index seek is useful if you need to pick a very small number of rows (<= 1-2% of the original number of rows) from a large table. Then, going through the clustered index navigation tree and finding those few rows involved makes a lot more sense than scanning the whole table. But here, with a single int column and 15 rows --> it's absolutely pointless to seek, it will be much faster to just read those 15 int values in a single scan and be done with it...

Update: no sure if it makes any difference in terms of performance, but I personally typically prefer to use joins rather than subselects for "connecting" two tables:

UPDATE we
SET we.WebcastEventTitle = LEFT(we.WebcastEventTitle, CHARINDEX('(CLONE)', we.WebcastEventTitle, 0) - 2)
FROM dbo.WebcastEvent we
INNER JOIN @newWebcastEvents nwe ON we.WebcastEventId = nwe.webcastEventId

Upvotes: 1

Related Questions