SQLを修正して修正前と結果が変わらないことを確認する

パフォーマンスチューニングをおこなう際に修正したSQLの取得結果が修正前と変わらないことを確かめるために調べました．

事前準備

例えばこんなテーブルとデータがあったとします．

CREATE TABLE SAMPLE_ROLL
(
    ID int,
    NAME nvarchar(20),
    PRIMARY KEY(ID)
);

CREATE TABLE SAMPLE_USER
(
    ID int,
    NAME nvarchar(20),
    ROLL_ID int,   
    PRIMARY KEY(ID),
    FOREIGN KEY(ROLL_ID)
      REFERENCES SAMPLE_ROLL(ID)
);

INSERT INTO SAMPLE_ROLL(ID, NAME) VALUES(1, 'ADMIN01');
INSERT INTO SAMPLE_ROLL(ID, NAME) VALUES(2, 'TEST01');
INSERT INTO SAMPLE_ROLL(ID, NAME) VALUES(3, 'TEST02');

INSERT INTO SAMPLE_USER(ID, NAME, ROLL_ID) VALUES(1, 'user01', 1);
INSERT INTO SAMPLE_USER(ID, NAME, ROLL_ID) VALUES(2, 'user02', 1);
INSERT INTO SAMPLE_USER(ID, NAME, ROLL_ID) VALUES(3, 'user03', 2);
INSERT INTO SAMPLE_USER(ID, NAME, ROLL_ID) VALUES(4, 'user04', 3);
INSERT INTO SAMPLE_USER(ID, NAME, ROLL_ID) VALUES(5, 'user05', 3);
INSERT INTO SAMPLE_USER(ID, NAME, ROLL_ID) VALUES(6, 'user06', 3);

あるSQLが遅いとします．

SELECT * FROM SAMPLE_USER u WHERE u.ROLL_ID IN (SELECT r.ID FROM SAMPLE_ROLL r WHERE r.NAME LIKE 'TEST%');

チューニング

INをEXISTSで書き直します．

SELECT * FROM SAMPLE_USER u WHERE EXISTS (SELECT * FROM SAMPLE_ROLL r WHERE r.NAME LIKE 'TEST%' and u.ROLL_ID = r.ID);

確認

修正前と修正後で取得結果が変わらないことを確かめるにはEXCEPTで差が無いことを確認します．

SELECT * FROM SAMPLE_USER u WHERE u.ROLL_ID IN (SELECT r.ID FROM SAMPLE_ROLL r WHERE r.NAME LIKE 'TEST%')
EXCEPT
SELECT * FROM SAMPLE_USER u WHERE EXISTS (SELECT * FROM SAMPLE_ROLL r WHERE r.NAME LIKE 'TEST%' and u.ROLL_ID = r.ID)

サンプル

一気通貫で確認したいかたは以下のsqlで．

BEGIN tran

CREATE TABLE SAMPLE_ROLL
(
    ID int,
    NAME nvarchar(20),
    PRIMARY KEY(ID)
);

CREATE TABLE SAMPLE_USER
(
    ID int,
    NAME nvarchar(20),
    ROLL_ID int,   
    PRIMARY KEY(ID),
    FOREIGN KEY(ROLL_ID)
      REFERENCES SAMPLE_ROLL(ID)
);

INSERT INTO SAMPLE_ROLL(ID, NAME) VALUES(1, 'ADMIN01');
INSERT INTO SAMPLE_ROLL(ID, NAME) VALUES(2, 'TEST01');
INSERT INTO SAMPLE_ROLL(ID, NAME) VALUES(3, 'TEST02');

INSERT INTO SAMPLE_USER(ID, NAME, ROLL_ID) VALUES(1, 'user01', 1);
INSERT INTO SAMPLE_USER(ID, NAME, ROLL_ID) VALUES(2, 'user02', 1);
INSERT INTO SAMPLE_USER(ID, NAME, ROLL_ID) VALUES(3, 'user03', 2);
INSERT INTO SAMPLE_USER(ID, NAME, ROLL_ID) VALUES(4, 'user04', 3);
INSERT INTO SAMPLE_USER(ID, NAME, ROLL_ID) VALUES(5, 'user05', 3);
INSERT INTO SAMPLE_USER(ID, NAME, ROLL_ID) VALUES(6, 'user06', 3);

SELECT * FROM SAMPLE_USER u WHERE u.ROLL_ID IN (SELECT r.ID FROM SAMPLE_ROLL r WHERE r.NAME LIKE 'TEST%')
EXCEPT
SELECT * FROM SAMPLE_USER u WHERE EXISTS (SELECT * FROM SAMPLE_ROLL r WHERE r.NAME LIKE 'TEST%' and u.ROLL_ID = r.ID)

ROLLBACK tran