Zoptymalizuj zapytanie najbliższego sąsiada w 70 milionach chmur punktów w SQL Server 2008

Mam około 75 milionów rekordów w bazie danych SQL Server 2008 R2 Express. Każda ma długość odpowiadającą pewnej wartości. Tabela ma kolumnę geograficzną. Próbuję znaleźć najbliższego sąsiada dla danej długości i szerokości geograficznej (punktu). Mam już zapytanie z indeksem przestrzennym. Ale w zależności od tego, gdzie rekord znajduje się w bazie danych, powiedzmy w pierwszym lub ostatnim kwartale, zapytanie może zająć od 3 do 30 sekund, aby znaleźć najbliższego sąsiada. Wydaje mi się, że można to zoptymalizować, aby uzyskać znacznie szybszy wynik przez optymalizację zapytania lub indeksu przestrzennego. W tej chwili zastosowano indeks przestrzenny z ustawieniami domyślnymi. Oto jak wygląda moja tabela i zapytanie.

CREATE TABLE lidar(
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [POINTID] [int] NOT NULL,
    [GRID_CODE] [numeric](17, 8) NULL,
    [geom] [geography] NULL,
 CONSTRAINT [PK_lidar_1] PRIMARY KEY CLUSTERED ([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]

Indeks przestrzenny, którego używam:

CREATE SPATIAL INDEX [SPATIAL_lidar] ON [dbo].[lidar] ([geom]) USING  GEOGRAPHY_GRID 
WITH (
GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM), 
CELLS_PER_OBJECT = 16, PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,  
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Oto zapytanie, którego używam:

declare @ms_at geography = 'POINT (-95.66 30.04)';
select TOP(1) nearPoints.geom.STAsText()as latlon 
from
(
select r.geom
from lidar r With(Index(SPATIAL_lidar))
where r.geom.STIntersects(@ms_at.STBuffer(1000)) = 1
) nearPoints

Oto próbka lat longs w mojej bazie danych. dać wyobrażenie o dokładności i gęstości. Wszystkie 70 milionów rekordów dotyczy jednego miasta (dane Lidar).

POINT (-95.669434934023087 30.049513838913736)

Teraz to zapytanie daje mi wyniki, jak opisano powyżej, ale chcę jak najbardziej poprawić wydajność. Domyślam się, modyfikując domyślne wartości wskaźnika przestrzennego, który mogę być powyżej, aby lepiej zoptymalizować wydajność. Jakieś wskazówki na ten temat?

Próbowałem zmienić bufor z 10 na 1000, ale z prawie takimi samymi wynikami.

Mile widziane są również wszelkie inne sugestie dotyczące poprawy wydajności.

Oto system, którego teraz używam:

Windows 7 64bit Professional
Intel(R) Core(TM)2 Quad CPU    Q9650  @ 3.00GHz (4 CPUs), ~3.0GHz
Ram: 8 GB
NVIDIA GeForce 9500 GT

Spróbuj uruchomić procedurę przechowywaną sp_help_spatial_geography_index , aby uzyskać szczegółowe informacje na temat używania indeksu przestrzennego. Powinieneś być w stanie użyć czegoś takiego:

declare @ms_at geography = 'POINT (-95.66 30.04)'
set @ms_at = @ms_at.STBuffer(1000).STAsText()
exec sp_help_spatial_geography_index 'lidar', 'SPATIAL_lidar', 0, @ms_at;

Opublikuj wyniki w swoim pytaniu, aby zobaczyć, czy coś się wyróżnia. Znaczenie każdego z elementów można znaleźć tutaj .

Jeśli rzutowane są twoje współrzędne, możesz również wykonać proste zapytanie nieprzestrzenne na polach obliczeniowych X, Y i sprawdzając X <MinX i X> MaxX itp.

Rzutowanie współrzędnych (w polu typu GEOMETRIA) pozwala również ograniczyć indeks przestrzenny do zakresu danych, co może znacznie przyspieszyć działanie. Zamień zakresy świata na zakresy danych:

CREATE SPATIAL INDEX [SPATIAL_lidar] ON [dbo].[lidar] ([geom]) USING  GEOMETRY_GRID 
WITH (
GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM), 
CELLS_PER_OBJECT = 16, PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,  
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON,
BOUNDING_BOX =(-90, -180, 90, 180),) ON [PRIMARY]

Rozważ uproszczenie bufora za pomocą BufferwithTolerance . Jeśli punkty są ciasno upakowane, system musi ustalić, czy punkt jest po obu stronach granicy. Im prostsza jest ta linia, tym mniej pracy musi wykonać maszyna.

Sprawdź ten zasób autorstwa Izaaka Kunena na temat korzystania z tabeli liczb w celu optymalizacji najbliższego sąsiada za pomocą indeksu przestrzennego

http://blogs.msdn.com/b/isaac/archive/2008/10/23/nearest-neighbors.aspx