Poprawa wydajności STIntersects

Tabela T_PINma 300 000 pinów i T_POLYGON36 000 wielokątów. T_PINma ten indeks:

CREATE SPATIAL INDEX [T_PIN_COORD] ON [dbo].[T_PIN]
(
[Coord]
)USING  GEOGRAPHY_GRID 
WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH), 
CELLS_PER_OBJECT = 128, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY];

T_POLYGON ma:

CREATE SPATIAL INDEX [T_POLYGON_COORD] ON [dbo].[T_POLYGON]
(
[COORD]
)USING  GEOGRAPHY_GRID 
WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH), 
CELLS_PER_OBJECT = 128, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
ON [PRIMARY];

Wykonanie zapytania w celu znalezienia przecięcia T_PINi jego wykonanie T_POLYGONzajmuje ponad 45 minut:

SELECT COUNT(*)
FROM T_PIN 
INNER JOIN T_POLYGON
    ON T_PIN.Coord.STIntersects(T_POLYGON.COORD) = 1;

Wynik to 4,438,318 wierszy.

Jak mogę przyspieszyć to zapytanie?

Po pierwsze, sprawdź, czy używany jest indeks przestrzenny, patrząc na plan wykonania zapytania i sprawdź, czy istnieje element Poszukiwany indeks klastrowany (przestrzenny).

Zakładając, że jest używany, możesz spróbować dodać dodatkowy / uproszczony filtr oparty na obwiedni z uproszczonymi wielokątami, aby sprawdzić najpierw. Mecze z tymi uproszczonymi wielokątami mogą być następnie przeprowadzane przez filtr główny, aby uzyskać ostateczne wyniki.

1) Dodaj nową kolumnę geografii i geometrii do tabeli [dbo]. [T_POLYGON]:

ALTER TABLE [dbo].[T_POLYGON] ADD SimplePolysGeom geometry;
ALTER TABLE [dbo].[T_POLYGON] ADD SimplePolysGeog geography;

2) Utwórz wielokąty obwiedni (obejmuje to wstępną konwersję do geometrii, aby skorzystać z STEnvelope ()):

UPDATE [dbo].[T_POLYGON] SET SimplePolysGeom = geometry::STGeomFromWKB(
    COORD.STAsBinary(), COORD.STSrid).STEnvelope();

UPDATE [dbo].[T_POLYGON] SET SimplePolysGeog = geography::STGeomFromWKB(
    SimplePolysGeom.STAsBinary(), SimplePolysGeom.STSrid);

3) Utwórz indeks przestrzenny w uproszczonej kolumnie geograficznej

4) Uzyskaj skrzyżowania z tą uproszczoną kolumną geograficzną, a następnie ponownie przefiltruj według pasujących typów danych geograficznych. Z grubsza coś takiego:

;WITH cte AS
(
   SELECT pinID, polygonID FROM T_PIN INNER JOIN T_POLYGON
    ON T_PIN.Coord.STIntersects(T_POLYGON.SimplePolysGeog ) = 1
)
SELECT COUNT(*)
FROM T_PIN 
INNER JOIN T_POLYGON
    ON T_PIN.Coord.STIntersects(T_POLYGON.COORD) = 1
    AND T_PIN.pinID IN (SELECT pinID FROM cte)
    AND T_POLYGON.polygonID IN (SELECT polygonID FROM cte)

EDYCJA : możesz zastąpić (1) i (2) tą wyliczoną, utrwaloną kolumną. podziękowania dla Paula White'a za sugestię.

ALTER TABLE [dbo].[T_POLYGON] ADD SimplePolysGeog AS  ([geography]::STGeomFromWKB([geometry]::STGeomFromWKB([COORD].[STAsBinary](),[COORD].[STSrid]).STEnvelope().STAsBinary(),(4326))) PERSISTED

Takie zapytania często zajmują dużo czasu ze względu na złożoność wielokątów. Widziałem złożone linie brzegowe (na przykład), które przez wieki testowały punkty znajdujące się w pobliżu ich granic, musiały powiększać wiele poziomów, aby sprawdzić, czy punkt znajduje się wewnątrz, czy na zewnątrz.

... abyś mógł wypróbować .Reduce()wielokąty i sprawdzić, czy to pomoże.

Więcej informacji na temat tej funkcji można znaleźć na stronie http://msdn.microsoft.com/en-us/library/cc627410.aspx

Według dokumentów Microsoft indeksy przestrzenne będą używane z typami geograficznymi w następujących metodach, gdy pojawią się one na początku predykatu porównania z WHEREklauzulą:

• STIntersects
• STDistance
• STEquals

Tylko metody typów geometrii (lista zastrzeżona) wyzwalają użycie indeksu przestrzennego w JOIN ... ON, więc zmień kod na używany WHERE geog1.STIntersects(geog2) = 1i to powinno poprawić prędkość.

Polecam również skorzystanie z porady w odpowiedzi g2server i dodanie poniższych elementów do filtrowania i dodania do niej indeksu przestrzennego

ALTER TABLE [dbo].[T_POLYGON] ADD SimplePolysGeog AS
     ([geography]::STGeomFromWKB([geometry]::STGeomFromWKB([COORD].[STAsBinary](),
                                                           [COORD].[STSrid])
                 .STEnvelope().STAsBinary(),(4326))) PERSISTED

możesz mieć takie zapytanie jak poniżej (napisałem ten post szybko i jeszcze go nie testowałem, to jest po prostu coś do wypróbowania, ponieważ widziałem, że twoje zapytanie i najwyższe opublikowane odpowiedzi używają JOIN ON spatial op = 1, który nie użyje indeks przestrzenny):

SELECT   
     (SELECT p2.polygon_id
      FROM   T_Polygon p2
      WHERE  p2.coords.STIntersects(t.coords) = 1),
     t.pin_id
FROM     T_PIN t
WHERE    
     (SELECT t.coords.STIntersects(p.coords)
      FROM   T_POLYGON p
      WHERE  t.coords.STIntersects(p.SimplePolysGeog) = 1) = 1

FYI: Powyższe nie działa, jeśli SimplePolysGeogskończy się nakładanie (jak w szpilce może być w dwóch uproszczonych geogach, po prostu uruchomiłem to na ludziach w obwodzie w stanie, a ponieważ normalne polisy mają granicę, pola ograniczające się na siebie nakładają), więc w większości przypadków przypadki wyrzuci błąd, że podzapytanie zwróciło więcej niż jeden wynik.

Z przeglądu indeksów przestrzennych MS Docs :

Metody geograficzne obsługiwane przez indeksy przestrzenne

Pod pewnymi warunkami indeksy przestrzenne obsługują następujące metody geograficzne zorientowane na zestaw: STIntersects (), STEquals () i STDistance (). Aby zapewnić obsługę indeksu przestrzennego, metody te muszą być używane w klauzuli WHERE zapytania i muszą występować w predykacie o następującej formie ogólnej:

geography1.method_name (geography2) Porównanie_operatorvalid_number

Aby zwrócić wynik inny niż zero, geografia1 i geografia2 muszą mieć ten sam identyfikator odniesienia przestrzennego (SRID) . W przeciwnym razie metoda zwraca NULL.

Indeksy przestrzenne obsługują następujące formy predykatów:

• geografia 1. STIntersects (geography2) = 1

• geografia 1. STALI (geografia 2) = 1

• geografia 1. STDistance (geografia2) <liczba

• geografia 1. STDistance (geografia2) <= liczba

Zapytania korzystające z indeksów przestrzennych

Indeksy przestrzenne są obsługiwane tylko w zapytaniach, które zawierają indeksowanego operatora przestrzennego w klauzuli WHERE. Na przykład składnia, taka jak:

[spatial object].SpatialMethod([reference spatial object]) [ = | < ] [const literal or variable]

Optymalizator zapytań rozumie komutatywność operacji przestrzennych (tego @a.STIntersects(@b) = @b.STInterestcs(@a)). Indeks przestrzenny nie będzie jednak stosowany, jeśli początek porównania nie zawiera operatora przestrzennego (na przykład WHERE 1 = spatial opnie użyje indeksu przestrzennego). Aby użyć indeksu przestrzennego, przepisz porównanie (na przykład WHERE spatial op = 1).

...

Następujące zapytanie będzie działać, jeśli się SimplePolysGeogspokrywają:

;WITH cte AS
(
   SELECT T_PIN.PIN_ID, 
          T_POLYGON.POLYGON_ID, 
          T_POLYGON.COORD 
   FROM T_PIN 
   INNER JOIN T_POLYGON
   ON T_PIN.COORD.STIntersects(T_POLYGON.SimplePolysGeog) = 1
)

SELECT COUNT(*)
FROM T_PIN 
INNER JOIN cte
ON T_PIN_PIN_ID = cte.PIN_ID
where cte.[COORD].STIntersects(T_PIN.COORD) = 1