Pchełki SQL: wiele kolumn i operator IN

https://xpil.eu/ec4

Dziś króciutka pchełka, która narodziła mi się całkiem niechcący.

Tym razem zamiast wstępów od razu lecimy z gęstym. Czytelnik zorientowany sobie poradzi, a Czytelnik niezorientowany prawdopodobnie zamknął artykuł już po przeczytaniu tytułu 😉

OK, lecimy. Najpierw tworzymy sobie testową tabelkę:

CREATE TABLE dbo.TestTable
    ( id INT IDENTITY(1, 1) NOT NULL
    , data1 VARCHAR(255) NULL
    , data2 VARCHAR(255) NULL
    , data3 VARCHAR(255) NULL
    )

Następnie wypełniamy tabelkę danymi:

INSERT  INTO dbo.TestTable
VALUES    ( 'Ala'  , 'ma'  , 'kota')
        , ( 'kot', 'to', 'niecnota')
        , ( 'zeżarł', 'jajeczko', 'i')
        , ( 'obsrał', 'pilota', 'od telewizora')
        , ( 'fajny', 'wierszyk', 'tylko')
        , ( 'kompletnie się', 'nie', 'rymuje')

No i teraz chcemy wyszukać wszystkie rekordy zawierające w jednej z kolumn data1, data2, data3 przynajmniej jedno ze słówek 'jajeczko', 'wierszyk' lub 'kalafior':

SELECT *
FROM dbo.TestTable tt 
WHERE  tt.data1 IN ('jajeczko', 'wierszyk', 'kalafior')
    OR tt.data2 IN ('jajeczko', 'wierszyk', 'kalafior')
    OR tt.data3 IN ('jajeczko', 'wierszyk', 'kalafior')

Wszystko pięknie, tylko, cholera, niepotrzebnie mamy skopiowane te same wartości trzy razy. Takie zapytanie jest nieeleganckie i błędogenne: jeżeli chcemy zastąpić 'kalafior' 'marchewką', musimy pamiętać, aby zrobić to w trzech miejscach.

Co więc zrobić?

Spójrzmy:

SELECT *
FROM dbo.TestTable tt
WHERE EXISTS (
    SELECT b.Name
    FROM ( VALUES ( 'jajeczko'), ( 'wierszyk'), ( 'kalafior') ) AS b (Name)
    WHERE b.Name IN ( tt.data1, tt.data2, tt.data3)
)

Co się tu wydarzyło?

Odwróciliśmy kota (nomen-omen) ogonem: zamiast sprawdzać, czy kolumna data1 lub data2 lub data3 zawiera jedną z trzech wartości, sprawdzamy, czy któraś z tych wartości zawiera się wśród kolumn data1, data2, data3. Różnica jest subtelna, ale istotna: dzięki temu unikamy wielokrotnego copy-paste na jajeczku, wierszyku i kalafiorze.

Zalety: zwięzłość kodu.

Wady: kod wykona się odrobinę wolniej, ponieważ - jeżeli przyjrzymy się planom wykonania zapytań - okaże się, że drugie zapytanie trochę więcej czasu spędzi na operacji HASH-JOIN, a trochę mniej na INDEX-SEEK (założenie jest takie, że wszystkie trzy kolumny są częścią jednego indeksu). Przy 60,000 rekordów różnica w wydajności wynosi około 15% na niekorzyść drugiego zapytania.

Czy to dużo, czy mało - o tym już, Czytelniku, musisz zadecydować sam. Według mnie poświęcenie 15% wydajności na rzecz lepszego kodu to opłacalna wymiana, o ile tylko nie ma potrzeby na absolutnie maksymalne wyśrubowanie wydajności.

https://xpil.eu/ec4