Część zapytania maksymalizująca procesor przez długi czas to funkcje w klauzuli GROUP BY i fakt, że w tym przypadku grupowanie zawsze będzie wymagało nieindeksowanego sortowania. Chociaż indeks pola znacznika czasu pomoże w początkowym filtrze, tę operację należy wykonać w każdym wierszu, który odpowiada filtrowi. Przyspieszy to korzystanie z bardziej wydajnej trasy do wykonania tej samej pracy, co sugeruje Alex, ale nadal masz tam ogromną nieefektywność, ponieważ jakakolwiek kombinacja funkcji, której używasz w narzędziu do planowania zapytań, nie będzie w stanie wymyślić coś, co pomoże jakikolwiek indeks, więc będzie musiał najpierw przejść przez każdy wiersz, uruchamiając funkcje, aby obliczyć wartości grupowania, tylko wtedy może uporządkować dane i obliczyć agregacje na podstawie wynikowych grup.
Tak więc rozwiązaniem jest jakoś utworzenie grupy procesów przez coś, do czego może użyć indeksu, lub w inny sposób wyeliminować potrzebę uwzględnienia wszystkich pasujących wierszy naraz.
Możesz zachować dodatkową kolumnę dla każdego wiersza zawierającą czas zaokrąglony do godziny i zindeksować tę kolumnę do użycia w takich zapytaniach. To denormalizuje dane, więc może wydawać się „brudny”, ale działałoby i byłoby czystsze niż buforowanie wszystkich agregatów do przyszłego użytku (i aktualizowanie tej pamięci podręcznej w miarę zmiany danych podstawowych). Dodatkowa kolumna powinna być utrzymywana przez wyzwalacz lub być utrwaloną kolumną obliczeniową, a nie utrzymywana logiką gdzie indziej, ponieważ zapewni to wszystkie obecne i przyszłe miejsca, w których można wstawić dane lub zaktualizować kolumny znaczników czasu lub istniejące wiersze, aby uzyskać spójne dane w nowym kolumna. Nadal możesz uzyskać MIN (znacznik czasu). To, co spowoduje zapytanie w ten sposób, to wciąż spacer po wszystkich wierszach (oczywiście nie można tego uniknąć), ale można to zrobić w kolejności indeksowania, wyprowadzanie wiersza dla każdej grupy, gdy dochodzi do następnej wartości w indeksie, zamiast konieczności pamiętania całego zestawu wierszy dla operacji sortowania w nieindeksowanym pliku, zanim będzie można wykonać grupowanie / agregację. Zużyje również dużo mniej pamięci, ponieważ nie będzie musiał pamiętać żadnych wierszy z poprzednich wartości grupowania w celu przetworzenia tego, na który patrzy teraz, lub reszty.
Ta metoda eliminuje potrzebę znalezienia gdzieś w pamięci dla całego zestawu wyników i wykonania nieindeksowanego sortowania dla operacji grupy i usuwa obliczenia wartości grupy z dużego zapytania (przeniesienie tego zadania do poszczególnych INSERT / UPDATE, które powodują dane) i powinny umożliwiać uruchamianie takich zapytań w sposób akceptowalny, bez konieczności prowadzenia osobnego magazynu zagregowanych wyników.
Metoda, która tego nie robizdenormalizuj swoje dane, ale nadal wymaga dodatkowej struktury, to użyć „harmonogramu”, w tym przypadku jednego zawierającego jeden wiersz na godzinę przez cały czas, jaki możesz wziąć pod uwagę. Ta tabela nie zajmowałaby znacznej ilości miejsca w DB lub znacznej wielkości - w celu objęcia 100-letniego okresu tabelą zawierającą jeden wiersz dwóch dat (początek i koniec godziny, na przykład „2011-01-01 @ 00: 00: 00.0000 ”,„ 2011-01-01 @ 00: 00: 59.9997 ”, przy czym„ 9997 ”jest najmniejszą liczbą milisekund pola DATETIME nie zaokrągli w górę do następnej sekundy), które są częścią klastrowany klucz podstawowy zajmie ~ 14 MB miejsca (8 + 8 bajtów na wiersz * 24 godziny / dzień * 365,25 dni / rok * 100, plus trochę narzut na strukturę drzewa indeksu klastrowanego, ale ten narzut nie będzie znaczący) .
SELECT CONVERT(VARCHAR, [timestamp], 1)+' '+ CAST(DATEPART(Hh,[timestamp]) as VARCHAR) AS TimeStampHour
, MIN([timestamp]) as TimeStamp
, AVG(MyField) As AvgField
FROM TimeRangeByHours tt
INNER JOIN MyData md ON md.TimeStamp BETWEEN tt.StartTime AND tt.EndTime
WHERE tt.StartTime > '4/10/2011'
GROUP BY tt.StartTime
ORDER BY tt.StartTime
Oznacza to, że planista zapytań może zorganizować użycie indeksu na MyData.TimeStamp. Planista zapytań powinien być na tyle jasny, aby zorientować się, że może przejść po tabeli oswajania zgodnie z indeksem MyData.TimeStamp, ponownie generując jeden wiersz na grupę i odrzucając każdy zestaw lub wiersze, gdy trafi następną wartość grupowania. Nie ma potrzeby przechowywania wszystkich pośrednich wierszy gdzieś w pamięci RAM, a następnie przeprowadzania na nich sortowania bez indeksów. Oczywiście ta metoda wymaga utworzenia harmonogramu i upewnienia się, że rozciąga się wystarczająco daleko, zarówno do tyłu, jak i do przodu, ale można go używać do zapytań dotyczących wielu pól daty w różnych zapytaniach, gdzie jako opcja „dodatkowej kolumny” wymagałaby dodatkową kolumnę obliczeniową dla każdego pola daty, którą trzeba było przefiltrować / pogrupować w ten sposób, oraz niewielki rozmiar tabeli (chyba że jest potrzebny do rozciągnięcia na 10,
Metoda tabeli czasowej ma dodatkową różnicę (która może być dość korzystna) w porównaniu z twoją obecną sytuacją i rozwiązaniem kolumny obliczeniowej: może zwracać wiersze dla okresów, dla których nie ma danych, po prostu zmieniając ŁĄCZENIE WEWNĘTRZNE w powyższym przykładowym zapytaniu być LEWĄ ZEWNĘTRZNĄ.
Niektóre osoby sugerują brak fizycznego harmonogramu, ale zamiast tego zawsze zwracają go z funkcji zwracającej tabelę. Oznacza to, że zawartość harmonogramu nigdy nie jest przechowywana na dysku (lub należy go odczytać), a jeśli funkcja jest dobrze napisana, nigdy nie musisz się martwić o to, jak długo przedział czasowy musi się rozciągać w przód iw tył, ale ja wątpię w koszt procesora związany z produkcją tabeli w pamięci dla niektórych wierszy, każde zapytanie jest warte niewielkiego zaoszczędzenia na kłopotach z tworzeniem (i utrzymywaniem, jeśli jego czas musi przekraczać limit początkowej wersji) fizycznego harmonogramu.
Dodatkowa uwaga: nie potrzebujesz tej klauzuli DISTINCT w pierwotnym zapytaniu. Grupowanie zagwarantuje, że te zapytania zwracają tylko jeden wiersz na rozważany okres, więc DISTINCT nie zrobi nic więcej poza obróceniem procesora nieco więcej (chyba że planista zapytań zauważy, że odrębne byłoby brakiem operacji, w którym to przypadku zrobi to zignoruj to i nie używaj dodatkowego czasu procesora).