Execution plan for update

Hi,
Please check two statements below, one is Update, and another is Select
statement based on that Update. Why they have different execution plan?
Problem is that Update statement first joins two titles tables instead to
first join titles and sales as Select does. It makes me problem on large
tables as Update statement never ends (while Select finishes in few
seconds).
I tried to use join hints but Update statement does not allow them.
I'm using SS2000 Enterprise edition SP3 on Win2000 Advanced Server SP4
Thanks in advance
Nikola Milic
--UPDATE StmtText
UPDATE pubs.dbo.titles
SET royalty = STT.royalty -- meaningless, just for test
--SELECT T.*
FROM pubs.dbo.titles T
JOIN(
SELECT S.title_id, S.qty, T.royalty
FROM(
SELECT DISTINCT title_id, qty
FROM pubs.dbo.sales
)S
JOIN(
SELECT DISTINCT title_id, royalty
FROM pubs.dbo.titles
)T
ON S.title_id = T.title_id
--OR S.qty = T.royalty -- meaningless, just for test
)STT
ON T.title_id = STT.title_id
--UPDATE ExecutionPlan Text
|--Clustered Index
Update(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind]),
SET:([titles].[royalty]=[titles].[royalty]))
|--Top(ROWCOUNT est 0)
|--Sort(DISTINCT ORDER BY:([Bmk1000] ASC))
|--Nested Loops(Inner Join, OUTER
REFERENCES:([titles].[title_id]))
|--Nested Loops(Inner Join, OUTER
REFERENCES:([titles].[title_id]))
| |--Clustered Index
Scan(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind]))
| |--Clustered Index
S(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind] AS [T]),
SEEK:([T].[title_id]=[titles].[title_id]) ORDERED FORWARD)
|--Index
S(OBJECT:([pubs].[dbo].[sales].[titleidind]),
SEEK:([sales].[title_id]=[titles].[title_id]) ORDERED FORWARD)
--SELECT StmtText
SELECT T.*
FROM pubs.dbo.titles T
JOIN(
SELECT S.title_id, S.qty, T.royalty
FROM(
SELECT DISTINCT title_id, qty
FROM pubs.dbo.sales
)S
JOIN(
SELECT DISTINCT title_id, royalty
FROM pubs.dbo.titles
)T
ON S.title_id = T.title_id
--OR S.qty = T.royalty -- meaningless, just for test
)STT
ON T.title_id = STT.title_id
--SELECT ExecutionPlan Text
|--Hash Match(Inner Join, HASH:([titles].[title_id])=([sales].[title_id]),
RESIDUAL:([sales].[title_id]=[titles].[title_id]))
|--Index Scan(OBJECT:([pubs].[dbo].[titles].[titleind]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([sales].[title_id]))
|--Sort(DISTINCT ORDER BY:([sales].[title_id] ASC, [sales].[qty]
ASC))
| |--Clustered Index
Scan(OBJECT:([pubs].[dbo].[sales].[UPKCL_sales]))
|--Clustered Index
S(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind] AS [T]),
SEEK:([T].[title_id]=[sales].[title_id]) ORDERED FORWARD)Hi Nikola,
The first impression I have when I look at your SQL is that you are
overcomplicating things. Are these actual SQL statements you use or are
these just samples?
Your SELECT could be rewritten as follows with the same result ifI'm not
mistaken:
SELECT T.*
FROM pubs.dbo.titles T
JOIN pubs.dbo.sales STT
ON T.title_id = STT.title_id
HTH
Karl Gram
"Nikola Milic" <hotmnikola@.hotmail.com> wrote in message
news:O$HwfO9WFHA.1468@.tk2msftngp13.phx.gbl...
> Hi,
> Please check two statements below, one is Update, and another is Select
> statement based on that Update. Why they have different execution plan?
> Problem is that Update statement first joins two titles tables instead to
> first join titles and sales as Select does. It makes me problem on large
> tables as Update statement never ends (while Select finishes in few
> seconds).
> I tried to use join hints but Update statement does not allow them.
> I'm using SS2000 Enterprise edition SP3 on Win2000 Advanced Server SP4
> Thanks in advance
> Nikola Milic
>
>
> --UPDATE StmtText
> UPDATE pubs.dbo.titles
> SET royalty = STT.royalty -- meaningless, just for test
> --SELECT T.*
> FROM pubs.dbo.titles T
> JOIN(
> SELECT S.title_id, S.qty, T.royalty
> FROM(
> SELECT DISTINCT title_id, qty
> FROM pubs.dbo.sales
> )S
> JOIN(
> SELECT DISTINCT title_id, royalty
> FROM pubs.dbo.titles
> )T
> ON S.title_id = T.title_id
> --OR S.qty = T.royalty -- meaningless, just for test
> )STT
> ON T.title_id = STT.title_id
> --UPDATE ExecutionPlan Text
> |--Clustered Index
> Update(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind]),
> SET:([titles].[royalty]=[titles].[royalty]))
> |--Top(ROWCOUNT est 0)
> |--Sort(DISTINCT ORDER BY:([Bmk1000] ASC))
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([titles].[title_id]))
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([titles].[title_id]))
> | |--Clustered Index
> Scan(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind]))
> | |--Clustered Index
> S(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind] AS [T]),
> SEEK:([T].[title_id]=[titles].[title_id]) ORDERED FORWARD)
> |--Index
> S(OBJECT:([pubs].[dbo].[sales].[titleidind]),
> SEEK:([sales].[title_id]=[titles].[title_id]) ORDERED FORWARD)
>
>
> --SELECT StmtText
> SELECT T.*
> FROM pubs.dbo.titles T
> JOIN(
> SELECT S.title_id, S.qty, T.royalty
> FROM(
> SELECT DISTINCT title_id, qty
> FROM pubs.dbo.sales
> )S
> JOIN(
> SELECT DISTINCT title_id, royalty
> FROM pubs.dbo.titles
> )T
> ON S.title_id = T.title_id
> --OR S.qty = T.royalty -- meaningless, just for test
> )STT
> ON T.title_id = STT.title_id
>
> --SELECT ExecutionPlan Text
> |--Hash Match(Inner Join,
> HASH:([titles].[title_id])=([sales].[title_id]),
> RESIDUAL:([sales].[title_id]=[titles].[title_id]))
> |--Index Scan(OBJECT:([pubs].[dbo].[titles].[titleind]))
> |--Nested Loops(Inner Join, OUTER REFERENCES:([sales].[title_id]))
> |--Sort(DISTINCT ORDER BY:([sales].[title_id] ASC,
> [sales].[qty] ASC))
> | |--Clustered Index
> Scan(OBJECT:([pubs].[dbo].[sales].[UPKCL_sales]))
> |--Clustered Index
> S(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind] AS [T]),
> SEEK:([T].[title_id]=[sales].[title_id]) ORDERED FORWARD)
>|||UPDATE Titles
SET royalty
= (SELECT S.royalty
FROM Sales AS S
WHERE Titles.title_id = S.title_id);
Avoid the unpredictable proprietary syntax needless complexity.|||Many thanks for your reply,
I made those SQL statements from sample database pubs and they actually
work, you can test it in your SQL server. They are not overcomplicated as
they have real meaning in my real database. Idea is to make equal data from
two tables.
My question is why Update statement makes different execution plan from
Select statement? Please note that I'm using here SQL Server syntax to make
Update statement.
Regards
Nikola
"Karl Gram" <karl@.gramonline.nl> wrote in message
news:%2337%23SL%23WFHA.2796@.TK2MSFTNGP09.phx.gbl...
> Hi Nikola,
> The first impression I have when I look at your SQL is that you are
> overcomplicating things. Are these actual SQL statements you use or are
> these just samples?
> Your SELECT could be rewritten as follows with the same result ifI'm not
> mistaken:
> SELECT T.*
> FROM pubs.dbo.titles T
> JOIN pubs.dbo.sales STT
> ON T.title_id = STT.title_id
> --
> HTH
> Karl Gram
>
> "Nikola Milic" <hotmnikola@.hotmail.com> wrote in message
> news:O$HwfO9WFHA.1468@.tk2msftngp13.phx.gbl...
>|||Many thanks for your reply,
I made those SQL statements from sample database pubs and they actually
work, you can test it in your SQL server. They are not overcomplicated as
they have real meaning in my real database. Idea is to make equal data from
two tables.
Also, please note that I'm using here SQL Server syntax to make Update
statement. My question is why Update statement makes different execution
plan from Select statement?
Your SQL does not work. Table S does not have royalty column.
Regards
Nikola
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1116458847.966216.212830@.z14g2000cwz.googlegroups.com...
> UPDATE Titles
> SET royalty
> = (SELECT S.royalty
> FROM Sales AS S
> WHERE Titles.title_id = S.title_id);
> Avoid the unpredictable proprietary syntax needless complexity.
>