Update statement

[simon]

I have to update column name in table tblA with the same column in table
tblB.

UPDATE a
SET a.name=b.NAME
FROM tblaA a INNER JOIN tblB b
ON a.ID=b.ID

ID is PK, UNIQUE CLUSTERED INDEX in both tables.

This update takes 15 seconds(36000 rows)

IF I execute the same update just with RIGHT JOIN:

UPDATE a
SET a.name=b.NAME
FROM tblaA a RIGHT JOIN tblB b
ON a.ID=b.ID WHERE a.ID is not NULL

I get the same update just that it takes 10 seconds.

Why is the second one for 5 seconds (30%) faster than the first one? No
logic to me.

Is there some faster way to execute this query?

Thank you,
Simon

[Erland Sommarskog]

simon (simon...@stud-moderna.si) writes:
> I have to update column name in table tblA with the same column in table
> tblB.
>
> UPDATE a
> SET a.name=b.NAME
> FROM tblaA a INNER JOIN tblB b
> ON a.ID=b.ID
>
> ID is PK, UNIQUE CLUSTERED INDEX in both tables.
>
> This update takes 15 seconds(36000 rows)
>
> IF I execute the same update just with RIGHT JOIN:
>
> UPDATE a
> SET a.name=b.NAME
> FROM tblaA a RIGHT JOIN tblB b
> ON a.ID=b.ID WHERE a.ID is not NULL
>
> I get the same update just that it takes 10 seconds.
>
> Why is the second one for 5 seconds (30%) faster than the first one? No
> logic to me.

If you did this as two consecutive queries, you had data in cache the
second time, which can explain why it went faster.

Or does this happen every time when you alternate between the queries?
Have you looked at the query plans to see if they are identical?
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

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

[simon]

This happen every time when I alternate between the queries.

I haven't look at query plans,and unfortunatelly, I don't have server here
at the moment to tell you something more about query plan.

Any idea or experience from the past?

Any better way to speed up the execution?

Thank you,
Simon

"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns96D76410A...@127.0.0.1...

[R.D]

simon
dont you think you have many null values and they are filtered with where
clause
My guess is that second one very selective while the first is not.
However see which type of join it is using. (hash,merge,loop) in the
execution plan.
Regards
R.S

[Erland Sommarskog]

simon (simon...@stud-moderna.si) writes:
> This happen every time when I alternate between the queries.
>
> I haven't look at query plans,and unfortunatelly, I don't have server here
> at the moment to tell you something more about query plan.

SQL Server has to scan one of the tables, and the different joins can
lead to different choices of which table to join, and which join
strategy.

> Any idea or experience from the past?
>
> Any better way to speed up the execution?

A non-clustered index on (ID, NAME) on both tables could help.