Imran Azad
Imran Azad

Reputation: 1048

SQL Server Update with Sub Query Performance

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

Answers (1)

Gordon Linoff
Gordon Linoff

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

Related Questions