Temat: [MSSQL]Duża tabela i sumowanie

Witam

Mam pewien problem z optymalizacją procedury. Tabela zawiera 7 362 837 rekordów, nie ma klucza głównego. Wygląda mniej więcej tak:

CreateDate datetime,
WidgetId int,
HostId int,
ProgramId int
Count int

Potrzebuje zsumować pole Count po HostId. Problem jednak tkwi w wydajności, proste zapytanie:

select hostid, sum(count) from tabela group by hostid

wykonuje się ponad 40 sekund. W jaki sposób można zoptymalizować taką procedure?
Czy założenie indeksu na tabele bez klucza głównego może coś pomóc?

Z góry dziekuje za podpowiedzi

Temat: [MSSQL]Duża tabela i sumowanie

Index moze pomoc zwlaszcza jak dobrze zostana ustawione warunki aktualizacji indeksu.

Dane musza byc caly czas aktualne czy dopuszczalne jest niewielkie odchylenie ?.

Jesli nie to rozpatrz utworzenie tabeli ze statystykami i za pomoca SQL Job Engine odpalaj aktualizacje co jakis czas.

Temat: [MSSQL]Duża tabela i sumowanie

załóż index
BTW ->
a jakie masz ograniczenia żeby dodatkowo dodać kolumnę id i nadać jej PK?

Temat: [MSSQL]Duża tabela i sumowanie

Można założyć jak najwęższy indeks zawierający tylko pola z kryterium grupowania. Bez warunku filtra i tak będzie skan - ale przynajmniej mniejszej liczby stron.

pozdrawiam

Temat: [MSSQL]Duża tabela i sumowanie

Rozumiem, że mamy pola

CreateDate datetime,
WidgetId int,
HostId int,
ProgramId int
Count int

bez żadnego indeksu.

W MS SQLu najwazniejsze jest zbudowanie indeksu klastrowego (clustered index) - to według tego indeksu poukładane są strony danych w bazie danych. Inaczej dane z tabeli są przeważnie strasznie "rozfragmentowane".
Pierwsze pole w indeksie powinno zawierać pole grupowane - hostid. Pozostałe - wedlug uznania, ale z punktu widzenia indeksu klastrowego najlepiej byłoby wybrać minimalną liczbę prostych pól, które utworzą klucz główny, choć nie jest to konieczne i można zbudować indeks wyłącznie z polem hostid.

Jeżeli zapytanie grupujące:
select hostid, sum(count) from tabela group by hostid
wywoływane jest bardzo często, to proponuję dodatkowo utworzyć widok z tym zapytaniem i zrobić na nim indeks. W MS SQLu indeks na takim widoku może być wykorzystany nawet, gdy nie będziemy się odwoływać do widoku, tylko to tabeli źródłowej, na której ten widok jest postawiony. Indeks na widoku będzie aktualizowany przy każdej zmianie w tabeli (schemabinding), więc zmaterializowany indeks na tym widoku będzie zawierał zawsze wszystkie potrzebne informacje. Dostęp do stron danych tabeli nie jest wówczas konieczny.

Tabela nie zajmuje zbyt dużo, dla indeksu klastrowego dane powinny zajmować ok. 7,36 mln * 30+ >= 200 MB (bez indeksu klastrowego dane zajmują więcej!).
Przypuszczam, że różnych hostid może być ze 100 k. Indeks na widoku powinien zajmować ok. 0,1 mln * 8 ~= 1 MBKrzysztof Gil edytował(a) ten post dnia 02.08.12 o godzinie 11:47

Temat: [MSSQL]Duża tabela i sumowanie

Krzysztof Gil:

Jeżeli zapytanie grupujące:
select hostid, sum(count) from tabela group by hostid
wywoływane jest bardzo często, to proponuję dodatkowo utworzyć widok z tym zapytaniem i zrobić na nim indeks. W MS SQLu indeks na takim widoku może być wykorzystany nawet, gdy nie będziemy się odwoływać do widoku, tylko to tabeli źródłowej, na której ten widok jest postawiony. Indeks na widoku będzie aktualizowany przy każdej zmianie w tabeli (schemabinding), więc zmaterializowany indeks na tym widoku będzie zawierał zawsze wszystkie potrzebne informacje. Dostęp do stron danych tabeli nie jest wówczas konieczny.

Zapomniałeś dodać, że takie cuda to się dzieją tylko w enterprise i developer. W niższych wariantach należy jawnie się odwołać do widoku indeksowanego