|
Sigurd Hermansen wrote:
> In SAS SQL, the EXISTS query in general, and specifically the second
> subquery in the UPDATE query, take relatively a long time to execute. I
> prefer an UPDATE query that does not require an EXISTS subquery:
>
> proc sql UNDO_POLICY=NONE;
> update test r
> set val=coalesce((
> select k.val from
> transUpd k
> where r.ID=k.ID
> ),r.val)
> ;
> quit;
>
> The EXISTS clause tends to work much faster in Oracle. I believe that the
> COALESCE() accomplishes the same purpose and will work much faster.
It looks elegant, but it takes a lot more time to execute than the
update with double subquery and as one can see the coalesce-version
updates each and every row.
(Table A and its index table are copies of the original Rakennus & its
index table.)
Updating all rows instead of just the 7 that have changes to be applied
didn't take terribly long, though. I ran both versions with the STIMER
option. See log below.
Not a great difference in execution time. In fact updating 7 rows took
more cpu time than updating all rows. How about that?
Still no use of the index on rakennustunnus that both tables have. Is
the index useless here? Why?
NOTE: Statement transforms to:
update LOCAL.A R
set kerrosala = COALESCE(
( select K.kerrosala
from LOCAL.RAKENNUS_KERROSALAN_KORJAUKSET K
where R.rakennustunnus=K.rakennustunnus
), R.kerrosala);
WARNING: The SQL option UNDO_POLICY=REQUIRED is not in effect. If an
error is detected when processing this UPDATE statement, that error will
not cause the entire statement to fail.
NOTE: 2788285 rows were updated in LOCAL.A.
NOTE: SQL Statement used:
real time 3:54.14
cpu time 2:07.37
Statement lines removed, because the feedback option prints a second,
transformed version of the statement ....
WARNING: A value expression of the SET clause references the data set
being updated.
NOTE: Statement transforms to:
update LOCAL.RAKENNUS R
set kerrosala =
( select K.kerrosala
from LOCAL.RAKENNUS_KERROSALAN_KORJAUKSET K
where R.rakennustunnus=K.rakennustunnus
)
where exists
( select *
from LOCAL.RAKENNUS_KERROSALAN_KORJAUKSET K
where R.rakennustunnus=K.rakennustunnus
);
WARNING: The SQL option UNDO_POLICY=REQUIRED is not in effect. If an
error is detected when processing this UPDATE statement, that error will
not cause the entire statement to fail.
NOTE: 7 rows were updated in LOCAL.RAKENNUS.
NOTE: SQL Statement used:
real time 3:46.03
cpu time 3:46.03
--
Mr. Arjen Raateland
Finnish Environment Institute SYKE
Research Dept.
SAS Support
phone +358 9 4030 0350
| 
