Reputation: 1048
Why does this query using a temporary table for a list of ids and then subsequent updates run quicker
DECLARE @temp TABLE
(
id int
);
INSERT INTO
@temp
select a.Id from aa a
inner join bb b on b.id = a.source_id
inner join cc c on c.document_id = a.id
update aa set updated_at = GETDATE()
where id in (select * from @temp)
Compared to this UPDATE
query which uses a subquery to select a list of ids which takes much longer.
update aa set updated_at = GETDATE()
where id in (select a.Id from aa a
inner join bb b on b.id = a.source_id
inner join cc c on c.document_id = a.id)
Upvotes: 1
Views: 41
Reputation: 1269793
Why are you repeating the aa
table in the subquery? Does this do the same thing?
update aa
set updated_at = GETDATE()
where a.source_id in (select b.Id
from b.id join
cc c
on c.document_id = a.id
);
There may be circumstances where they are not the same, but I am guessing this is the logic.
Also, you can write this without a subquery at all:
update a
set updated_at = GETDATE()
from aa a inner join
bb b
on b.id = a.source_id inner join
cc c
on c.document_id = a.id;
I would expect this to have good performance -- unless the joins result in many repeated records for a given row in aa
.
Upvotes: 1