Jak korzystać z trybu wsadowego z UNPIVOT (łączenie w pętli)?

Mam zapytanie o następującej formie:

SELECT ...
FROM ColumnstoreTable cs
CROSS APPLY (
    SELECT *
    FROM (VALUES
        ('A', cs.DataA)
        , ('B', cs.DataB)
        , ('C', cs.DataC)
    ) x(Col0, Col1)
) someValues

Pobiera każdy wiersz z podzapytania wspieranego przez Columnstore ( ColumnstoreTable) i mnoży te wiersze. Jest to zasadniczo UNPIVOT. Rzeczywiste zapytanie jest większe niż to. Ta część zapytania jest przekazywana do innego przetwarzania.

Problem polega na tym, że CROSS APPLYjest to realizowane jako połączenie pętli, co jest rozsądnym wyborem. Niestety sprzężenia pętli nie obsługują trybu wsadowego.

Ta część zapytania ma bardzo duże znaczenie dla wydajności i podejrzewam, że uruchomienie go w trybie wsadowym może być bardzo korzystne dla wydajności.

Jak mogę przepisać to zapytanie, aby nie przechodzić z trybu wsadowego?

Próbowałem użyć tabeli tymczasowej zamiast VALUES, ale to nie zmieniło faktu, że nie ma warunku łączenia równości dla łączenia mieszającego.

Jednym z podejść może być użycie tabeli #temp dla wartości, a także wprowadzenie fikcyjnej kolumny equijoin, aby umożliwić połączenie mieszające. Na przykład:

-- Create a #temp table with a dummy column to match the hash join
-- and the actual column you want
CREATE TABLE #values (dummy INT NOT NULL, Col0 CHAR(1) NOT NULL)
INSERT INTO #values (dummy, Col0)
VALUES (0, 'A'),
        (0, 'B'),
        (0, 'C')
GO

-- A similar query, but with a dummy equijoin condition to allow for a hash join
SELECT v.Col0, 
    CASE v.Col0 
        WHEN 'A' THEN cs.DataA 
        WHEN 'B' THEN cs.DataB 
        WHEN 'C' THEN cs.DataC
        END AS Col1
FROM ColumnstoreTable cs
JOIN #values v
    -- Join your dummy column to any numeric column on the columnstore,
    -- multiplying that column by 0 to ensure a match to all #values
    ON v.dummy = cs.DataA * 0

Plan wydajności i zapytań

Takie podejście daje plan zapytania podobny do następującego, a dopasowanie mieszające jest wykonywane w trybie wsadowym:

Jeśli mam wymienić SELECToświadczenie z SUMtego CASErachunku w celu uniknięcia konieczności strumieniowo wszystkie te wiersze do konsoli, a następnie uruchomić kwerendę na prawdziwym 100mm rząd columnstore tabeli Mam leżące wokół, widzę dość dobra wydajność wygenerować wymaganą 300mm wydziwianie:

CPU time = 33803 ms, elapsed time = 4363 ms.

Rzeczywisty plan pokazuje dobrą równoległość łączenia mieszającego.

Uwagi dotyczące równoległości łączenia mieszającego, gdy wszystkie wiersze mają tę samą wartość

Wydajność tego zapytania zależy w dużej mierze od tego, czy każdy wątek po stronie sondy sprzężenia ma dostęp do pełnej tabeli mieszającej (w przeciwieństwie do wersji podzielonej na skróty, która mapowałaby wszystkie wiersze na pojedynczy wątek, biorąc pod uwagę, że istnieje tylko jedna odrębna wartość dla dummykolumny).

Na szczęście jest to prawda w tym przypadku (jak widać po braku Parallelismoperatora po stronie sondy) i powinno być niezawodnie prawdziwe, ponieważ tryb wsadowy tworzy pojedynczą tabelę skrótów, która jest współużytkowana przez wątki. Dlatego każdy wątek może pobrać swoje wiersze z Columnstore Index Scani dopasować je do tej wspólnej tabeli skrótów. W SQL Server 2012 ta funkcja była znacznie mniej przewidywalna, ponieważ wyciek spowodował, że operator zrestartował się w trybie wiersza, tracąc zarówno korzyści z trybu wsadowego, jak i wymagając Repartition Streamsoperatora po stronie sondy złącza, co w tym przypadku spowodowałoby przekrzywienie wątku . Zezwolenie na pozostawanie wycieków w trybie wsadowym jest istotnym ulepszeniem programu SQL Server 2014.

Według mojej wiedzy, tryb wierszy nie ma tej wspólnej funkcji tabeli skrótów. Jednak w niektórych przypadkach, zwykle z szacunkową liczbą mniejszą niż 100 wierszy po stronie kompilacji, SQL Server utworzy osobną kopię tabeli skrótów dla każdego wątku (identyfikowalną przez wprowadzenie Distribute Streamsdo sprzężenia skrótu). Może to być bardzo wydajne, ale jest znacznie mniej niezawodne niż tryb wsadowy, ponieważ zależy od twoich oszacowań liczności, a SQL Server próbuje oszacować korzyści w porównaniu z kosztem zbudowania pełnej kopii tabeli skrótów dla każdego wątku.

UNION ALL: prostsza alternatywa

Paul White wskazał, że inną i potencjalnie prostszą opcją byłoby UNION ALLpołączenie wierszy dla każdej wartości. Jest to prawdopodobnie twój najlepszy zakład, zakładając, że łatwo jest zbudować ten SQL dynamicznie. Na przykład:

SELECT 'A' AS Col0, c.DataA AS Col1
FROM ColumnstoreTable c
UNION ALL
SELECT 'B' AS Col0, c.DataB AS Col1
FROM ColumnstoreTable c
UNION ALL
SELECT 'C' AS Col0, c.DataC AS Col1
FROM ColumnstoreTable c

Daje to również plan, który może wykorzystywać tryb wsadowy i zapewnia jeszcze lepszą wydajność niż pierwotna odpowiedź. (Chociaż w obu przypadkach wydajność jest na tyle szybka, że ​​każde wybranie lub zapisanie danych do tabeli szybko staje się wąskim gardłem.) UNION ALLPodejście to pozwala także uniknąć grania w gry, takie jak mnożenie przez 0. Czasami najlepiej jest myśleć prosto!

CPU time = 8673 ms, elapsed time = 4270 ms.