Улучшение производительности STIntersects

Таблица T_PIN имеет 300 000 контактов и T_POLYGON имеет 36 000 полигонов. T_PIN имеет этот индекс:

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 имеет:

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];

Запрос для поиска пересечения T_PIN и T_POLYGON исполняется более 45 минут:

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

В результате получается 4 438 318 строк.

Как ускорить этот запрос?

11 голосов | спросил seb49 6 FriEurope/Moscow2013-12-06T12:48:34+04:00Europe/Moscow12bEurope/MoscowFri, 06 Dec 2013 12:48:34 +0400 2013, 12:48:34

3 ответа


6

Во-первых, проверьте, используется ли пространственный индекс, просматривая план выполнения запроса и проверяйте, есть ли объект поиска кластеризованного индекса (Пространственный).

Предполагая, что он используется, вы можете попробовать добавить вторичный /упрощенный фильтр на основе ограничивающего прямоугольника с упрощенными полигонами для проверки в первую очередь. Соответствие этим упрощенным многоугольникам можно запустить через первичный фильтр, чтобы получить окончательные результаты.

1) Добавьте новый столбец географии и геометрии в таблицу [dbo]. [T_POLYGON]:

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

2) Создайте многоугольники ограничивающей рамки (это предполагает первоначальное преобразование в геометрию, чтобы использовать 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) Создайте пространственный индекс в столбце упрощенной географии

4) Получите пересечения с этим упрощенным столбцом географии, а затем снова отфильтруйте соответствующие типы данных географии. Примерно так:

;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)

EDIT : вы можете заменить (1) и (2) на этот вычисленный, упорный столбец. кредит Паулю Уайту за предложение.

ALTER TABLE [dbo].[T_POLYGON] ADD SimplePolysGeog AS  ([geography]::STGeomFromWKB([geometry]::STGeomFromWKB([COORD].[STAsBinary](),[COORD].[STSrid]).STEnvelope().STAsBinary(),(4326))) PERSISTED
ответил g2server 22 MonEurope/Moscow2014-12-22T12:53:08+03:00Europe/Moscow12bEurope/MoscowMon, 22 Dec 2014 12:53:08 +0300 2014, 12:53:08
2

Подобные запросы часто занимают много времени из-за сложности полигонов. Я видел, что сложные береговые линии (например) занимают возрасты до контрольных точек, которые находятся вблизи их границ, и они должны масштабировать многие уровни, чтобы определить, находится ли точка внутри или снаружи.

... так что вы можете попробовать .Reduce() 'полигоны, чтобы убедиться, что это помогает.

И для получения дополнительной информации о этой функции просмотрите http://msdn.microsoft .com /en-us /library /cc627410.aspx

ответил Rob Farley 22 MonEurope/Moscow2014-12-22T11:20:54+03:00Europe/Moscow12bEurope/MoscowMon, 22 Dec 2014 11:20:54 +0300 2014, 11:20:54
0

В соответствии с документами Microsoft пространственные индексы будут использоваться с типами географии по следующим методам, когда они появятся в начале предиката сравнения с помощью WHERE:

    STIntersects литий> STDistance литий> STEquals литий>

Только методы типа геометрии (ограниченный список) вызовут использование пространственного индекса в JOIN ... ON, поэтому измените свой код на использование WHERE geog1.STIntersects(geog2) = 1, и это должно улучшить скорость.

Я также рекомендую обратиться за консультацией в ответ g2server и добавить следующее для фильтрации и добавить к нему пространственный индекс

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

у вас может получиться запрос как следующий (я быстро написал это сообщение и еще не тестировался, это просто попытка попробовать, потому что я видел, что ваш запрос и самые высокие ответы на них используют JOIN ON пространственный op = 1, который не будет использовать пространственный индекс):

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: вышеупомянутое не работает, если SimplePolysGeog в конечном итоге перекрывается (как в булавке может быть два упрощенных геолога, это на людей в участках в состоянии, а так как нормальная граница разделяет полисы, ограничивающие поля перекрываются), поэтому в большинстве случаев использования он выдает ошибку, что подзапрос возвращает более одного результата.

Из MS Docs ' Обзор пространственных индексов :

  

Методы географии, поддерживаемые пространственными индексами

     

При определенных условиях пространственные индексы поддерживают следующие методы ориентированной геометрии: STIntersects (), STEquals () и STDistance (). Для поддержки пространственного индекса эти методы должны использоваться в предложении WHERE запроса, и они должны встречаться в предикате следующего общего вида:

     

geography1.method_name (geography2) comparison_operatorvalid_number

     

Чтобы вернуть ненулевой результат, geography1 и geography2 должны иметь тот же Spatial Reference Identifier (SRID) . В противном случае метод возвращает NULL.

     

Пространственные индексы поддерживают следующие предикатные формы:

     
  • география1. STIntersects (geography2) = 1

  •   
  • география 1. STEquals (geography2) = 1

  •   
  • география1. STDistance (geography2) <номер

  •   
  • география1. STDistance (география2) <= число

  •   


     

Запросы, которые используют пространственные индексы

     

Пространственные индексы поддерживаются только в запросах, которые включают индексированный пространственный оператор в предложении WHERE. Например, синтаксис, например:

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

Оптимизатор запросов понимает коммутативность пространственных операций (что @a.STIntersects(@b) = @b.STInterestcs(@a)). Однако пространственный индекс не будет использоваться, если начало сравнения не содержит пространственный оператор (например, WHERE 1 = spatial op) не будет использоватьпространственный индекс). Чтобы использовать пространственный индекс, перепишите сравнение (например, WHERE spatial op = 1).

     

...

Следующий запрос будет работать, если SimplePolysGeogs перекрывается:

;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
ответил Rob Farley 22 MonEurope/Moscow2014-12-22T11:20:54+03:00Europe/Moscow12bEurope/MoscowMon, 22 Dec 2014 11:20:54 +0300 2014, 11:20:54

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132