Często muszę wybrać liczbę wierszy z każdej grupy w zestawie wyników.

Na przykład może chciałbym podać najwyższe lub najniższe wartości ostatniego zamówienia „n” na klienta.

W bardziej złożonych przypadkach liczba wierszy do wyświetlenia może się różnić w zależności od grupy (zdefiniowanej przez atrybut rekordu grupowania / rodzica). Ta część jest zdecydowanie opcjonalna / dla dodatkowego kredytu i nie ma na celu odwieść ludzi od odpowiedzi.

Jakie są główne opcje rozwiązywania tego rodzaju problemów w SQL Server 2005 i nowszych? Jakie są główne zalety i wady każdej metody?

Przykłady AdventureWorks (dla przejrzystości, opcjonalnie)

1. Wymień pięć ostatnich dat transakcji i identyfikatory z TransactionHistorytabeli, dla każdego produktu zaczynającego się na literę od M do R włącznie.
2. To samo znowu, ale z nliniami historii na produkt, gdzie njest pięciokrotnie DaysToManufactureatrybut produktu.
3. To samo, w szczególnym przypadku, w którym wymagany jest dokładnie jeden wiersz historii na produkt (ostatni pojedynczy wpis autorstwa TransactionDate, rozstrzygnięcie w sprawie) TransactionID.

Zacznijmy od podstawowego scenariusza.

Jeśli chcę uzyskać pewną liczbę wierszy ze tabeli, mam dwie główne opcje: funkcje rankingu; lub TOP.

Najpierw rozważmy cały zestaw Production.TransactionHistorydla konkretnego ProductID:

SELECT h.TransactionID, h.ProductID, h.TransactionDate
FROM Production.TransactionHistory h
WHERE h.ProductID = 800;

Zwraca 418 wierszy, a plan pokazuje, że sprawdza każdy wiersz w tabeli, szukając tego - nieograniczone skanowanie indeksu klastrowego, z predykatem zapewniającym filtr. 797 czyta tutaj, co jest brzydkie.

Bądźmy wobec tego uczciwi i stwórzmy indeks, który byłby bardziej użyteczny. Nasze warunki wymagają włączenia równości ProductID, a następnie wyszukiwania najnowszych przez TransactionDate. Potrzebujemy TransactionIDpowrócił też, więc chodźmy z: CREATE INDEX ix_FindingMostRecent ON Production.TransactionHistory (ProductID, TransactionDate) INCLUDE (TransactionID);.

Po wykonaniu tej czynności nasz plan zmienia się znacznie i zmniejsza odczyty do zaledwie 3. Więc już poprawiamy rzeczy o ponad 250 razy ...

Teraz, gdy wyrównowaliśmy szanse, spójrzmy na najlepsze opcje - funkcje rankingu i TOP.

WITH Numbered AS
(
SELECT h.TransactionID, h.ProductID, h.TransactionDate, ROW_NUMBER() OVER (ORDER BY TransactionDate DESC) AS RowNum
FROM Production.TransactionHistory h
WHERE h.ProductID = 800
)
SELECT TransactionID, ProductID, TransactionDate
FROM Numbered
WHERE RowNum <= 5;

SELECT TOP (5) h.TransactionID, h.ProductID, h.TransactionDate
FROM Production.TransactionHistory h
WHERE h.ProductID = 800
ORDER BY TransactionDate DESC;

Zauważysz, że drugie ( TOP) zapytanie jest znacznie prostsze niż pierwsze, zarówno w zapytaniu, jak iw planie. Ale bardzo znacząco, oba używają, TOPaby ograniczyć liczbę wierszy faktycznie wyciągniętych z indeksu. Koszty są jedynie szacunkowe i warte zignorowania, ale widać duże podobieństwo w dwóch planach, przy czym ROW_NUMBER()wersja wykonuje niewielką dodatkową pracę, aby przypisać liczby i odpowiednio filtrować, a oba zapytania kończą się tylko 2 odczytami ich praca. Optymalizator zapytań z pewnością uznaje pomysł filtrowania na ROW_NUMBER()polu, zdając sobie sprawę, że może użyć operatora Top do zignorowania wierszy, które nie będą potrzebne. Oba te zapytania są wystarczająco dobre - TOPnie jest o wiele lepsze, że warto zmienić kod, ale jest prostsze i prawdopodobnie bardziej zrozumiałe dla początkujących.

Działa to w jednym produkcie. Musimy jednak rozważyć, co się stanie, jeśli będziemy musieli to zrobić dla wielu produktów.

Iteracyjny programista rozważy pomysł przechodzenia przez interesujące produkty i wywoływania tego zapytania wiele razy, a my możemy faktycznie uciec od pisania zapytania w tej formie - nie przy użyciu kursorów, ale przy użyciu APPLY. Używam OUTER APPLY, domyślając się, że możemy chcieć zwrócić Produkt z wartością NULL, jeśli nie ma dla niego Transakcji.

SELECT p.Name, p.ProductID, t.TransactionID, t.TransactionDate
FROM 
Production.Product p
OUTER APPLY (
    SELECT TOP (5) h.TransactionID, h.ProductID, h.TransactionDate
    FROM Production.TransactionHistory h
    WHERE h.ProductID = p.ProductID
    ORDER BY TransactionDate DESC
) t
WHERE p.Name >= 'M' AND p.Name < 'S';

Planem jest iteracyjna metoda programistów - Nested Loop, wykonująca operację Top i Seek (te 2 odczyty, które mieliśmy wcześniej) dla każdego produktu. To daje 4 odczyty w stosunku do produktu i 360 w stosunku do TransactionHistory.

Korzystanie ROW_NUMBER()z metody użyć PARTITION BYw OVERklauzuli, tak, że ponowne uruchomienie numeracji dla każdego produktu. Można to następnie przefiltrować jak poprzednio. Ostatecznie plan jest zupełnie inny. Odczyty logiczne są o około 15% niższe w TransactionHistory, a pełne skanowanie indeksu trwa, aby wyjąć wiersze.

WITH Numbered AS
(
SELECT p.Name, p.ProductID, h.TransactionID, h.TransactionDate, ROW_NUMBER() OVER (PARTITION BY h.ProductID ORDER BY h.TransactionDate DESC) AS RowNum
FROM Production.Product p
LEFT JOIN Production.TransactionHistory h ON h.ProductID = p.ProductID
WHERE p.Name >= 'M' AND p.Name < 'S'
)
SELECT Name, ProductID, TransactionID, TransactionDate
FROM Numbered n
WHERE RowNum <= 5;

Co istotne, ten plan ma kosztowny operator sortowania. Łączenie scalające nie wydaje się utrzymywać kolejności wierszy w TransactionHistory, dane muszą zostać wykorzystane, aby móc znaleźć numery początkowe. Jest mniej odczytów, ale ten rodzaj blokowania może być bolesny. Używając APPLY, zagnieżdżona pętla bardzo szybko zwróci pierwsze rzędy, po zaledwie kilku odczytach, ale z sortowaniem, ROW_NUMBER()zwróci wiersze tylko po zakończeniu większości pracy.

Co ciekawe, jeśli ROW_NUMBER()zapytanie używa INNER JOINzamiast tego LEFT JOIN, pojawia się inny plan.

Ten plan używa zagnieżdżonej pętli, podobnie jak w przypadku APPLY. Ale nie ma operatora Top, więc pobiera wszystkie transakcje dla każdego produktu i zużywa o wiele więcej odczytów niż wcześniej - 492 odczytów w stosunku do TransactionHistory. Nie ma dobrego powodu, aby nie wybierać tutaj opcji Scal dołącz, więc myślę, że plan został uznany za „wystarczająco dobry”. Mimo to - nie blokuje, co jest miłe - po prostu nie tak ładne jak APPLY.

PARTITION BYKolumna że użyłem za ROW_NUMBER()to h.ProductIDw obu przypadkach, bo chciał dać QO możliwość wytwarzania wartości rownum Przed dołączeniem do stołu Produktu. Jeśli użyję p.ProductID, zobaczymy taki sam plan kształtu, jak w przypadku INNER JOINwariantu.

WITH Numbered AS
(
SELECT p.Name, p.ProductID, h.TransactionID, h.TransactionDate, ROW_NUMBER() OVER (PARTITION BY p.ProductID ORDER BY h.TransactionDate DESC) AS RowNum
FROM Production.Product p
LEFT JOIN Production.TransactionHistory h ON h.ProductID = p.ProductID
WHERE p.Name >= 'M' AND p.Name < 'S'
)
SELECT Name, ProductID, TransactionID, TransactionDate
FROM Numbered n
WHERE RowNum <= 5;

Ale operator Join mówi „Left Outer Join” zamiast „Inner Join”. Liczba odczytów jest wciąż nieco mniejsza niż 500 odczytów w stosunku do tabeli TransactionHistory.

W każdym razie - wracając do pytania ...

Odpowiedzieliśmy na pytanie 1 , mając dwie opcje do wyboru. Osobiście podoba mi się ta APPLYopcja.

Aby rozszerzyć to na użycie zmiennej liczby ( pytanie 2 ), 5wystarczy odpowiednio zmienić. Aha, a ja dodałem kolejny indeks, aby indeks Production.Product.Namezawierał DaysToManufacturekolumnę.

WITH Numbered AS
(
SELECT p.Name, p.ProductID, p.DaysToManufacture, h.TransactionID, h.TransactionDate, ROW_NUMBER() OVER (PARTITION BY h.ProductID ORDER BY h.TransactionDate DESC) AS RowNum
FROM Production.Product p
LEFT JOIN Production.TransactionHistory h ON h.ProductID = p.ProductID
WHERE p.Name >= 'M' AND p.Name < 'S'
)
SELECT Name, ProductID, TransactionID, TransactionDate
FROM Numbered n
WHERE RowNum <= 5 * DaysToManufacture;

SELECT p.Name, p.ProductID, t.TransactionID, t.TransactionDate
FROM 
Production.Product p
OUTER APPLY (
    SELECT TOP (5 * p.DaysToManufacture) h.TransactionID, h.ProductID, h.TransactionDate
    FROM Production.TransactionHistory h
    WHERE h.ProductID = p.ProductID
    ORDER BY TransactionDate DESC
) t
WHERE p.Name >= 'M' AND p.Name < 'S';

Oba plany są prawie identyczne z poprzednimi!

Ponownie zignoruj ​​szacunkowe koszty - ale nadal podoba mi się scenariusz TOP, ponieważ jest o wiele prostszy, a plan nie ma operatora blokującego. Odczyty są mniejsze w TransactionHistory ze względu na dużą liczbę zer DaysToManufacture, ale w rzeczywistości wątpię, byśmy wybrali tę kolumnę. ;)

Jednym ze sposobów uniknięcia bloku jest wymyślenie planu, który obsługuje ROW_NUMBER()bit po prawej (w planie) złączenia. Możemy to przekonać, wykonując połączenie poza CTE.

WITH Numbered AS
(
SELECT h.TransactionID, h.ProductID, h.TransactionDate, ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY TransactionDate DESC) AS RowNum
FROM Production.TransactionHistory h
)
SELECT p.Name, p.ProductID, t.TransactionID, t.TransactionDate
FROM Production.Product p
LEFT JOIN Numbered t ON t.ProductID = p.ProductID
    AND t.RowNum <= 5 * p.DaysToManufacture
WHERE p.Name >= 'M' AND p.Name < 'S';

Plan tutaj wygląda na prostszy - nie blokuje, ale istnieje ukryte niebezpieczeństwo.

Zwróć uwagę na obliczony skalar, który pobiera dane z tabeli produktów. To jest wypracowanie 5 * p.DaysToManufacturewartości. Ta wartość nie jest przekazywana do gałęzi, która pobiera dane z tabeli TransactionHistory, jest używana w Łączeniu scalającym. Jako pozostałość.

Tak więc łączenie scalające pochłania WSZYSTKIE wiersze, a nie tylko pierwszą, ale potrzebną, ale wszystkie z nich, a następnie wykonuje kontrolę resztkową. Jest to niebezpieczne, ponieważ liczba transakcji rośnie. Nie jestem fanem tego scenariusza - pozostałe predykaty w łączeniach scalających mogą szybko wzrosnąć. Kolejny powód, dla którego wolę ten APPLY/TOPscenariusz.

W szczególnym przypadku, gdy jest to dokładnie jeden wiersz, w przypadku pytania 3 możemy oczywiście użyć tych samych zapytań, ale 1zamiast 5. Ale mamy dodatkową opcję, która polega na użyciu zwykłych agregatów.

SELECT ProductID, MAX(TransactionDate)
FROM Production.TransactionHistory
GROUP BY ProductID;

Takie zapytanie byłoby użytecznym początkiem i moglibyśmy go łatwo zmodyfikować, aby wyciągnąć TransactionID również do celów rozstrzygania remisów (używając konkatenacji, która byłaby następnie podzielona), ale albo patrzymy na cały indeks, albo nurkujemy produkt po produkcie i tak naprawdę nie uzyskujemy dużej poprawy w stosunku do tego, co mieliśmy wcześniej w tym scenariuszu.

Ale powinienem zauważyć, że analizujemy tutaj konkretny scenariusz. W przypadku rzeczywistych danych i strategii indeksowania, która może nie być idealna, przebieg może się znacznie różnić. Pomimo tego, że widzieliśmy, że APPLYjest tutaj silny, w niektórych sytuacjach może być wolniejszy. Rzadko się jednak blokuje, ponieważ ma tendencję do używania zagnieżdżonych pętli, które wiele osób (w tym ja) uważa za bardzo atrakcyjne.

Nie próbowałem tutaj badać paralelizmu ani nie zastanawiałem się bardzo nad pytaniem 3, które widzę jako szczególny przypadek, którego ludzie rzadko chcą ze względu na komplikację łączenia i podziału. Najważniejszą rzeczą do rozważenia tutaj jest to, że te dwie opcje są bardzo silne.

Wolę APPLY. Jest jasne, dobrze wykorzystuje operatora Top i rzadko powoduje blokowanie.

Typowym sposobem na to w SQL Server 2005 i nowszych wersjach jest użycie CTE i funkcji okienkowania. Na górę N na grupie wystarczy skorzystać ROW_NUMBER()z PARTITIONklauzuli i filtr przeciw, że w zewnętrznej kwerendy. Na przykład można wyświetlić w ten sposób 5 najnowszych zamówień na jednego klienta:

DECLARE @top INT;
SET @top = 5;

;WITH grp AS 
(
   SELECT CustomerID, OrderID, OrderDate,
     rn = ROW_NUMBER() OVER
     (PARTITION BY CustomerID ORDER BY OrderDate DESC)
   FROM dbo.Orders
)
SELECT CustomerID, OrderID, OrderDate
  FROM grp
  WHERE rn <= @top
  ORDER BY CustomerID, OrderDate DESC;

Możesz to również zrobić za pomocą CROSS APPLY:

DECLARE @top INT;
SET @top = 5;

SELECT c.CustomerID, o.OrderID, o.OrderDate
FROM dbo.Customers AS c
CROSS APPLY 
(
    SELECT TOP (@top) OrderID, OrderDate 
    FROM dbo.Orders AS o
    WHERE CustomerID = c.CustomerID
    ORDER BY OrderDate DESC
) AS o
ORDER BY c.CustomerID, o.OrderDate DESC;

Po wskazaniu dodatkowej opcji Paul powiedzmy, że tabela Klienci ma kolumnę wskazującą liczbę wierszy do uwzględnienia na klienta:

;WITH grp AS 
(
   SELECT CustomerID, OrderID, OrderDate,
     rn = ROW_NUMBER() OVER
     (PARTITION BY CustomerID ORDER BY OrderDate DESC)
   FROM dbo.Orders
)
SELECT c.CustomerID, grp.OrderID, grp.OrderDate
  FROM grp 
  INNER JOIN dbo.Customers AS c
  ON grp.CustomerID = c.CustomerID
  AND grp.rn <= c.Number_of_Recent_Orders_to_Show
  ORDER BY c.CustomerID, grp.OrderDate DESC;

I znowu, używając CROSS APPLYi włączając dodaną opcję, aby liczba wierszy dla klienta była podyktowana jakąś kolumną w tabeli klientów:

SELECT c.CustomerID, o.OrderID, o.OrderDate
FROM dbo.Customers AS c
CROSS APPLY 
(
    SELECT TOP (c.Number_of_Recent_Orders_to_Show) OrderID, OrderDate 
    FROM dbo.Orders AS o
    WHERE CustomerID = c.CustomerID
    ORDER BY OrderDate DESC
) AS o
ORDER BY c.CustomerID, o.OrderDate DESC;

Należy pamiętać, że będą one działać różnie w zależności od dystrybucji danych i dostępności indeksów pomocniczych, więc optymalizacja wydajności i uzyskanie najlepszego planu będzie naprawdę zależeć od lokalnych czynników.

Osobiście wolę CTE i rozwiązania okienkowe od CROSS APPLY/, TOPponieważ lepiej oddzielają logikę i są bardziej intuicyjne (dla mnie). Ogólnie (zarówno w tym przypadku, jak i z mojego ogólnego doświadczenia) podejście CTE zapewnia bardziej wydajne plany (przykłady poniżej), ale nie należy tego traktować jako uniwersalnej prawdy - zawsze należy testować swoje scenariusze, szczególnie jeśli indeksy uległy zmianie lub dane znacznie się zmieniły.

Przykłady AdventureWorks - bez żadnych zmian

1. Wymień pięć ostatnich dat transakcji i identyfikatory z TransactionHistorytabeli, dla każdego produktu zaczynającego się na literę od M do R włącznie.

-- CTE / OVER()

;WITH History AS
(
  SELECT p.ProductID, p.Name, t.TransactionID, t.TransactionDate,
    rn = ROW_NUMBER() OVER 
    (PARTITION BY t.ProductID ORDER BY t.TransactionDate DESC)
  FROM Production.Product AS p
  INNER JOIN Production.TransactionHistory AS t
  ON p.ProductID = t.ProductID
  WHERE p.Name >= N'M' AND p.Name < N'S'
)
SELECT ProductID, Name, TransactionID, TransactionDate
FROM History 
WHERE rn <= 5;

-- CROSS APPLY

SELECT p.ProductID, p.Name, t.TransactionID, t.TransactionDate
FROM Production.Product AS p
CROSS APPLY
(
  SELECT TOP (5) TransactionID, TransactionDate
  FROM Production.TransactionHistory
  WHERE ProductID = p.ProductID
  ORDER BY TransactionDate DESC
) AS t
WHERE p.Name >= N'M' AND p.Name < N'S';

Porównanie tych dwóch parametrów pomiaru:

CTE / OVER()plan:

CROSS APPLY plan:

Plan CTE wygląda na bardziej skomplikowany, ale w rzeczywistości jest znacznie bardziej wydajny. Nie zwracaj uwagi na szacunkowe wartości procentowe kosztów, ale skup się na ważniejszych rzeczywistych obserwacjach, takich jak znacznie mniej odczytów i znacznie krótszy czas trwania. Prowadziłem je również bez równoległości i to nie była różnica. Wskaźniki czasu wykonywania i plan CTE ( CROSS APPLYplan pozostał taki sam):

2. To samo znowu, ale z nliniami historii na produkt, gdzie njest pięciokrotnie DaysToManufactureatrybut produktu.

Wymagane tutaj bardzo niewielkie zmiany. W przypadku CTE możemy dodać kolumnę do zapytania wewnętrznego i filtrować zapytanie zewnętrzne; dla CROSS APPLYmożemy wykonać obliczenia w skorelowanym TOP. Można by pomyśleć, że to nadałoby pewną skuteczność CROSS APPLYrozwiązaniu, ale tak się nie dzieje w tym przypadku. Zapytania:

-- CTE / OVER()

;WITH History AS
(
  SELECT p.ProductID, p.Name, p.DaysToManufacture, t.TransactionID, t.TransactionDate,
    rn = ROW_NUMBER() OVER 
    (PARTITION BY t.ProductID ORDER BY t.TransactionDate DESC)
  FROM Production.Product AS p
  INNER JOIN Production.TransactionHistory AS t
  ON p.ProductID = t.ProductID
  WHERE p.Name >= N'M' AND p.Name < N'S'
)
SELECT ProductID, Name, TransactionID, TransactionDate
FROM History 
WHERE rn <= (5 * DaysToManufacture);

-- CROSS APPLY

SELECT p.ProductID, p.Name, t.TransactionID, t.TransactionDate
FROM Production.Product AS p
CROSS APPLY
(
  SELECT TOP (5 * p.DaysToManufacture) TransactionID, TransactionDate
  FROM Production.TransactionHistory
  WHERE ProductID = p.ProductID
  ORDER BY TransactionDate DESC
) AS t
WHERE p.Name >= N'M' AND p.Name < N'S';

Wyniki w środowisku wykonawczym:

Równoległe CTE / OVER()plan:

Jednowątkowy CTE / OVER()plan:

CROSS APPLY plan:

3. To samo, w szczególnym przypadku, w którym wymagany jest dokładnie jeden wiersz historii na produkt (ostatni pojedynczy wpis autorstwa TransactionDate, rozstrzygnięcie w sprawie) TransactionID.

Ponownie drobne zmiany tutaj. W rozwiązaniu CTE dodajemy TransactionIDdo OVER()klauzuli i zmieniamy filtr zewnętrzny na rn = 1. Albowiem CROSS APPLY, zmieniamy TOPsię TOP (1)i dodać TransactionIDdo wewnętrznej ORDER BY.

-- CTE / OVER()

;WITH History AS
(
  SELECT p.ProductID, p.Name, t.TransactionID, t.TransactionDate,
    rn = ROW_NUMBER() OVER 
    (PARTITION BY t.ProductID ORDER BY t.TransactionDate DESC, TransactionID DESC)
  FROM Production.Product AS p
  INNER JOIN Production.TransactionHistory AS t
  ON p.ProductID = t.ProductID
  WHERE p.Name >= N'M' AND p.Name < N'S'
)
SELECT ProductID, Name, TransactionID, TransactionDate
FROM History 
WHERE rn = 1;

-- CROSS APPLY

SELECT p.ProductID, p.Name, t.TransactionID, t.TransactionDate
FROM Production.Product AS p
CROSS APPLY
(
  SELECT TOP (1) TransactionID, TransactionDate
  FROM Production.TransactionHistory
  WHERE ProductID = p.ProductID
  ORDER BY TransactionDate DESC, TransactionID DESC
) AS t
WHERE p.Name >= N'M' AND p.Name < N'S';

Wyniki w środowisku wykonawczym:

Równoległe CTE / OVER()plan:

Plan jednowątkowy CTE / OVER ():

CROSS APPLY plan:

Funkcje okienkowania nie zawsze są najlepszą alternatywą (spróbuj COUNT(*) OVER()) i nie są to jedyne dwa podejścia do rozwiązania problemu n wierszy na grupę, ale w tym konkretnym przypadku - biorąc pod uwagę schemat, istniejące indeksy i dystrybucję danych - CTE wypadło lepiej pod wszystkimi znaczącymi kontami.

Przykłady AdventureWorks - z możliwością dodawania indeksów

Jeśli jednak dodasz indeks pomocniczy, podobny do tego, o którym Paweł wspomniał w komentarzu, ale z uporządkowaną drugą i trzecią kolumną DESC:

CREATE UNIQUE NONCLUSTERED INDEX UQ3 ON Production.TransactionHistory 
  (ProductID, TransactionDate DESC, TransactionID DESC);

W rzeczywistości uzyskałbyś o wiele korzystniejsze plany, a wskaźniki zmieniłyby się, aby faworyzować CROSS APPLYpodejście we wszystkich trzech przypadkach:

Gdyby to było moje środowisko produkcyjne, prawdopodobnie byłbym w tym przypadku zadowolony z czasu trwania i nie zawracałbym sobie głowy dalszą optymalizacją.

To wszystko było o wiele bardziej brzydsze w SQL Server 2000, który nie obsługiwał APPLYani nie zawierał OVER()klauzuli.

W DBMS, takim jak MySQL, które nie mają funkcji okna lub CROSS APPLYsposobem na to byłoby użycie standardowego SQL (89). Wolną drogą byłoby trójkątne połączenie krzyżowe z kruszywem. Szybszym sposobem (ale wciąż i prawdopodobnie nie tak wydajnym, jak użycie funkcji cross Apply lub row_number) byłoby to, co nazywam „biednym CROSS APPLY” . Byłoby interesujące porównać to zapytanie z innymi:

Założenie: Orders (CustomerID, OrderDate)ma UNIQUEograniczenie:

DECLARE @top INT;
SET @top = 5;

SELECT o.CustomerID, o.OrderID, o.OrderDate
  FROM dbo.Customers AS c
    JOIN dbo.Orders AS o
      ON  o.CustomerID = c.CustomerID
      AND o.OrderID IN
          ( SELECT TOP (@top) oi.OrderID
            FROM dbo.Orders AS oi
            WHERE oi.CustomerID = c.CustomerID
            ORDER BY oi.OrderDate DESC
          )
  ORDER BY CustomerID, OrderDate DESC ;

Dodatkowy problem niestandardowych górnych wierszy dla grupy:

SELECT o.CustomerID, o.OrderID, o.OrderDate
  FROM dbo.Customers AS c
    JOIN dbo.Orders AS o
      ON  o.CustomerID = c.CustomerID
      AND o.OrderID IN
          ( SELECT TOP (c.Number_of_Recent_Orders_to_Show) oi.OrderID
            FROM dbo.Orders AS oi
            WHERE oi.CustomerID = c.CustomerID
            ORDER BY oi.OrderDate DESC
          )
  ORDER BY CustomerID, OrderDate DESC ;

Uwaga: W MySQL zamiast AND o.OrderID IN (SELECT TOP(@top) oi.OrderID ...)jednego użyłby AND o.OrderDate >= (SELECT oi.OrderDate ... LIMIT 1 OFFSET (@top - 1)). SQL-Server dodał FETCH / OFFSETskładnię w wersji 2012. Zapytania tutaj zostały dostosowane IN (TOP...)do pracy z wcześniejszymi wersjami.

Przyjąłem nieco inne podejście, głównie po to, aby zobaczyć, jak ta technika mogłaby się porównać do innych, ponieważ posiadanie opcji jest dobre, prawda?

Testowanie

Dlaczego nie zaczniemy od spojrzenia na zestawienie różnych metod. Zrobiłem trzy zestawy testów:

1. Pierwszy zestaw działał bez modyfikacji DB
2. Drugi zestaw uruchomiono po utworzeniu indeksu do obsługi TransactionDatezapytań opartych na Production.TransactionHistory.
3. Trzeci zestaw przyjął nieco inne założenie. Ponieważ wszystkie trzy testy zostały przeprowadzone na tej samej liście produktów, co zrobić, jeśli będziemy buforować tę listę? Moja metoda używa pamięci podręcznej w pamięci, podczas gdy inne metody używały równoważnej tabeli temp. Indeks pomocniczy utworzony dla drugiego zestawu testów nadal istnieje dla tego zestawu testów.

Dodatkowe szczegóły testu:

• Testy przeprowadzono AdventureWorks2012na SQL Server 2012, SP2 (edycja dla programistów).
• Dla każdego testu oznaczyłem etykietę, z której odpowiedzi wziąłem zapytanie i które to zapytanie.
• Użyłem opcji „Odrzuć wyniki po wykonaniu” w Opcjach zapytania | Wyniki
• Uwaga: w przypadku pierwszych dwóch zestawów testów RowCountswydaje się, że dla mojej metody jest on wyłączony. Wynika to z tego, że moja metoda jest ręczną implementacją tego, co CROSS APPLYsię dzieje: uruchamia zapytanie początkowe Production.Producti zwraca 161 wierszy z powrotem, które następnie wykorzystuje do zapytań Production.TransactionHistory. W związku z tym RowCountwartości moich wpisów są zawsze o 161 większe niż innych wpisów. W trzecim zestawie testów (z buforowaniem) liczba wierszy jest taka sama dla wszystkich metod.
• Użyłem SQL Server Profiler do przechwytywania statystyk zamiast polegać na planach wykonania. Aaron i Mikael wykonali już świetną robotę, pokazując plany swoich zapytań i nie ma potrzeby kopiowania tych informacji. Celem mojej metody jest sprowadzenie zapytań do tak prostej formy, że tak naprawdę nie miałoby to znaczenia. Istnieje dodatkowy powód korzystania z Profiler, ale zostanie on wspomniany później.
• Zamiast używać Name >= N'M' AND Name < N'S'konstrukcji, zdecydowałem się użyć Name LIKE N'[M-R]%', a SQL Server traktuje je tak samo.

Wyniki

Brak indeksu pomocniczego

Jest to zasadniczo gotowe urządzenie AdventureWorks2012. We wszystkich przypadkach moja metoda jest wyraźnie lepsza niż niektóre inne, ale nigdy nie jest tak dobra jak pierwsza lub 2 metody.

Test 1
CTE Aarona jest tutaj wyraźnie zwycięzcą.

Przetestuj 2
CTE Aarona (ponownie), a druga apply row_number()metoda Mikaela jest bliska drugiej.

Test 3
CTE Aarona (ponownie) jest zwycięzcą.

Podsumowanie
Gdy nie ma włączonego indeksu pomocniczego TransactionDate, moja metoda jest lepsza niż robienie standardu CROSS APPLY, ale nadal zdecydowanie korzystam z metody CTE.

Z indeksem wspierającym (bez buforowania)

Do tego zestawu testów dodałem oczywisty indeks, TransactionHistory.TransactionDateponieważ wszystkie zapytania są sortowane na tym polu. Mówię „oczywiste”, ponieważ większość innych odpowiedzi również zgadza się w tej kwestii. A ponieważ wszystkie zapytania wymagają najnowszych dat, TransactionDatepole należy zamówić DESC, więc po prostu złapałem CREATE INDEXoświadczenie u dołu odpowiedzi Mikaela i dodałem wyraźne FILLFACTOR:

CREATE INDEX [IX_TransactionHistoryX]
    ON Production.TransactionHistory (ProductID ASC, TransactionDate DESC)
    WITH (FILLFACTOR = 100);

Po wprowadzeniu tego indeksu wyniki dość się zmieniają.

Test 1
Tym razem to moja metoda wychodzi na przód, przynajmniej w zakresie odczytów logicznych. CROSS APPLYMetoda, wcześniej najgorszy wykonawca na teście 1, wygrywa na czas trwania, a nawet bije metodę CTE na logicznych odczytów.

Test 2
Tym razem jest to pierwsza apply row_number()metoda Mikaela, która zwyciężyła, patrząc na lektury, podczas gdy wcześniej była to jedna z najgorzej wykonujących. A teraz moja metoda znajduje się na bardzo drugim miejscu, gdy patrzę na Reads. W rzeczywistości, poza metodą CTE, wszystkie pozostałe są dość zbliżone pod względem odczytów.

Test 3
Tutaj CTE jest nadal zwycięzcą, ale teraz różnica między innymi metodami jest ledwo zauważalna w porównaniu z drastyczną różnicą, która istniała przed utworzeniem indeksu.

Wniosek
Zastosowanie mojej metody jest teraz bardziej widoczne, choć jest mniej odporne na brak odpowiednich indeksów.

Z obsługą indeksu i buforowania

Do tego zestawu testów wykorzystałem buforowanie, bo cóż, dlaczego nie? Moja metoda pozwala na użycie buforowania w pamięci, do którego inne metody nie mają dostępu. Żeby być uczciwym, stworzyłem następującą tabelę temp, która była używana zamiast Product.Productwszystkich odniesień w tych innych metodach we wszystkich trzech testach. To DaysToManufacturepole jest używane tylko w teście nr 2, ale łatwiej było zachować spójność we wszystkich skryptach SQL, aby używać tej samej tabeli i nie zaszkodziło, aby było tam.

CREATE TABLE #Products
(
    ProductID INT NOT NULL PRIMARY KEY,
    Name NVARCHAR(50) NOT NULL,
    DaysToManufacture INT NOT NULL
);

INSERT INTO #Products (ProductID, Name, DaysToManufacture)
    SELECT  p.ProductID, p.Name, p.DaysToManufacture
    FROM    Production.Product p
    WHERE   p.Name >= N'M' AND p.Name < N'S'
    AND    EXISTS (
                    SELECT  *
                    FROM    Production.TransactionHistory th
                    WHERE   th.ProductID = p.ProductID
                );

ALTER TABLE #Products REBUILD WITH (FILLFACTOR = 100);

Test 1
Wszystkie metody wydają się czerpać równe korzyści z buforowania, a moja metoda wciąż wychodzi na przód.

Test 2
Tutaj widzimy teraz różnicę w składzie, ponieważ moja metoda wychodzi zaledwie na przód, tylko 2 Odczytuje lepiej niż pierwsza apply row_number()metoda Mikaela , podczas gdy bez buforowania moja metoda była opóźniona o 4 Odczyty.

Test 3
Proszę zobaczyć aktualizację w dół (poniżej linii) . Tutaj znów widzimy różnicę. „Sparametryzowany” smak mojej metody jest teraz ledwo na czele o 2 odczyty w porównaniu z metodą CROSS APPLY Aarona (bez buforowania były one równe). Ale naprawdę dziwne jest to, że po raz pierwszy widzimy metodę, na którą buforowanie ma negatywny wpływ: metodę CTE Aarona (która wcześniej była najlepsza dla testu nr 3). Ale nie zamierzam przypisywać sobie uznania, gdy nie jest to należne, a ponieważ bez buforowania metoda CTE Aarona jest wciąż szybsza niż moja metoda tutaj z buforowaniem, najlepszym podejściem w tej konkretnej sytuacji wydaje się być metoda CTE Aarona.

Podsumowanie Proszę zobaczyć aktualizację w dół (poniżej linii)
Sytuacje, w których wielokrotne wykorzystywanie wyników drugiego zapytania może często (ale nie zawsze) skorzystać z buforowania tych wyników. Ale gdy buforowanie jest zaletą, użycie pamięci dla wspomnianego buforowania ma pewną przewagę nad użyciem tabel tymczasowych.

Metoda

Ogólnie

Oddzieliłem zapytanie „nagłówkowe” (tj. Otrzymując ProductIDs, aw jednym przypadku także DaysToManufacture, w oparciu o Namerozpoczęcie od pewnych liter) od zapytań „szczegółowych” (tj. Otrzymujących TransactionIDs i TransactionDates). Założeniem było wykonanie bardzo prostych zapytań i niedopuszczenie do dezorientacji optymalizatora podczas dołączania do nich. Oczywiście nie zawsze jest to korzystne, ponieważ uniemożliwia optymalizatorowi optymalizację. Ale jak widzieliśmy w wynikach, w zależności od rodzaju zapytania, ta metoda ma swoje zalety.

Różnice między różnymi smakami tej metody to:

• Stałe: Prześlij dowolne wartości wymienne jako stałe wbudowane zamiast parametrów. Odnosi się to do ProductIDwszystkich trzech testów, a także do liczby wierszy, które należy zwrócić w teście 2, ponieważ jest to funkcja „pięciokrotności DaysToManufactureatrybutu produktu”. Ta pod-metoda oznacza, że ​​każdy ProductIDotrzyma własny plan wykonania, co może być korzystne, jeśli występuje duża różnorodność dystrybucji danych ProductID. Ale jeśli istnieje niewielka zmienność w dystrybucji danych, koszt wygenerowania dodatkowych planów prawdopodobnie nie będzie tego wart.

• Sparametryzowane: Prześlij co najmniej ProductIDjako @ProductID, umożliwiając buforowanie planu wykonania i ponowne użycie. Dostępna jest dodatkowa opcja testu, która również traktuje zmienną liczbę wierszy zwracanych do testu 2 jako parametr.

• Optymalizuj nieznane: Jeśli odwołujesz się ProductIDjako @ProductID, jeśli istnieje duża różnorodność dystrybucji danych, możliwe jest buforowanie planu, który ma negatywny wpływ na inne ProductIDwartości, więc dobrze byłoby wiedzieć, czy skorzystanie z tej wskazówki zapytania pomoże.

• Produkty w pamięci podręcznej: Zamiast za Production.Productkażdym razem sprawdzać tabelę, tylko w celu uzyskania dokładnie tej samej listy, uruchom zapytanie raz (a gdy już nad tym pracujemy, odfiltruj te ProductID, których nawet nie ma w TransactionHistorytabeli, abyśmy nie marnowali zasoby tam) i buforuj tę listę. Lista powinna zawierać DaysToManufacturepole. Użycie tej opcji powoduje nieco wyższe początkowe trafienie w Odczyty logiczne dla pierwszego wykonania, ale po tym pytana jest tylko TransactionHistorytabela.

konkretnie

Ok, ale tak, um, w jaki sposób możliwe jest wydawanie wszystkich pod-zapytań jako osobnych zapytań bez użycia CURSOR i zrzucania każdego zestawu wyników do tymczasowej tabeli lub zmiennej tabeli? Wyraźne wykonanie metody CURSOR / Temp Table odzwierciedlałoby w sposób oczywisty w Odczytach i Zapisach. Cóż, używając SQLCLR :). Tworząc procedurę przechowywaną SQLCLR, byłem w stanie otworzyć zestaw wyników i zasadniczo przesyłać strumieniowo do niego wyniki każdego zapytania podrzędnego jako ciągły zestaw wyników (a nie wiele zestawów wyników). Poza informacjami o produkcie (tj ProductID, NameiDaysToManufacture), żaden z wyników zapytania podrzędnego nie musiał być nigdzie zapisany (pamięć lub dysk) i został po prostu przekazany jako główny zestaw wyników procedury składowanej SQLCLR. Pozwoliło mi to zrobić proste zapytanie, aby uzyskać informacje o produkcie, a następnie przejść przez nie, wysyłając bardzo proste zapytania TransactionHistory.

I dlatego musiałem użyć SQL Server Profiler do przechwytywania statystyk. Procedura przechowywana SQLCLR nie zwróciła planu wykonania ani przez ustawienie opcji zapytania „Uwzględnij rzeczywisty plan wykonania”, ani przez wydanie SET STATISTICS XML ON;.

Do buforowania informacji o produkcie użyłem readonly staticListy ogólnej (tj. _GlobalProductsW poniższym kodzie). Wydaje się, że dodanie do zbiorów nie narusza readonlymożliwości, stąd ten kod działa, gdy zespół ma PERMISSON_SETwśród SAFE:), nawet jeśli jest to sprzeczne z intuicją.

Wygenerowane zapytania

Zapytania wygenerowane przez tę procedurę przechowywaną SQLCLR są następujące:

Informacje o produkcie

Testuj numery 1 i 3 (bez buforowania)

SELECT prod1.ProductID, prod1.Name, 1 AS [DaysToManufacture]
FROM   Production.Product prod1
WHERE  prod1.Name LIKE N'[M-R]%';

Numer testu 2 (bez buforowania)

;WITH cte AS
(
    SELECT prod1.ProductID
    FROM   Production.Product prod1 WITH (INDEX(AK_Product_Name))
    WHERE  prod1.Name LIKE N'[M-R]%'
)
SELECT prod2.ProductID, prod2.Name, prod2.DaysToManufacture
FROM   Production.Product prod2
INNER JOIN cte
        ON cte.ProductID = prod2.ProductID;

Testuj numery 1, 2 i 3 (buforowanie)

;WITH cte AS
(
    SELECT prod1.ProductID
    FROM   Production.Product prod1 WITH (INDEX(AK_Product_Name))
    WHERE  prod1.Name LIKE N'[M-R]%'
    AND    EXISTS (
                SELECT *
                FROM Production.TransactionHistory th
                WHERE th.ProductID = prod1.ProductID
                  )
)
SELECT prod2.ProductID, prod2.Name, prod2.DaysToManufacture
FROM   Production.Product prod2
INNER JOIN cte
        ON cte.ProductID = prod2.ProductID;

Informacje o transakcji

Liczby testowe 1 i 2 (stałe)

SELECT TOP (5) th.TransactionID, th.TransactionDate
FROM   Production.TransactionHistory th
WHERE  th.ProductID = 977
ORDER BY th.TransactionDate DESC;

Numery testowe 1 i 2 (sparametryzowane)

SELECT TOP (5) th.TransactionID, th.TransactionDate
FROM   Production.TransactionHistory th
WHERE  th.ProductID = @ProductID
ORDER BY th.TransactionDate DESC
;

Numery testowe 1 i 2 (sparametryzowane + OPTYMALIZUJ NIEZNANE)

SELECT TOP (5) th.TransactionID, th.TransactionDate
FROM   Production.TransactionHistory th
WHERE  th.ProductID = @ProductID
ORDER BY th.TransactionDate DESC
OPTION (OPTIMIZE FOR (@ProductID UNKNOWN));

Test nr 2 (sparametryzowane oba)

SELECT TOP (@RowsToReturn) th.TransactionID, th.TransactionDate
FROM   Production.TransactionHistory th
WHERE  th.ProductID = @ProductID
ORDER BY th.TransactionDate DESC
;

Test nr 2 (sparametryzowany + OPTYMALIZUJ NIEZNANY)

SELECT TOP (@RowsToReturn) th.TransactionID, th.TransactionDate
FROM   Production.TransactionHistory th
WHERE  th.ProductID = @ProductID
ORDER BY th.TransactionDate DESC
OPTION (OPTIMIZE FOR (@ProductID UNKNOWN));

Numer testu 3 (stałe)

SELECT TOP (1) th.TransactionID, th.TransactionDate
FROM   Production.TransactionHistory th
WHERE  th.ProductID = 977
ORDER BY th.TransactionDate DESC, th.TransactionID DESC;

Numer testu 3 (sparametryzowany)

SELECT TOP (1) th.TransactionID, th.TransactionDate
FROM   Production.TransactionHistory th
WHERE  th.ProductID = @ProductID
ORDER BY th.TransactionDate DESC, th.TransactionID DESC
;

Numer testu 3 (sparametryzowany + OPTYMALIZUJ NIEZNANY)

SELECT TOP (1) th.TransactionID, th.TransactionDate
FROM   Production.TransactionHistory th
WHERE  th.ProductID = @ProductID
ORDER BY th.TransactionDate DESC, th.TransactionID DESC
OPTION (OPTIMIZE FOR (@ProductID UNKNOWN));

Kod

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public class ObligatoryClassName
{
    private class ProductInfo
    {
        public int ProductID;
        public string Name;
        public int DaysToManufacture;

        public ProductInfo(int ProductID, string Name, int DaysToManufacture)
        {
            this.ProductID = ProductID;
            this.Name = Name;
            this.DaysToManufacture = DaysToManufacture;

            return;
        }
    }

    private static readonly List<ProductInfo> _GlobalProducts = new List<ProductInfo>();

    private static void PopulateGlobalProducts(SqlBoolean PrintQuery)
    {
        if (_GlobalProducts.Count > 0)
        {
            if (PrintQuery.IsTrue)
            {
                SqlContext.Pipe.Send(String.Concat("I already haz ", _GlobalProducts.Count,
                            " entries :)"));
            }

            return;
        }

        SqlConnection _Connection = new SqlConnection("Context Connection = true;");
        SqlCommand _Command = new SqlCommand();
        _Command.CommandType = CommandType.Text;
        _Command.Connection = _Connection;
        _Command.CommandText = @"
   ;WITH cte AS
   (
     SELECT prod1.ProductID
     FROM   Production.Product prod1 WITH (INDEX(AK_Product_Name))
     WHERE  prod1.Name LIKE N'[M-R]%'
     AND    EXISTS (
                     SELECT *
                     FROM Production.TransactionHistory th
                     WHERE th.ProductID = prod1.ProductID
                   )
   )
   SELECT prod2.ProductID, prod2.Name, prod2.DaysToManufacture
   FROM   Production.Product prod2
   INNER JOIN cte
           ON cte.ProductID = prod2.ProductID;
";

        SqlDataReader _Reader = null;

        try
        {
            _Connection.Open();

            _Reader = _Command.ExecuteReader();

            while (_Reader.Read())
            {
                _GlobalProducts.Add(new ProductInfo(_Reader.GetInt32(0), _Reader.GetString(1),
                                                    _Reader.GetInt32(2)));
            }
        }
        catch
        {
            throw;
        }
        finally
        {
            if (_Reader != null && !_Reader.IsClosed)
            {
                _Reader.Close();
            }

            if (_Connection != null && _Connection.State != ConnectionState.Closed)
            {
                _Connection.Close();
            }

            if (PrintQuery.IsTrue)
            {
                SqlContext.Pipe.Send(_Command.CommandText);
            }
        }

        return;
    }


    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetTopRowsPerGroup(SqlByte TestNumber,
        SqlByte ParameterizeProductID, SqlBoolean OptimizeForUnknown,
        SqlBoolean UseSequentialAccess, SqlBoolean CacheProducts, SqlBoolean PrintQueries)
    {
        SqlConnection _Connection = new SqlConnection("Context Connection = true;");
        SqlCommand _Command = new SqlCommand();
        _Command.CommandType = CommandType.Text;
        _Command.Connection = _Connection;

        List<ProductInfo> _Products = null;
        SqlDataReader _Reader = null;

        int _RowsToGet = 5; // default value is for Test Number 1
        string _OrderByTransactionID = "";
        string _OptimizeForUnknown = "";
        CommandBehavior _CmdBehavior = CommandBehavior.Default;

        if (OptimizeForUnknown.IsTrue)
        {
            _OptimizeForUnknown = "OPTION (OPTIMIZE FOR (@ProductID UNKNOWN))";
        }

        if (UseSequentialAccess.IsTrue)
        {
            _CmdBehavior = CommandBehavior.SequentialAccess;
        }

        if (CacheProducts.IsTrue)
        {
            PopulateGlobalProducts(PrintQueries);
        }
        else
        {
            _Products = new List<ProductInfo>();
        }


        if (TestNumber.Value == 2)
        {
            _Command.CommandText = @"
   ;WITH cte AS
   (
     SELECT prod1.ProductID
     FROM   Production.Product prod1 WITH (INDEX(AK_Product_Name))
     WHERE  prod1.Name LIKE N'[M-R]%'
   )
   SELECT prod2.ProductID, prod2.Name, prod2.DaysToManufacture
   FROM   Production.Product prod2
   INNER JOIN cte
           ON cte.ProductID = prod2.ProductID;
";
        }
        else
        {
            _Command.CommandText = @"
     SELECT prod1.ProductID, prod1.Name, 1 AS [DaysToManufacture]
     FROM   Production.Product prod1
     WHERE  prod1.Name LIKE N'[M-R]%';
";
            if (TestNumber.Value == 3)
            {
                _RowsToGet = 1;
                _OrderByTransactionID = ", th.TransactionID DESC";
            }
        }

        try
        {
            _Connection.Open();

            // Populate Product list for this run if not using the Product Cache
            if (!CacheProducts.IsTrue)
            {
                _Reader = _Command.ExecuteReader(_CmdBehavior);

                while (_Reader.Read())
                {
                    _Products.Add(new ProductInfo(_Reader.GetInt32(0), _Reader.GetString(1),
                                                  _Reader.GetInt32(2)));
                }

                _Reader.Close();

                if (PrintQueries.IsTrue)
                {
                    SqlContext.Pipe.Send(_Command.CommandText);
                }
            }
            else
            {
                _Products = _GlobalProducts;
            }

            SqlDataRecord _ResultRow = new SqlDataRecord(
                new SqlMetaData[]{
                    new SqlMetaData("ProductID", SqlDbType.Int),
                    new SqlMetaData("Name", SqlDbType.NVarChar, 50),
                    new SqlMetaData("TransactionID", SqlDbType.Int),
                    new SqlMetaData("TransactionDate", SqlDbType.DateTime)
                });

            SqlParameter _ProductID = new SqlParameter("@ProductID", SqlDbType.Int);
            _Command.Parameters.Add(_ProductID);
            SqlParameter _RowsToReturn = new SqlParameter("@RowsToReturn", SqlDbType.Int);
            _Command.Parameters.Add(_RowsToReturn);

            SqlContext.Pipe.SendResultsStart(_ResultRow);

            for (int _Row = 0; _Row < _Products.Count; _Row++)
            {
                // Tests 1 and 3 use previously set static values for _RowsToGet
                if (TestNumber.Value == 2)
                {
                    if (_Products[_Row].DaysToManufacture == 0)
                    {
                        continue; // no use in issuing SELECT TOP (0) query
                    }

                    _RowsToGet = (5 * _Products[_Row].DaysToManufacture);
                }

                _ResultRow.SetInt32(0, _Products[_Row].ProductID);
                _ResultRow.SetString(1, _Products[_Row].Name);

                switch (ParameterizeProductID.Value)
                {
                    case 0x01:
                        _Command.CommandText = String.Format(@"
   SELECT TOP ({0}) th.TransactionID, th.TransactionDate
   FROM   Production.TransactionHistory th
   WHERE  th.ProductID = @ProductID
   ORDER BY th.TransactionDate DESC{2}
   {1};
", _RowsToGet, _OptimizeForUnknown, _OrderByTransactionID);

                        _ProductID.Value = _Products[_Row].ProductID;
                        break;
                    case 0x02:
                        _Command.CommandText = String.Format(@"
   SELECT TOP (@RowsToReturn) th.TransactionID, th.TransactionDate
   FROM   Production.TransactionHistory th
   WHERE  th.ProductID = @ProductID
   ORDER BY th.TransactionDate DESC
   {0};
", _OptimizeForUnknown);

                        _ProductID.Value = _Products[_Row].ProductID;
                        _RowsToReturn.Value = _RowsToGet;
                        break;
                    default:
                        _Command.CommandText = String.Format(@"
   SELECT TOP ({0}) th.TransactionID, th.TransactionDate
   FROM   Production.TransactionHistory th
   WHERE  th.ProductID = {1}
   ORDER BY th.TransactionDate DESC{2};
", _RowsToGet, _Products[_Row].ProductID, _OrderByTransactionID);
                        break;
                }


                _Reader = _Command.ExecuteReader(_CmdBehavior);

                while (_Reader.Read())
                {
                    _ResultRow.SetInt32(2, _Reader.GetInt32(0));
                    _ResultRow.SetDateTime(3, _Reader.GetDateTime(1));

                    SqlContext.Pipe.SendResultsRow(_ResultRow);
                }
                _Reader.Close();
            }

        }
        catch
        {
            throw;
        }
        finally
        {
            if (SqlContext.Pipe.IsSendingResults)
            {
                SqlContext.Pipe.SendResultsEnd();
            }

            if (_Reader != null && !_Reader.IsClosed)
            {
                _Reader.Close();
            }

            if (_Connection != null && _Connection.State != ConnectionState.Closed)
            {
                _Connection.Close();
            }

            if (PrintQueries.IsTrue)
            {
                SqlContext.Pipe.Send(_Command.CommandText);
            }
        }


    }
}

Zapytania testowe

Nie ma wystarczająco dużo miejsca, aby opublikować tutaj testy, więc znajdę inną lokalizację.

Konkluzja

W niektórych scenariuszach można użyć SQLCLR do manipulowania niektórymi aspektami zapytań, których nie można wykonać w języku T-SQL. Istnieje również możliwość użycia pamięci do buforowania zamiast tabel tymczasowych, choć należy to robić oszczędnie i ostrożnie, ponieważ pamięć nie jest automatycznie zwalniana z powrotem do systemu. Ta metoda również nie pomaga w zapytaniach ad hoc, ale można ją uczynić bardziej elastyczną, niż pokazałem tutaj, po prostu dodając parametry, aby dostosować więcej aspektów wykonywanych zapytań.

AKTUALIZACJA

Test dodatkowy W
moich oryginalnych testach, które zawierały indeks pomocniczy, TransactionHistoryzastosowano następującą definicję:

ProductID ASC, TransactionDate DESC

W tym czasie zdecydowałem się zrezygnować z dołączenia TransactionId DESCna końcu, stwierdzając, że chociaż może to pomóc w Testie 3 (który określa rozstrzyganie ostatnich - TransactionIdcóż, zakłada się, że „najnowszy” nie jest wyraźnie określony, ale wszyscy wydają się zgodzić się z tym założeniem), prawdopodobnie nie będzie wystarczającej liczby więzi, aby coś zmienić.

Ale potem Aaron ponownie przetestował z dodatkowym indeksem, który zawierał TransactionId DESCi stwierdził, że CROSS APPLYmetoda była zwycięska we wszystkich trzech testach. Różniło się to od moich testów, które wskazały, że metoda CTE była najlepsza dla testu nr 3 (gdy nie użyto buforowania, co odzwierciedla test Aarona). Było jasne, że istniała dodatkowa odmiana, którą należało przetestować.

Usunąłem bieżący indeks pomocniczy, utworzyłem nowy za pomocą TransactionIdi wyczyściłem pamięć podręczną planu (dla pewności):

DROP INDEX [IX_TransactionHistoryX] ON Production.TransactionHistory;

CREATE UNIQUE INDEX [UIX_TransactionHistoryX]
    ON Production.TransactionHistory (ProductID ASC, TransactionDate DESC, TransactionID DESC)
    WITH (FILLFACTOR = 100);

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

Ponownie uruchomiłem Test numer 1 i wyniki były takie same, jak oczekiwano. Następnie ponownie uruchomiłem Test Numer 3 i wyniki rzeczywiście się zmieniły:

Powyższe wyniki dotyczą standardowego testu bez buforowania. Tym razem nie tylko CROSS APPLYpokonano CTE (tak jak wskazał test Aarona), ale proc SQLCLR przejął prowadzenie o 30 odczytów (woo hoo).

Powyższe wyniki dotyczą testu z włączonym buforowaniem. Tym razem wydajność CTE nie ulega pogorszeniu, choć CROSS APPLYnadal go bije. Jednak teraz SQLCLR proc przejmuje prowadzenie przez 23 Odczyty (znowu woo hoo).

Odejdź

1. Istnieją różne opcje do użycia. Najlepiej wypróbować kilka, ponieważ każda z nich ma swoje mocne strony. Wykonane tutaj testy wykazują raczej niewielką wariancję zarówno odczytów, jak i czasu trwania między najlepszymi i najgorszymi wynikami we wszystkich testach (z indeksem wspierającym); różnica w odczytach wynosi około 350, a czas trwania wynosi 55 ms. Chociaż proces SQLCLR wygrał we wszystkich testach oprócz 1 (pod względem liczby odczytów), zapisanie tylko kilku odczytów zazwyczaj nie jest warte kosztów utrzymania trasy SQLCLR. Ale w AdventureWorks2012 Producttabela ma tylko 504 wiersze i TransactionHistoryma tylko 113.443 wiersze. Różnica w wydajności tych metod prawdopodobnie staje się wyraźniejsza wraz ze wzrostem liczby wierszy.

2. Chociaż pytanie to dotyczyło konkretnego zestawu wierszy, nie należy zapominać, że najważniejszym czynnikiem wpływającym na wydajność było indeksowanie, a nie konkretny SQL. Dobry indeks musi być na miejscu przed ustaleniem, która metoda jest naprawdę najlepsza.

3. Najważniejsza lekcja tutaj nie dotyczy CROSS APPLY vs CTE vs SQLCLR: chodzi o TESTOWANIE. Nie zakładaj. Zdobądź pomysły od kilku osób i przetestuj jak najwięcej scenariuszy.

APPLY TOPczy ROW_NUMBER()? Co może być więcej do powiedzenia na ten temat?

Krótkie podsumowanie różnic i naprawdę krótko, pokażę tylko plany dla opcji 2 i dodałem indeks Production.TransactionHistory.

create index IX_TransactionHistoryX on 
  Production.TransactionHistory(ProductID, TransactionDate)

row_number()Zapytanie :.

with C as
(
  select T.TransactionID,
         T.TransactionDate,
         P.DaysToManufacture,
         row_number() over(partition by P.ProductID order by T.TransactionDate desc) as rn
  from Production.Product as P
    inner join Production.TransactionHistory as T
      on P.ProductID = T.ProductID
  where P.Name >= N'M' and
        P.Name < N'S'
)
select C.TransactionID,
       C.TransactionDate
from C
where C.rn <= 5 * C.DaysToManufacture;

apply topWersja:

select T.TransactionID, 
       T.TransactionDate
from Production.Product as P
  cross apply (
              select top(cast(5 * P.DaysToManufacture as bigint))
                T.TransactionID,
                T.TransactionDate
              from Production.TransactionHistory as T
              where P.ProductID = T.ProductID
              order by T.TransactionDate desc
              ) as T
where P.Name >= N'M' and
      P.Name < N'S';

Główną różnicą między nimi jest to, że apply topfiltry w górnym wyrażeniu poniżej zagnieżdżonych pętli łączą się, gdy row_numberwersja filtruje po złączeniu. Oznacza to, że jest więcej odczytów Production.TransactionHistoryniż jest to naprawdę konieczne.

Jeśli istniałby tylko sposób na popchnięcie operatorów odpowiedzialnych za wyliczanie wierszy do dolnej gałęzi przed złączeniem, wówczas row_numberwersja mogłaby działać lepiej.

Więc wpisz apply row_number()wersję.

select T.TransactionID, 
       T.TransactionDate
from Production.Product as P
  cross apply (
              select T.TransactionID,
                     T.TransactionDate
              from (
                   select T.TransactionID,
                          T.TransactionDate,
                          row_number() over(order by T.TransactionDate desc) as rn
                   from Production.TransactionHistory as T
                   where P.ProductID = T.ProductID
                   ) as T
              where T.rn <= cast(5 * P.DaysToManufacture as bigint)
              ) as T
where P.Name >= N'M' and
      P.Name < N'S';

Jak widać, apply row_number()jest prawie taki sam, jak apply toptylko nieco bardziej skomplikowany. Czas wykonania jest mniej więcej taki sam lub nieco dłuższy.

Dlaczego więc zadałem sobie trud znalezienia odpowiedzi, która nie jest lepsza niż to, co już mamy? Cóż, masz jeszcze jedną rzecz do wypróbowania w prawdziwym świecie, a tak naprawdę jest różnica w odczytach. Taki, dla którego nie mam wyjaśnienia *.

APPLY - ROW_NUMBER
(961 row(s) affected)
Table 'TransactionHistory'. Scan count 115, logical reads 230, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Product'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

APPLY - TOP
(961 row(s) affected)
Table 'TransactionHistory'. Scan count 115, logical reads 268, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Product'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

W tym momencie równie dobrze mogę wrzucić drugą row_number()wersję, która w niektórych przypadkach może być właściwą drogą. Te niektóre przypadki byłyby wtedy, gdy spodziewasz się, że faktycznie potrzebujesz większości wierszy, Production.TransactionHistoryponieważ tutaj otrzymujesz połączenie scalające Production.Producti wyliczone Production.TransactionHistory.

with C as
(
  select T.TransactionID,
         T.TransactionDate,
         T.ProductID,
         row_number() over(partition by T.ProductID order by T.TransactionDate desc) as rn
  from Production.TransactionHistory as T
)
select C.TransactionID,
       C.TransactionDate
from C
 inner join Production.Product as P
      on P.ProductID = C.ProductID
where P.Name >= N'M' and
      P.Name < N'S' and
      C.rn <= 5 * P.DaysToManufacture;

Aby uzyskać powyższy kształt bez operatora sortowania, należy również zmienić indeks pomocniczy, aby uporządkować, TransactionDatemalejąco.

create index IX_TransactionHistoryX on 
  Production.TransactionHistory(ProductID, TransactionDate desc)

^* Edycja: Dodatkowe logiczne odczyty wynikają z zagnieżdżonego pobierania wstępnego pętli używanego z aplikatorem . Możesz to wyłączyć za pomocą niezaszyfrowanego TF 8744 (i / lub 9115 w późniejszych wersjach), aby uzyskać taką samą liczbę logicznych odczytów. Pobieranie wstępne może być zaletą alternatywnej aplikacji w odpowiednich okolicznościach. - Paul White

Zazwyczaj używam kombinacji CTE i funkcji okienkowania. Możesz uzyskać tę odpowiedź, używając czegoś takiego:

;WITH GiveMeCounts
AS (
    SELECT CustomerID
        ,OrderDate
        ,TotalAmt

        ,ROW_NUMBER() OVER (
            PARTITION BY CustomerID ORDER BY 
            --You can change the following field or sort order to whatever you'd like to order by.
            TotalAmt desc
            ) AS MySeqNum
    )
SELECT CustomerID, OrderDate, TotalAmt
FROM GiveMeCounts
--Set n per group here
where MySeqNum <= 10

W przypadku części dodatkowej kredytu, w której różne grupy mogą chcieć zwrócić inną liczbę wierszy, można użyć oddzielnej tabeli. Powiedzmy, używając kryteriów geograficznych, takich jak stan:

+-------+-----------+
| State | MaxSeqnum |
+-------+-----------+
| AK    |        10 |
| NY    |         5 |
| NC    |        23 |
+-------+-----------+

Aby to osiągnąć, gdy wartości mogą być różne, musisz dołączyć CTE do tabeli stanu podobnej do tej:

SELECT [CustomerID]
    ,[OrderDate]
    ,[TotalAmt]
    ,[State]
FROM GiveMeCounts gmc
INNER JOIN StateTable st ON gmc.[State] = st.[State]
    AND gmc.MySeqNum <= st.MaxSeqNum