Jaki jest najskuteczniejszy sposób uzyskania minimum wielu kolumn w SQL Server 2005?

Jestem w sytuacji, w której chcę uzyskać minimalną wartość z 6 kolumn.

Znalazłem do tej pory trzy sposoby, aby to osiągnąć, ale mam obawy dotyczące wydajności tych metod i chciałbym wiedzieć, które z nich byłyby lepsze.

Pierwszą metodą jest użycie dużej instrukcji . Oto przykład z 3 kolumnami, na podstawie przykładu w powyższym linku. Mój opis przypadku byłby znacznie dłuższy, ponieważ będę patrzył na 6 kolumn.

Select Id,
       Case When Col1 <= Col2 And Col1 <= Col3 Then Col1
            When Col2 <= Col3 Then Col2 
            Else Col3
            End As TheMin
From   MyTable

Drugą opcją jest użycie UNIONoperatora z wieloma instrukcjami select . Umieściłbym to w UDF, który akceptuje parametr Id.

select Id, dbo.GetMinimumFromMyTable(Id)
from MyTable

i

select min(col)
from
(
    select col1 [col] from MyTable where Id = @id
    union all
    select col2 from MyTable where Id = @id
    union all
    select col3 from MyTable where Id = @id
) as t

A trzecią opcją, którą znalazłem, było użycie operatora UNPIVOT , który do tej pory nawet nie istniał

with cte (ID, Col1, Col2, Col3)
as
(
    select ID, Col1, Col2, Col3
    from TestTable
)
select cte.ID, Col1, Col2, Col3, TheMin from cte
join
(
    select
        ID, min(Amount) as TheMin
    from 
        cte 
        UNPIVOT (Amount for AmountCol in (Col1, Col2, Col3)) as unpvt
    group by ID
) as minValues
on cte.ID = minValues.ID

Ze względu na rozmiar i częstotliwość przeszukiwania i aktualizowania tej tabeli martwię się o wpływ wydajności tych zapytań na bazę danych.

To zapytanie zostanie faktycznie użyte w połączeniu z tabelą zawierającą kilka milionów rekordów, jednak zwracane rekordy będą zmniejszane do około stu rekordów jednocześnie. Będzie uruchamiany wiele razy w ciągu dnia, a 6 kolumn, o które pytam, są często aktualizowane (zawierają dzienne statystyki). Nie sądzę, że w 6 kolumnach, o które pytam, są jakieś indeksy.

Która z tych metod jest lepsza pod względem wydajności, gdy próbujesz uzyskać minimum wielu kolumn? A może istnieje inna lepsza metoda, o której nie wiem?

Używam SQL Server 2005

Przykładowe dane i wyniki

Jeśli moje dane zawierały takie rekordy:

Id Col1 Col2 Col3 Col4 Col5 Col6
1 3 4 0 2 1 5
2 2 6 10 5 7 9
3 1 1 2 3 4 5
4 9 5 4 6 8 9

Wynik końcowy powinien być

Wartość identyfikacyjna
1 0
2 2
3 1
4 4

Przetestowałem wydajność wszystkich 3 metod i oto, co znalazłem:

• 1 rekord: brak zauważalnej różnicy
• 10 rekordów: Brak zauważalnej różnicy
• 1000 rekordów: brak zauważalnej różnicy
• 10 000 rekordów: UNIONpodkwerenda była nieco wolniejsza. CASE WHENZapytanie jest trochę szybciej niż UNPIVOTjeden.
• 100 000 rekordów: UNIONpodzapytanie jest znacznie wolniejsze, ale UNPIVOTzapytanie staje się nieco szybsze niż CASE WHENzapytanie
• 500 000 rekordów: UNIONpodzapytanie jest nadal znacznie wolniejsze, ale UNPIVOTstaje się znacznie szybsze niż CASE WHENzapytanie

Wydaje się, że wyniki końcowe są

• Przy mniejszych zestawach rekordów wydaje się, że różnica nie ma znaczenia. Używaj tego, co najłatwiejsze do odczytania i utrzymania.

• Gdy zaczniesz wchodzić w większe zestawy rekordów, UNION ALLpodzapytanie zaczyna słabo działać w porównaniu do pozostałych dwóch metod.

• Do CASErachunku wykonuje najlepszy Aż do pewnego punktu (w moim przypadku, około 100k wierszy), a tym momencie UNPIVOTzapytania staje zapytanie o najlepszych wynikach

Rzeczywista liczba, przy której jedno zapytanie staje się lepsze od drugiego, prawdopodobnie ulegnie zmianie w wyniku sprzętu, schematu bazy danych, danych i bieżącego obciążenia serwera, więc pamiętaj o przetestowaniu na własnym systemie, jeśli obawiasz się o wydajność.

Przeprowadziłem też kilka testów, używając odpowiedzi Mikaela ; był jednak wolniejszy niż wszystkie 3 inne metody wypróbowane tutaj dla większości rozmiarów zestawów rekordów. Jedynym wyjątkiem było to, że wypadło to lepiej niż UNION ALLzapytanie o bardzo duże rozmiary zestawów rekordów. Podoba mi się fakt, że oprócz najmniejszej wartości pokazuje nazwę kolumny.

Nie jestem dba, więc mogłem nie zoptymalizować swoich testów i coś przeoczyłem. Testowałem z rzeczywistymi danymi na żywo, więc mogło to mieć wpływ na wyniki. Próbowałem to wyjaśnić, uruchamiając każde zapytanie kilka razy, ale nigdy nie wiadomo. Byłbym zdecydowanie zainteresowany, gdyby ktoś napisał czysty test na ten temat i podzielił się swoimi wynikami.

Nie wiem, co jest najszybsze, ale możesz spróbować czegoś takiego.

declare @T table
(
  Col1 int,
  Col2 int,
  Col3 int,
  Col4 int,
  Col5 int,
  Col6 int
)

insert into @T values(1, 2, 3, 4, 5, 6)
insert into @T values(2, 3, 1, 4, 5, 6)

select T4.ColName, T4.ColValue
from @T as T1
  cross apply (
                select T3.ColValue, T3.ColName
                from (
                       select row_number() over(order by T2.ColValue) as rn,
                              T2.ColValue,
                              T2.ColName
                       from (
                              select T1.Col1, 'Col1' union all
                              select T1.Col2, 'Col2' union all
                              select T1.Col3, 'Col3' union all
                              select T1.Col4, 'Col4' union all
                              select T1.Col5, 'Col5' union all
                              select T1.Col6, 'Col6'
                            ) as T2(ColValue, ColName)
                     ) as T3
                where T3.rn = 1
              ) as T4

Wynik:

ColName ColValue
------- -----------
Col1    1
Col3    1

Jeśli nie jesteś zainteresowany, która kolumna ma wartość minimalną, możesz jej użyć.

declare @T table
(
  Id int,
  Col1 int,
  Col2 int,
  Col3 int,
  Col4 int,
  Col5 int,
  Col6 int
)

insert into @T
select 1,        3,       4,       0,       2,       1,       5 union all
select 2,        2,       6,      10,       5,       7,       9 union all
select 3,        1,       1,       2,       3,       4,       5 union all
select 4,        9,       5,       4,       6,       8,       9

select T.Id, (select min(T1.ColValue)
              from (
                      select T.Col1 union all
                      select T.Col2 union all
                      select T.Col3 union all
                      select T.Col4 union all
                      select T.Col5 union all
                      select T.Col6
                    ) as T1(ColValue)
             ) as ColValue
from @T as T

Uproszczone zapytanie przestawne.

select Id, min(ColValue) as ColValue
from @T
unpivot (ColValue for Col in (Col1, Col2, Col3, Col4, Col5, Col6)) as U
group by Id

Dodaj utrwaloną kolumnę obliczeniową, która używa CASE instrukcji do wykonania potrzebnej logiki.

Minimalna wartość będzie wtedy zawsze efektywnie dostępna, gdy trzeba wykonać sprzężenie (lub cokolwiek innego) na podstawie tej wartości.

Wartość będzie przeliczana za każdym razem, gdy zmieni się dowolna z wartości źródłowych ( INSERT/ UPDATE/ MERGE). Nie mówię, że to niekoniecznie najlepsze rozwiązanie dla obciążenia, ja po prostu zaoferować go jako na rozwiązanie, podobnie jak inne odpowiedzi. Tylko OP może określić, który jest najlepszy dla obciążenia.

Oświadczenie o sprawie na 6 dat. Aby zrobić mniej, skopiuj prawdziwą gałąź z pierwszej instrukcji przypadku. Najgorszy przypadek to, gdy Data1 jest najniższą wartością, najlepszy przypadek to, gdy Data6 jest najniższą wartością, więc wpisz najbardziej prawdopodobną datę w Date6. Napisałem to z powodu ograniczeń kolumn obliczeniowych.

CASE WHEN Date1 IS NULL OR Date1 > Date2 THEN
        CASE WHEN Date2 IS NULL OR Date2 > Date3 THEN
            CASE WHEN Date3 IS NULL OR Date3 > Date4 THEN
                CASE WHEN Date4 IS NULL OR Date4 > Date5 THEN
                    CASE WHEN Date5 IS NULL OR Date5 > Date6 THEN
                        Date6
                    ELSE
                        Date5
                    END
                ELSE
                    CASE WHEN Date4 IS NULL OR Date4 > Date6 THEN
                        Date6
                    ELSE
                        Date4
                    END
                END
            ELSE
                CASE WHEN Date3 IS NULL OR Date3 > Date5 THEN
                    CASE WHEN Date5 IS NULL OR Date5 > Date6 THEN
                        Date6
                    ELSE
                        Date5
                    END
                ELSE
                    CASE WHEN Date3 IS NULL OR Date3 > Date6 THEN
                        Date6
                    ELSE
                        Date3
                    END
                END
            END
        ELSE
            CASE WHEN Date2 IS NULL OR Date2 > Date4 THEN
                CASE WHEN Date4 IS NULL OR Date4 > Date5 THEN
                    CASE WHEN Date5 IS NULL OR Date5 > Date6 THEN
                        Date6
                    ELSE
                        Date5
                    END
                ELSE
                    CASE WHEN Date4 IS NULL OR Date4 > Date5 THEN
                        CASE WHEN Date5 IS NULL OR Date5 > Date6 THEN
                            Date6
                        ELSE
                            Date5
                        END
                    ELSE
                        CASE WHEN Date4 IS NULL OR Date4 > Date6 THEN
                            Date6
                        ELSE
                            Date4
                        END
                    END
                END
            ELSE
                CASE WHEN Date2 IS NULL OR Date2 > Date5 THEN
                    CASE WHEN Date5 IS NULL OR Date5 > Date6 THEN
                        Date6
                    ELSE
                        Date5
                    END
                ELSE
                    CASE WHEN Date2 IS NULL OR Date2 > Date6 THEN
                        Date6
                    ELSE
                        Date2
                    END
                END
            END
        END
ELSE
    CASE WHEN Date1 IS NULL OR Date1 > Date3 THEN
        CASE WHEN Date3 IS NULL OR Date3 > Date4 THEN
            CASE WHEN Date4 IS NULL OR Date4 > Date5 THEN
                CASE WHEN Date5 IS NULL OR Date5 > Date6 THEN
                    Date6
                ELSE
                    Date5
                END
            ELSE
                CASE WHEN Date4 IS NULL OR Date4 > Date6 THEN
                    Date6
                ELSE
                    Date4
                END
            END
        ELSE
            CASE WHEN Date3 IS NULL OR Date3 > Date5 THEN
                CASE WHEN Date5 IS NULL OR Date5 > Date6 THEN
                    Date6
                ELSE
                    Date5
                END
            ELSE
                CASE WHEN Date3 IS NULL OR Date3 > Date6 THEN
                    Date6
                ELSE
                    Date3
                END
            END
        END
    ELSE
        CASE WHEN Date1 IS NULL OR Date1 > Date4 THEN
            CASE WHEN Date4 IS NULL OR Date4 > Date5 THEN
                CASE WHEN Date5 IS NULL OR Date5 > Date6 THEN
                    Date6
                ELSE
                    Date5
                END
            ELSE
                CASE WHEN Date4 IS NULL OR Date4 > Date6 THEN
                    Date6
                ELSE
                    Date4
                END
            END
        ELSE
            CASE WHEN Date1 IS NULL OR Date1 > Date5 THEN
                CASE WHEN Date5 IS NULL OR Date5 > Date6 THEN
                    Date6
                ELSE
                    Date5
                END
            ELSE
                CASE WHEN Date1 IS NULL OR Date1 > Date6 THEN
                    Date6
                ELSE
                    Date1
                END
            END
        END
    END
END

Jeśli natrafiłeś na tę stronę, chcąc po prostu porównać daty i nie martwisz się wydajnością ani kompatybilnością, możesz użyć Konstruktora wartości tabeli, z którego można korzystać wszędzie tam, gdzie dozwolone są podselekcje (SQL Server 2008 i nowsze wersje):

Lowest =    
(
    SELECT MIN(TVC.d) 
    FROM 
    (
        VALUES
            (Date1), 
            (Date2), 
            (Date3), 
            (Date4), 
            (Date5), 
            (Date6)
    ) 
    AS TVC(d)
)

Twoje caseoświadczenie nie jest skuteczne. Robisz 5 porównań w najgorszym przypadku i 2 w najlepszym przypadku; podczas gdy znalezienie minimum npowinno zrobić co najwyżej n-1porównania.

Dla każdego wiersza porównuje się średnio 3,5 zamiast 2. W związku z tym zajmuje to więcej czasu procesora i jest powolne. Spróbuj ponownie wykonać testy, korzystając z poniższej caseinstrukcji. Używa tylko 2 porównań na wiersz i powinna być bardziej wydajna niż unpivoti union all.

Select Id, 
       Case 
           When Col1 <= Col2 then case when Col1 <= Col3 Then Col1  else col3 end
            When  Col2 <= Col3 Then Col2  
            Else Col3 
            End As TheMin 
From   YourTableNameHere

The union allMetoda jest nie tak w twoim przypadku jak otrzymujesz minimalną wartość nie w każdym wierszu, ale dla całej tabeli. Ponadto nie będzie to wydajne, ponieważ będziesz skanować ten sam stół 3 razy. Gdy stół jest mały, operacje we / wy nie będą miały większego znaczenia, ale w przypadku dużych tabel tak. Nie używaj tej metody.

Unpivotjest dobry i spróbuj również ręcznie cofnąć przestawienie, używając połączenia krzyżowego ze swoim stołem (select 1 union all select 2 union all select 3). Powinien być tak samo wydajny jakunpivot .

Najlepszym rozwiązaniem byłoby obliczenie utrwalonej kolumny, jeśli nie występują problemy z przestrzenią. To doda do wielkości wiersza o 4 bajty (przypuszczam, że będziesz miałintZwiększy rozmiar typ), co z kolei zwiększy rozmiar tabeli.

Jednak w systemie występuje problem z pamięcią i pamięcią, a procesor nie powoduje, że jest on utrwalony, ale używa prostej kolumny obliczeniowej za pomocą instrukcji case. Uprości to kod.

Wydaje mi się, że pierwsza opcja jest najszybsza (choć z perspektywy programowania nie wygląda zbyt gładko!). Jest tak, ponieważ dotyczy dokładnie N wierszy (gdzie N jest rozmiarem tabeli) i nie musi wyszukiwać ani sortować, jak metoda 2 lub 3.

Test z dużą próbką powinien udowodnić sens.

Kolejną opcją do rozważenia (jakbyś potrzebował więcej!) Jest stworzenie zmaterializowanego widoku na stole. jeśli rozmiar Twojej tabeli wynosi 100 lub więcej tysięcy. W ten sposób wartość minimalna jest obliczana przy zmianie wiersza, a cała tabela nie musiałaby być przetwarzana przy każdym zapytaniu. W SQL Server zmaterializowane widoki nazywane są widokami indeksowanymi

Create table #temp
   (
    id int identity(1,1),
    Name varchar(30),
    Year1 int,
    Year2 int,
    Year3 int,
    Year4 int
   )

   Insert into #temp values ('A' ,2015,2016,2014,2010)
   Insert into #temp values ('B' ,2016,2013,2017,2018)
   Insert into #temp values ('C' ,2010,2016,2014,2017)
   Insert into #temp values ('D' ,2017,2016,2014,2015)
   Insert into #temp values ('E' ,2016,2016,2016,2016)
   Insert into #temp values ('F' ,2016,2017,2018,2019)
   Insert into #temp values ('G' ,2016,2017,2020,2019)

   Select *, Case 
                 when Year1 >= Year2 and Year1 >= Year3 and Year1 >= Year4 then Year1
                 when Year2 >= Year3 and Year2 >= Year4 and Year2 >= Year1 then Year2
                 when Year3 >= Year4 and Year3 >= Year1 and Year3 >= Year2 then Year3
                 when Year4 >= Year1 and Year4 >= Year2 and Year4 >= Year3 then Year4  
                 else Year1 end as maxscore  
                 from #temp