Posted by Erland Sommarskog on 11/07/05 10:59

001 (001@ms8.url.com.tw) writes:
> The select statement needs only 1 second to complete the query.
> But the update statement spends 30 minutes. Why?
>
>
> SELECT STATEMENT:
> declare @IDate smalldatetime
> select @IDate=col001 from USDay
> select * from USDay A
> join (
> select US990010, US990020, US990030, US990040, US990050, US990060,
> US990070 from US99000D where US990010=@IDate
> ) B on A.col001=B.US990010 and A.col002=B.US990020
> where B.US990010 is not null
>
>
> UPDATE STATEMENT:
> update US99000D
> set US990030=A.col003,
> US990040=A.col004,
> US990050=A.col005,
> US990060=A.col006,
> US990070=A.col007
> from USDay A
> join (
> select US990010, US990020, US990030, US990040, US990050, US990060,
> US990070 from US99000D where US990010=@IDate
> ) B on A.col001=B.US990010 and A.col002=B.US990020
> where B.US990010 is not null


Unless there is a blocking issue, I would suspect it is because the
US99000D is not match against the instance in the derived table. How this
work:

update US99000D
set US990030=A.col003,
US990040=A.col004,
US990050=A.col005,
US990060=A.col006,
US990070=A.col007
from US99000D
join USDay A on A.col001=B.US990010 and A.col002=B.US990020
where B.US990010=@IDate


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

• Next in forum: Re: update spends 1800 times more than select
• Prev in forum: update spends 1800 times more than select
• Thread view: Re: update spends 1800 times more than select

[Reply to this message]