update spends 1800 times more than select

[001]

Hello,

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.US99 0010 and A.col002=B.US99 0020
where B.US990010 is not null


UPDATE STATEMENT:
update US99000D
set US990030=A.col0 03,
US990040=A.col0 04,
US990050=A.col0 05,
US990060=A.col0 06,
US990070=A.col0 07
from USDay A
join (
select US990010, US990020, US990030, US990040, US990050, US990060,
US990070 from US99000D where US990010=@IDate
) B on A.col001=B.US99 0010 and A.col002=B.US99 0020
where B.US990010 is not null


INDEX:
clustered index: US990020, US990010
non-unique index: US990010, US990020

[Erland Sommarskog]

Re: update spends 1800 times more than select

001 (001@ms8.url.co m.tw) writes:[color=blue]
> 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.US99 0010 and A.col002=B.US99 0020
> where B.US990010 is not null
>
>
> UPDATE STATEMENT:
> update US99000D
> set US990030=A.col0 03,
> US990040=A.col0 04,
> US990050=A.col0 05,
> US990060=A.col0 06,
> US990070=A.col0 07
> from USDay A
> join (
> select US990010, US990020, US990030, US990040, US990050, US990060,
> US990070 from US99000D where US990010=@IDate
> ) B on A.col001=B.US99 0010 and A.col002=B.US99 0020
> where B.US990010 is not null[/color]


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.col0 03,
US990040=A.col0 04,
US990050=A.col0 05,
US990060=A.col0 06,
US990070=A.col0 07
from US99000D
join USDay A on A.col001=B.US99 0010 and A.col002=B.US99 0020
where B.US990010=@IDa te


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Peter Yu]

Re: update spends 1800 times more than select



It works fine. But I don't understand why you said *the
US99000D is not match against the instance in the derived table* ?

*** Sent via Developersdex http://www.developersdex.com ***

[Erland Sommarskog]

Re: update spends 1800 times more than select

Peter Yu (yuyuante@ms3.h inet.net) writes:[color=blue]
> It works fine. But I don't understand why you said *the
> US99000D is not match against the instance in the derived table* ?[/color]

The UPDATE FROM syntax is proprietary for MS SQL Server and Sybase. It
works well most of the time, but there are some funny tweaks when you
leave the main row. I recommend that you always include the target table
as the first table in the FROM clause to avoid accidents like this. What
happened now was that you probably got a cartesian product between the
target table and the tables in the FROM clause.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Hugo Kornelis]

Re: update spends 1800 times more than select

On Mon, 7 Nov 2005 15:08:30 +0800, 001 wrote:
[color=blue]
>Hello,
>
>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.US99 0010 and A.col002=B.US99 0020
> where B.US990010 is not null
>
>
>UPDATE STATEMENT:
>update US99000D
> set US990030=A.col0 03,
> US990040=A.col0 04,
> US990050=A.col0 05,
> US990060=A.col0 06,
> US990070=A.col0 07
> from USDay A
> join (
> select US990010, US990020, US990030, US990040, US990050, US990060,
>US990070 from US99000D where US990010=@IDate
> ) B on A.col001=B.US99 0010 and A.col002=B.US99 0020
> where B.US990010 is not null
>
>
>INDEX:
>clustered index: US990020, US990010
>non-unique index: US990010, US990020[/color]

Hi 001,

The proprietary UPDATE FROM syntax has many issues. One of them is that
these two versions are considered equal:

UPDATE Table1
SET Something = SomethingElse
FROM Table2
WHERE xxx = yyy

or

UPDATE Table1
SET Something = SomethingElse
FROM Table2, Table1
WHERE xxx = yyy

The technicalities: the table name to be updated is looked for in the
FROM clause. If one match is found, that table is used. If two matches
are found, an error is raised (you'll have to use an alias in that
case). And if no matches is found, the table is implicitly added to the
FROM clause.

Since the tables in your UPDATE's FROM clause are called USDay, with
alias A and (no name for derived table), with alias B, the table name
US99000D is added to the FROM clause. Your UPDATE is equivalent, not to
the SELECT statement above, but to this one below:

select * from USDay A
join (
select US990010, US990020, US990030, US990040, US990050, US990060,
US990070 from US99000D where US990010=@IDate
) B on A.col001=B.US99 0010 and A.col002=B.US99 0020
CROSS JOIN US99000D
where B.US990010 is not null


The best way to prevent these problems is two teach yourself the
following guidelines:

1. Avoid the proprietary UPDATE ... FROM and DELETE FROM ... FROM when
ever possible. Only use it if ANSI syntax has distinct disadvantages.

2. If you have to use UODATE ... FROM, *always* include all tables
(including the one to update) in the FROM; *always* give all tables in
the FROM an alias, and (and here comes the hammer) **always** include
the _alias_, not the table name after the UPDATE keyword.

Example:
UPDATE a
SET Something = b.SomethingElse
FROM Table2 AS b
INNER JOIN Table1 AS a
ON a.xxx = b.yyy


Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)