Próbuję zwiększyć wydajność dzięki zapytaniu, które uzyskuje dostęp do tabeli zawierającej ~ 250 milionów rekordów. Z mojego lektury faktycznego (nieocenionego) planu wykonania pierwsze wąskie gardło to zapytanie, które wygląda następująco:
select
b.stuff,
a.added,
a.value
from
dbo.hugetable a
inner join
#smalltable b on a.fk = b.pk
where
a.added between @start and @end;Zobacz dalsze definicje tabel i indeksów.
Plan wykonania wskazuje, że na #smalltable używana jest zagnieżdżona pętla, a skanowanie indeksu na hugetable jest wykonywane 480 razy (dla każdego wiersza w #smalltable). Wydaje mi się to odwrócone, więc próbowałem wymusić użycie połączenia scalającego:
select
b.stuff,
a.added,
a.value
from
dbo.hugetable a with(index = ix_hugetable)
inner merge join
#smalltable b with(index(1)) on a.fk = b.pk
where
a.added between @start and @end;Indeks, o którym mowa (pełna definicja znajduje się poniżej), obejmuje kolumny fk (predykat złączenia), dodane (używane w klauzuli where) i id (bezużyteczne) w porządku rosnącym oraz zawiera wartość .
Kiedy to robię, zapytanie przepływa z 2 1/2 minuty do ponad 9. Miałem nadzieję, że podpowiedzi wymuszą bardziej wydajne łączenie, które wykonuje tylko jedno przejście nad każdą tabelą, ale najwyraźniej nie.
Wszelkie wskazówki są mile widziane. W razie potrzeby dostarczone dodatkowe informacje.
Aktualizacja (2011/06/02)
Po reorganizacji indeksowania na stole dokonałem znacznego wzrostu wydajności, jednak natrafiłem na nową przeszkodę, jeśli chodzi o podsumowanie danych w ogromnej tabeli. Wynikiem jest podsumowanie według miesięcy, które obecnie wygląda następująco:
select
b.stuff,
datediff(month, 0, a.added),
count(a.value),
sum(case when a.value > 0 else 1 end) -- this triples the running time!
from
dbo.hugetable a
inner join
#smalltable b on a.fk = b.pk
group by
b.stuff,
datediff(month, 0, a.added);Obecnie hugetable ma indeks klastrowany pk_hugetable (added, fk)(klucz podstawowy), a indeks nieklastrowany działa w drugą stronę ix_hugetable (fk, added).
Bez czwartej kolumny powyżej optymalizator używa zagnieżdżonego łączenia pętli, jak poprzednio, używając #smalltable jako wejścia zewnętrznego, a nieklastrowany indeks szuka jako pętli wewnętrznej (wykonując ponownie 480 razy). To, co mnie niepokoi, to rozbieżność między rzędami szacowanymi (12 958,4) a rzeczywistymi (74 668 468). Względny koszt tych poszukiwań wynosi 45%. Czas trwania jest jednak krótszy niż minuta.
Czwarta kolumna skraca czas działania do 4 minut. Tym razem szuka indeksu klastrowego (2 wykonania) dla tego samego kosztu względnego (45%), agreguje poprzez dopasowanie hash (30%), a następnie łączy hash na #smalltable (0%).
Nie jestem pewien, co do mojego następnego sposobu działania. Obawiam się, że ani wyszukiwanie zakresu dat, ani predykat łączenia nie są gwarantowane, a nawet wszystko, co może drastycznie zmniejszyć zestaw wyników. Zakres dat w większości przypadków przycina tylko 10-15% rekordów, a wewnętrzne sprzężenie na fk może odfiltrować może 20-30%.
Zgodnie z życzeniem Willa A wyniki sp_spaceused:
name | rows | reserved | data | index_size | unused
hugetable | 261774373 | 93552920 KB | 18373816 KB | 75167432 KB | 11672 KB#smalltable jest zdefiniowany jako:
create table #endpoints (
pk uniqueidentifier primary key clustered,
stuff varchar(6) null
);Podczas gdy dbo.hugetable jest zdefiniowany jako:
create table dbo.hugetable (
id uniqueidentifier not null,
fk uniqueidentifier not null,
added datetime not null,
value decimal(13, 3) not null,
constraint pk_hugetable primary key clustered (
fk asc,
added asc,
id asc
)
with (
pad_index = off, statistics_norecompute = off,
ignore_dup_key = off, allow_row_locks = on,
allow_page_locks = on
)
on [primary]
)
on [primary];Ze zdefiniowanym następującym indeksem:
create nonclustered index ix_hugetable on dbo.hugetable (
fk asc, added asc, id asc
) include(value) with (
pad_index = off, statistics_norecompute = off,
sort_in_tempdb = off, ignore_dup_key = off,
drop_existing = off, online = off,
allow_row_locks = on, allow_page_locks = on
)
on [primary];Pole id jest redundantne, artefakt z poprzedniego DBA, który nalegał, aby wszystkie tabele wszędzie miały GUID, bez wyjątków.