Dlaczego zapytanie zagregowane jest znacznie szybsze z klauzulą ​​GROUP BY niż bez niej?

Jestem tylko ciekawy, dlaczego zapytanie zagregowane działa o wiele szybciej z GROUP BYklauzulą ​​niż bez niej.

Na przykład uruchomienie tego zapytania zajmuje prawie 10 sekund

SELECT MIN(CreatedDate)
FROM MyTable
WHERE SomeIndexedValue = 1

Podczas gdy ten zajmuje mniej niż sekundę

SELECT MIN(CreatedDate)
FROM MyTable
WHERE SomeIndexedValue = 1
GROUP BY CreatedDate

W CreatedDatetym przypadku jest tylko jeden , więc zgrupowane zapytanie zwraca te same wyniki, co zgrupowane.

Zauważyłem, że plany wykonania dwóch zapytań są różne - drugie zapytanie korzysta z równoległości, a pierwsze nie.

Czy to normalne, że SQL Server inaczej ocenia zapytanie zagregowane, jeśli nie ma klauzuli GROUP BY? Czy jest coś, co mogę zrobić, aby poprawić wydajność pierwszego zapytania bez użycia GROUP BYklauzuli?

Edytować

Właśnie się nauczyłem, że mogę OPTION(querytraceon 8649)ustawić narzut kosztów równoległości na 0, co sprawia, że ​​zapytanie korzysta z pewnej równoległości i skraca czas działania do 2 sekund, chociaż nie wiem, czy jest jakaś wada korzystania z tej wskazówki zapytania.

SELECT MIN(CreatedDate)
FROM MyTable
WHERE SomeIndexedValue = 1
OPTION(querytraceon 8649)

Wciąż wolę krótszy czas wykonywania, ponieważ zapytanie ma wypełniać wartość po wybraniu użytkownika, więc idealnie powinno być natychmiastowe, tak jak zapytanie zgrupowane. W tej chwili właśnie kończę moje zapytanie, ale wiem, że to nie jest idealne rozwiązanie.

SELECT Min(CreatedDate)
FROM
(
    SELECT Min(CreatedDate) as CreatedDate
    FROM MyTable WITH (NOLOCK) 
    WHERE SomeIndexedValue = 1
    GROUP BY CreatedDate
) as T

Edytuj # 2

W odpowiedzi na prośbę Martina o dodatkowe informacje :

Zarówno CreatedDatei SomeIndexedValuemają oddzielny non-unikalny, nieklastrowanym indeks na nich. SomeIndexedValuejest właściwie polem varchar (7), mimo że przechowuje wartość liczbową wskazującą na PK (int) innej tabeli. Relacja między dwiema tabelami nie jest zdefiniowana w bazie danych. Nie mam w ogóle zmieniać bazy danych i mogę pisać tylko zapytania, które wyszukują dane.

MyTablezawiera ponad 3 miliony rekordów, a do każdego rekordu przypisana jest grupa, do której należy ( SomeIndexedValue). Grupy mogą mieć od 1 do 200 000 rekordów

Wygląda na to, że prawdopodobnie śledzi indeks CreatedDatew kolejności od najniższej do najwyższej i wykonuje wyszukiwania w celu oceny SomeIndexedValue = 1predykatu.

Gdy znajdzie pierwszy pasujący wiersz, jest to zrobione, ale może wykonywać znacznie więcej wyszukiwań, niż się spodziewa, zanim znajdzie taki wiersz (zakłada, że ​​wiersze pasujące do predykatu są losowo rozmieszczane według daty).

Zobacz moją odpowiedź tutaj na podobny problem

Idealny indeks dla tego zapytania byłby taki sam SomeIndexedValue, CreatedDate. Zakładając, że nie możesz tego dodać lub przynajmniej utworzyć istniejącego indeksu na SomeIndexedValueokładce CreatedDatejako dołączonej kolumny, możesz spróbować przepisać zapytanie w następujący sposób

SELECT MIN(DATEADD(DAY, 0, CreatedDate)) AS CreatedDate
FROM MyTable
WHERE SomeIndexedValue = 1

aby zapobiec korzystaniu z tego konkretnego planu.

Czy możemy kontrolować MAXDOP i wybrać znaną tabelę, np. AdventureWorks.Production.TransactionHistory?

Kiedy powtórzę konfigurację za pomocą

--#1
SELECT MIN(TransactionDate) 
FROM AdventureWorks.Production.TransactionHistory
WHERE TransactionID = 100001 
OPTION( MAXDOP 1) ;

--#2
SELECT MIN(TransactionDate) 
FROM AdventureWorks.Production.TransactionHistory
WHERE TransactionID = 100001 
GROUP BY TransactionDate
OPTION( MAXDOP 1) ;
GO 

koszty są identyczne.

Nawiasem mówiąc, spodziewałbym się (sprawił, że tak się stanie) poszukiwania indeksu na podstawie wartości indeksowanej; w przeciwnym razie prawdopodobnie zobaczysz dopasowania skrótów zamiast agregacji strumienia. Możesz poprawić wydajność za pomocą indeksów nieklastrowych, które zawierają agregowane wartości, lub utworzyć indeksowany widok, który definiuje agregaty jako kolumny. Następnie trafiałbyś do indeksu klastrowego, który zawiera twoje agregacje, za pomocą indeksu indeksowanego. W SQL Standard możesz po prostu utworzyć widok i użyć podpowiedzi Z (NOEXPAND).

Przykład (nie używam MIN, ponieważ nie działa w widokach indeksowanych):

USE AdventureWorks ;
GO

-- Covering Index with Include
CREATE INDEX IX_CoverAndInclude
ON Production.TransactionHistory(TransactionDate) 
INCLUDE (Quantity) ;
GO

-- Indexed View
CREATE VIEW dbo.SumofQtyByTransDate
    WITH SCHEMABINDING
AS
SELECT 
      TransactionDate 
    , COUNT_BIG(*) AS NumberOfTransactions
    , SUM(Quantity) AS TotalTransactions
FROM Production.TransactionHistory
GROUP BY TransactionDate ;
GO

CREATE UNIQUE CLUSTERED INDEX SumofAllChargesIndex 
    ON dbo.SumofQtyByTransDate (TransactionDate) ;  
GO


--#1
SELECT SUM(Quantity) 
FROM AdventureWorks.Production.TransactionHistory 
WITH (INDEX(0))
WHERE TransactionID = 100001 
OPTION( MAXDOP 1) ;

--#2
SELECT SUM(Quantity)  
FROM AdventureWorks.Production.TransactionHistory 
WITH (INDEX(IX_CoverAndInclude))
WHERE TransactionID = 100001 
GROUP BY TransactionDate
OPTION( MAXDOP 1) ;
GO 

--#3
SELECT SUM(Quantity)  
FROM AdventureWorks.Production.TransactionHistory
WHERE TransactionID = 100001 
GROUP BY TransactionDate
OPTION( MAXDOP 1) ;
GO

Moim zdaniem przyczyną problemu jest to, że optymalizator serwera SQL nie szuka planu BEST, a raczej dobrego planu, co widać po tym, że po wymuszeniu równoległości zapytanie wykonało się znacznie szybciej, coś, co optymalizator miał nie zrobione na własną rękę.

Widziałem także wiele sytuacji, w których przepisywanie zapytania w innym formacie stanowiło różnicę między równoległością (na przykład chociaż większość artykułów na temat SQL poleca parametryzację, zauważyłem, że czasami powoduje to paralelizację, nawet jeśli parametry wąchane były takie same jak inne - zrównoleglenie jednego lub połączenie dwóch zapytań z UNION ALL może czasem wyeliminować równoległość).

W związku z tym poprawnym rozwiązaniem może być wypróbowanie różnych sposobów pisania zapytania, takich jak wypróbowanie tabel tymczasowych, zmiennych tabel, cte, tabel pochodnych, parametryzacji itd., A także odtwarzanie indeksów, widoków indeksowanych lub indeksów filtrowanych w aby uzyskać najlepszy plan.