Jak zoptymalizować zapytanie, aby najpierw szukało jednego indeksu, a potem innego indeksu

Mam dwa zestawy pomiarów ziemi z danych satelitarnych, każdy z polami czasowymi (mjd dla średniej daty julian) i pozycjami geograficznymi (GeoPoint, spacial) i szukam zbieżności między tymi dwoma zestawami, aby ich czasy były zgodne z progiem 3 godziny (lub .125 dni) i ich odległości do 200 km od siebie.

Zrobiłem indeksy zarówno dla pól mjd na obu tabelach, jak i tabelach przestrzennych.

Kiedy po prostu dołączę do ograniczenia czasowego, baza danych oblicza 100 000 dopasowań w 8 sekund i oblicza odległości dla wszystkich 100 000 dopasowań w tym czasie. Zapytanie wygląda następująco:

select top 100000 h.Time, m.Time, h.GeoPoint.STDistance(m.GeoPoint)/1000.0
from L2V5.dbo.header h join L2.dbo.MLS_Header m
on h.mjd between m.mjd-.125 and m.mjd+.125
option( table hint ( h, index(ix_MJD) ), table hint( m, index(ix_MJD) ) )

Zrealizowany plan to:

Po posortowaniu 9 odległości było poniżej 200 km, więc są mecze. Problem polega na tym, że gdy dodam ograniczenie odległości i uruchomię to,

select top 10 h.Time, m.Time, h.GeoPoint.STDistance(m.GeoPoint)/1000.0
from L2V5.dbo.header h join L2.dbo.MLS_Header m
on h.mjd between m.mjd-.125 and m.mjd+.125
and h.GeoPoint.STDistance(m.GeoPoint)<200000
option( table hint ( h, index(ix_MJD) ), table hint( m, index(ix_MJD) ) )

znika na długo. Oczywiście w 8 sekund można było znaleźć 100 000 dopasowań czasowych, z których 9 było poniżej 200 km, więc optymalizator musi spróbować czegoś nieoptymalnego. Plan wygląda podobnie do powyższego z filtrem na odległości (tak sądzę).

Mogę wymusić użycie indeksu przestrzennego za pomocą:

select top 5 h.Time, m.Time, h.GeoPoint.STDistance(m.GeoPoint)/1000.0 
from L2V5.dbo.header h join L2.dbo.MLS_Header m 
on h.GeoPoint.STDistance(m.GeoPoint)<200000
and h.mjd between m.mjd-.125 and m.mjd+.125 
option( table hint ( h, index(ix_MJD), index(ix_GeoPoint) ), table hint( m, index(ix_MJD) ) )

co następnie zajmuje 3 minuty, aby znaleźć 5 meczów.

Jak powiedzieć optymalizatorowi zapytań, aby najpierw używał wyszukiwania indeksu MJD, a następnie indeksu przestrzennego drugi (lub czy to już robi) i czy jest jakiś sposób, aby pomóc, mówiąc, ile dopasowań się spodziewać? Jeśli może obliczyć 100 000 meczów z odległościami w ciągu 8 sekund, które mają 9 poniżej 200 km, czy dodanie indeksu przestrzennego nie przyspieszy, a nie spowolni?

Dziękujemy za wszelkie inne wskazówki lub pomysły.

EDYCJA: Aby odpowiedzieć na pytanie, jak wygląda plan bez podpowiedzi, to (i to trwa wiecznie):

Warto również wspomnieć, że w jednej tabeli jest prawie 1 mln rekordów, a w drugiej 8 mln

Problem polega na tym, że może (i prawdopodobnie znając indeksy przestrzenne) założyć, że filtr przestrzenny będzie znacznie bardziej selektywny niż filtr czasu.

Ale jeśli masz kilka milionów rekordów w odległości 200 km, może być znacznie gorzej.

Poprosisz go o znalezienie rekordów w promieniu 200 km, które zwracają dane uporządkowane według jakiegoś porządku przestrzennego. Znalezienie tam zapisów, które są blisko w czasie, oznacza sprawdzenie każdego z nich.

Albo odnajdujesz rekordy według czasu i osiągasz wyniki w kolejności czasowej. Następnie filtrowanie tej listy do promienia 200 km to kwestia sprawdzenia każdej z nich.

Jeśli odfiltrujesz dane w dwóch zakresach takich jak ten, trudno będzie zastosować drugi filtr za pomocą indeksu. Lepiej jest powiedzieć, że nie używa indeksu przestrzennego, jeśli filtr czasu jest silniejszy.

Jeśli oba są duże indywidualnie i tylko razem są ciasne, masz bardziej złożony problem, który ludzie próbowali rozwiązać od dłuższego czasu, i który można by ładnie rozwiązać za pomocą indeksów obejmujących 3D (i nie tylko) przestrzeń. Tyle że SQL Server ich nie ma.

Przepraszam.

Edycja: więcej informacji ...

Jest to podobny problem jak znalezienie przedziałów czasowych obejmujących określony moment w czasie. Kiedy wyszukujesz rekordy, które zaczynają się przed tym punktem, masz nieuporządkowany bałagan czasów końcowych - i odwrotnie. Jeśli szukasz w książce telefonicznej osób, których nazwiska zaczynają się na F, nie możesz mieć nadziei, że znajdziesz osoby, których imiona zaczynają się na R bardzo łatwo. A indeks imienia nie pomaga z tego samego powodu. Znalezienie rzeczy w następnym indeksie jest trudne, gdy pierwszy indeks nie jest równy.

Teraz, jeśli możesz zmienić swój filtr daty na filtr równości (lub serię filtrów równości), możesz mieć szansę, z wyjątkiem tego, że indeks przestrzenny jest specjalnym rodzajem indeksu i nie może być używany jako drugi poziom w indeks złożony.

Obawiam się, że masz dziwną sytuację. :(

Edycja: Spróbuj:

select top 100000 h.Time, m.Time, h.GeoPoint.STDistance(m.GeoPoint)/1000.0
from L2V5.dbo.header h join L2.dbo.MLS_Header m
on h.mjd between m.mjd-.125 and m.mjd+.125
where h.GeoPoint.STDistance(m.GeoPoint)/1000.0 < 200
option( table hint ( h, index(ix_MJD) ) );

Zauważ, że celowo przerywam sargility, dzieląc przez 1000 przed porównaniem do 200. Chcę, aby ta praca była wykonywana podczas wyszukiwania klucza.

Pamiętaj, że możesz uniknąć konieczności wyszukiwania (i podpowiedzi) ZAWIERAJĄ GeoPoint i Time w obu indeksach ix_MJD. To z pewnością zabierze trochę ciepła z planu zapytań.