CODEWITHSUNDEEP
sqloracle-databaseperformancetriggers
Patrick Rode
Patrick Rode

Reputation: 339

Which SQL is faster? Select and then Update VS. Update with inner select

I am using an Oracle Database. See these 2 examples (doing exactly the same):

1.

CREATE OR REPLACE TRIGGER LAST_CHANGE_TRIGGER
BEFORE INSERT
    ON TABLE_X_HAVING_ITEM_ID
FOR EACH ROW

DECLARE
    style_id NUMBER(18);

BEGIN      
    -- 1. get style_id
    SELECT
        ITEM.STYLE_ID
    INTO
        style_id
    FROM
        ITEM
    WHERE
        ITEM.ITEM_ID = :NEW.ITEM_ID;

    -- 2. set last_modified
    UPDATE
        STYLE
    SET
        STYLE.LAST_MODIFIED = SYSDATE
    WHERE
        STYLE.STYLE_ID = style_id;
END;
/

2.

CREATE OR REPLACE TRIGGER LAST_CHANGE_TRIGGER
BEFORE INSERT
    ON TABLE_X_HAVING_ITEM_ID
FOR EACH ROW

BEGIN
    UPDATE
        STYLE
    SET
        STYLE.LAST_MODIFIED = SYSDATE
    WHERE
        STYLE.STYLE_ID = (
            SELECT
                ITEM.STYLE_ID
            FROM
                ITEM
            WHERE
                ITEM.ITEM_ID = :NEW.ITEM_ID
        );
END;
/

I heard that the 2. one would be faster. I think the 1. one is easier to understand though (more straight forward). Can you explain/proof that 2. is faster? If you can do so, is this only the case for oracle?

Upvotes: 0

Views: 1536

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270483

The second should be faster, because you don't have two round trips to the database.

It also allows multiple rows to be updated at the same time, in case there are multiple matches.

That said, I don't see why this trigger is necessary. You can just look up the style when querying the data. Storing redundant data -- particularly to a reference table -- seems like a bad idea.

Upvotes: 4

Related Questions