CODEWITHSUNDEEP
sqldb2
user1582625
user1582625

Reputation: 303

Improving performance SQL

Any way to customize below SQL to improve performance where same inner query is running for more than once?

DELETE FROM interactionrel 
WHERE  ( from_interact_id IN(SELECT interaction_id 
                             FROM   interaction 
                             WHERE  instance_pk = ?) ) 
        OR ( to_interact_id IN(SELECT interaction_id 
                               FROM   interaction 
                               WHERE  instance_pk = ?) )

Upvotes: 0

Views: 89

Answers (2)

Clockwork-Muse
Clockwork-Muse

Reputation: 13056

You might try this - although you're going to need to run this against an optimizer:

DELETE FROM interactionrel a
WHERE EXISTS (SELECT * 
              FROM interaction b
              WHERE b.instance_pl = ?
                    AND (b.interaction_id = a.from_interact_id
                         OR b.interaction_id = a.to_interact_id))

Upvotes: 2

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20745

DELETE il 
FROM   interactionrel il, 
       interaction 
WHERE  ( interaction.interaction_id = il.from_interact_id 
          OR interaction.interaction_id = il.to_interact_id ) 
       AND interaction.instance_pk = ?

Upvotes: 1

Related Questions