Indeksy przy pobieraniu danych w MSSQL

Załóżmy, że mam tabelę:

create table Samochody
(
    Zrodlo1 varchar(20) not null,
    Zrodlo2 varchar(20) not null,
    Marka varchar(100) not null,
    Kolor varchar(100) not null,
    MocSilnika varchar(100) not null,
    KosztZakupu numeric not null,
    WartoscSprzedazy numeric not null
)

Do tabeli są wprowadzane dane z różnych źródeł (źródło identyfikowane za pomocą Zrodlo1 i Zrodlo2) i przewiduje się ewentualną aktualizację danych w razie potrzeby. Aktualizacja polega na usunięciu danych mających konkretną wartość pól Zrodlo1 i Zrodlo2, a potem wstawienie na nowo danych. Oczywiście możliwe jest dopisanie danych z innego źródła, jak też rezygnacja z danych z określonego źródła. Jednakże, zestawy wartości pól Marka, Kolor, MocSilnika nie powtarzają się, ale wartość jednego z tych pól może się powtarzać.

Aby zapewnić wydajność przy kontroli, czy są dane z danego źródła i usuwaniu danych z konkrentego źródła, w tabeli jest założony indeks klastrowany na polach Zrodlo1 i Zrodlo2.

Natomiast korzystanie z danych polega na wywołaniu zapytania np.:

select sum(KosztZakupu), sum(WartoscSprzedazy), Marka, Kolor, MocSilnika from Samochody group by Marka, Kolor, MocSilnika
select sum(KosztZakupu), sum(WartoscSprzedazy), Marka, Kolor from Samochody group by Marka, Kolor
select sum(KosztZakupu), sum(WartoscSprzedazy), Kolor, MocSilnika from Samochody group by Kolor, MocSilnika
select sum(KosztZakupu), sum(WartoscSprzedazy) from Samochody

W tym przypadku nie bierze się pod uwagę pól Zrodlo1 i Zrodlo2, a dopuszczalne są wszystkie możliwe grupowania pól Marka, Kolor, MocSilnika (czyli jest 8 możliwości), każde grupowanie z podobną częstotliwością zapotrzebowania. W jaki sposób najlepiej usprawnić pobieranie danych?
Mi do głowy przychodzą następujące działania:

1. Założyć indeks nieklastrowany na każde z pól grupowania (Marka, Kolor, MocSilnika), w tym przypadku będa trzy indeksy.
2. Założyć jeden indeks nieklastrowany na wszystkie pola grupowania (Marka, Kolor, MocSilnika). Czy kolejnosć pól w indeksie ma znaczenie?
3. Założyć indeks nieklastrowany na każde możliwe grupowanie, w tym przypadku będzie to 7 indeksów (bo nie da się założyć indeksu bez podawania pól).
4. Rozszerzyć indeks klastrowany na wszystkie pola, czyli indeks klastrowany obejmowałby pola (Zrodlo1, Zrodlo2, Marka, Kolor, MocSilnika). Będzie to mieć istotny wpływ na edycję danych w opisany wyżej sposób?

Pobór danych ma miejsce istotnie częściej niż ewentualna kontrola i aktualizacja danych.

Ja bym zaczął od 1 unikalnego indeksu na Marka, Kolor i MocSilnika bo jak napisałeś one się nie powtarzają i sprawdził wydajność zapytań. Indeksy czasami można dodawać na zasadzie prób i błędów więc sprawdź sam, które rozwiązanie będzie dla Ciebie najkorzystniejsze

1. Ile jest tych danych?
2. Jaki % unikalności masz po Marka, Kolor, Moc silnika?
3. Czy te agregacje to jedyne zapytania na tej tabeli? (Pomijając aktualizację po zrodlo1/zrodlo2)

Zacznij od palny zapytania. Możesz też rzucić okiem na to: https://www.brentozar.com/archive/2015/06/indexing-for-group-by/ - pamiętaj, że kolejne indeksy będą psuły wydajność przy dodawaniu rekordów, Nie wiem, czy to jest ważne w twoim przypadku, ale należy zwrócić na to uwagę.

Aby zapewnić wydajność przy kontroli, czy są dane z danego źródła i usuwaniu danych z konkrentego źródła, w tabeli jest założony indeks klastrowany na polach Zrodlo1 i Zrodlo2.

Dla mnie nie ma to sensu, zakladanie pogrupowanego indeksu tylko w celu przyspieszenia usuwania (zakładam, że to operacja wykonywana raz na jakiś czas i sama wydajność nie wydaje się kluczowa)
gdzie i tak odpytujesz wszystkie dane bez względu na źródło.

Samo konstrukcja trzymania tego w osobnych 2 polach jakaś pokraczna, czemu nie w jednym? Jak dojdzie 3 źródło to dodasz kolumne zrodlo3

yarel napisał(a):

1. Ile jest tych danych?

2. Jaki % unikalności masz po Marka, Kolor, Moc silnika?
3. Czy te agregacje to jedyne zapytania na tej tabeli? (Pomijając aktualizację po zrodlo1/zrodlo2)

1. Od kilku milionów do ok. 20 milionów pozycji
2. W docelowym rozwiązaniu takich kolumn do grupowania ma być ok. 10, nie wiem, jak rozumieć "% unikalności", dla jednej konkrentnej kombinacji wartości cech od kilku do kilkudziesięciu pozycji.
3. Zapytania pobierające dane to wymienione agregacje (obejmujące wszystkie możliwe grupowania pól cech) oraz select Zrodlo1, Zrodlo2 from Samochody group by Zrodlo1, Zrodlo2 w celu uzyskania informacji, z jakich źródeł są wprowadzone dane.

Panczo napisał(a):

Aby zapewnić wydajność przy kontroli, czy są dane z danego źródła i usuwaniu danych z konkrentego źródła, w tabeli jest założony indeks klastrowany na polach Zrodlo1 i Zrodlo2.

Dla mnie nie ma to sensu, zakladanie pogrupowanego indeksu tylko w celu przyspieszenia usuwania (zakładam, że to operacja wykonywana raz na jakiś czas i sama wydajność nie wydaje się kluczowa)
gdzie i tak odpytujesz wszystkie dane bez względu na źródło.

Samo konstrukcja trzymania tego w osobnych 2 polach jakaś pokraczna, czemu nie w jednym? Jak dojdzie 3 źródło to dodasz kolumne zrodlo3

Jedno źródło danych to nie jedna wartość, tylko zestaw wartości. W tej koncepcji sa to dwie wartości, jak zajdzie potrzeba, że mają być trzy, to dojdzie pole Zrodlo3. Jednakże nie wykluczam możliwości przerobienia na jedno pole, w którym wartość będzie jednoznacznie identyfikować źródło danych, np. wartość jest tekstem Wartosc1 + '|' + Wartosc2, gdzie w samej wartości znak | nie ma prawa wystąpić, a i tak klient bazy danych generuje listę wartości. Najpierw następuje select Zrodlo1, Zrodlo2 from Samochody group by Zrodlo1, Zrodlo2, odpowiedź jest porównywana z listą z innego miejsca (to porównanie robi klient bazy danych) i jeżeli jest jakaś wartość, która jest w odpowiedzi, a nie ma na liście, to jest robiony delete from Samochody where Zrodlo1 = 'Wartosc1' and Zrodlo2 = 'Wartosc2'. A jak jest odwrotnie, czyli jakaś para wartości jest na liście kontrolnej, a nie ma w tabeli, to jest robiony insert into Samochody (Zrodlo1, Zrodlo2, Marka, Model, PojemnoscSilnika, KwotaZakupu, KwotaSprzedazy) select 'Wartosc1', 'Wartosc2', BardzoSkomplikowaneZapytanieSelect, gdzie wprowadzane dane są tak naprawdę odpowiedzią na bardziej skomplikowany select. Kontrola źródeł z listą następuje równie często, co pobór agregacji, a ewentualne zmiany na liście kontrolnej, które pociągają odpowiednie modyfikacje tabeli następują rzadko.

Jedno źródło danych to nie jedna wartość, tylko zestaw wartości.

To bardziej by pasowała konstrukcja: tabela ze źródłami, z dowolnymi atrybutami, bezproblemowa do rozbudowy/zmiany, ID źródła w tabeli z danymi.

Panczo napisał(a):

Jedno źródło danych to nie jedna wartość, tylko zestaw wartości.

To bardziej by pasowała konstrukcja: tabela ze źródłami, z dowolnymi atrybutami, bezproblemowa do rozbudowy/zmiany, ID źródła w tabeli z danymi.

Według pierwszej lub drugiej postaci normalnej tak nawet być powinno, testujemy system i nie wykluczone, że coś takiego będzie.

andrzejlisek napisał(a):

Panczo napisał(a):

Jedno źródło danych to nie jedna wartość, tylko zestaw wartości.

To bardziej by pasowała konstrukcja: tabela ze źródłami, z dowolnymi atrybutami, bezproblemowa do rozbudowy/zmiany, ID źródła w tabeli z danymi.

Według pierwszej lub drugiej postaci normalnej tak nawet być powinno, testujemy system i nie wykluczone, że coś takiego będzie.

W inżynierii pewne rzeczy się WIE a nie "testuje". Inżynieria jest teorią i praktyką osiągania celów, a nie miotaniem z kąta w kąt. Myśl uprzedza realizację, a nie an odwrót.

nie to że jestem religijnym zwolennikiem 3ciej postaci, lubię 2 1/2, ale pierwsza postać normalna nie jest i nigdy nie była żeby ja implementować, tylko jako etap analizy

Jak komentowano wcześniej - utrzymanie 2 kolumn 'zrodlo' zamiast jednej + tabela ze zrodlami nie jest najlepszym pomysłem. A już w ogóle "big no no" sklejanie wartości w jednej kolumnie 'zrodlo'. To proszenie się o kłopoty w utrzymaniu. Niemniej jest jak jest :-) Masz na nich indeks klastrowy co oznacza, że dane w tabeli posortowane są wg kolumn zrodlo1, zrodlo2. Dla wymienionych zapytań agregujących ten indeks klastrowy jest nieprzydatny. W zaprezentowanych zapytaniach nie ma klauzuli 'where', więc zawsze czytana jest cała tabela. Grupowanie nigdy nie jest po kolumnach 'zrodlo1/2', więc posortowanie danych w tabeli wg zrodel nie jest przydatne dla zapytań. Zakładanie kolejnych indeksów nie ma sensu ponieważ nie zmienią sortu na tabeli, a zawsze trzeba odczytać wszystkie dane bo nie ma 'where'.

Moje sugestie do rozważenia/stestowania:
#1 zmienić indeks klastrowy na zbudowany na kolumnach 'KosztZakupu', 'WartoscSprzedazy'. Indeks klastrowy wartości kolumn przechowuje w liściach, więc zapytanie:
select sum(KosztZakupu), sum(WartoscSprzedazy) from Samochody
powinno działać szybciej - wykona skan indeksu zamiast skanu tabeli. Z dysku odczyta tylko bloki z indeksem.
Co do usuwania danych wg źródła to trzeba policzyć liczność krotek z każdego źródła. Jeśli jest 50/50 to spodziewam się, że optymalizator i tak przeskanuje całą tabelę. Jeśli jest znaczna dysproporcja np. 20/80 to wtedy indeks tylko na kolumnie źródła, które ma 20% krotek. Dla 80% i tak będzie skan tabeli.

Innymi słowy dla motoru bazy danych odczytanie 50% tabeli po indeksie jest mniej opłacalne niż skanowanie 100% tabeli. Korzystanie z indeksu to dla motoru 3 IO aby dostać dane. 2IO jeśli są w liściach indeksu. Skan tabeli to 1IO, który może być robiony w duuużych blokach dyskowych po wiele stron bazodanowych - bardziej wydajnie. Na dodatek skan tabeli nie zaśmieca cache bazy danych.

#2 zawsze sumujesz KosztZakupu i WartoscSprzedazy - przemyślałbym trzymanie danych wstępnie zagregowanych pod tym kątem aby za każdym razem nie sumować wszystkich krotek. Czyli zsumować dla każdej kombinacji marki, koloru i mocy silnika - bo jak piszesz to jest unikalne. A wynik końcowy obliczać sumując te wcześniejsze podsumy. Innymi słowy - nie musisz przechowywać każdego rekordu zakupu/sprzedaży audi. Nigdy nie oglądasz jakimi egzemplarzami audi handlowałeś. Wystarczy Ci suma zakupu/sprzedaży dla marki audi. Podobnie koloru i mocy silnika. Później te podsumy tylko dodajesz na różne sposoby.

Tytułem komentarza - schemat tabeli bardziej odpowiada systemowi OLTP, natomiast użycie to hurtownia zwracająca zagregowane dane finansowe. Wynika to również z Twojego opisu - wyciąganie danych znacznie częstsze niż modyfikacje. Doczytałbym na Twoim miejscu o kostkach OLAP i ETL - może się nada.

Właśnie dokonałem następujących zmian:

1. Jedno pole na źródło danych.
2. Po polu "zrodlo" indeks nieklastrowany.
3. Po pozostałych polach z danymi nieliczbowymi indeks klastrowany.

W międzyczasie stwierdziłem, że w indeksie nieklastrowanym maksymalna wielkość pozycji danych może wynieść 1700 bajtów. Przy zakładaniu indeksu pokazuje sie takie ostrzeżenie.

Co do OLAP, to doczytam, a przechowywanie podsumowanych danych jest i takt przewidziane.