Почему мой пространственный поиск медленнее в SQL Server, чем в PostGIS?

Question

Я работаю над переносом некоторых возможностей пространственного поиска из Postgres с PostGIS на SQL Server и вижу довольно ужасную производительность, даже с индексами.

Мои данные составляют около миллиона точек, и я хочу выяснить, какие из этих точек находятся в заданных фигурах, поэтому запрос выглядит примерно так:

DECLARE @Shape GEOMETRY = ...
SELECT * FROM PointsTable WHERE Point.STWithin(@Shape) = 1

Если я выберу довольно маленькую фигуру, я иногда получаю менее секунды, но если моя фигура довольно большая (а они иногда), я могу получить раз за 5 минут. Если я выполняю одни и те же поиски в Postgres, они всегда меньше секунды (фактически, почти все они меньше 200 мс).

Я пробовал несколько разных размеров сетки на моих индексах (все высокие, все средние, все низкие), разные ячейки на объект (16, 64, 256), и независимо от того, что я делаю, время остается довольно постоянным. Я хотел бы попробовать больше комбинаций, но я даже не знаю, в каком направлении идти. Больше клеток на объект? Меньше? Какая-то странная комбинация размеров сетки?

Я посмотрел на свои планы запросов, и они всегда используют индекс, просто он совсем не помогает. Я даже пытался без индекса, и это не намного хуже.

Кто-нибудь может дать совет по этому поводу? Все, что я могу найти, говорит о том, что "мы не можем дать вам никаких советов по индексам, просто попробуйте все, и, возможно, один из них сработает", но при этом на создание индекса уходит 10 минут, а слепая работа - огромная трата времени.

РЕДАКТИРОВАТЬ: Я также разместил это на форуме Microsoft. Вот некоторая информация, которую они просили там:

Лучший рабочий индекс, который я мог получить, был этот:

CREATE SPATIAL INDEX MapTesting_Location_Medium_Medium_Medium_Medium_16_NDX
    ON MapTesting (Location)
 USING GEOMETRY_GRID
  WITH (
    BOUNDING_BOX = ( -- The extent of our data, data is clustered in cities, but this is about as small as the index can be without missing thousands of points
        XMIN = -12135832,
        YMIN = 4433884,
        XMAX = -11296439,
        YMAX = 5443645),
    GRIDS = (
        LEVEL_1 = MEDIUM,
        LEVEL_2 = MEDIUM,
        LEVEL_3 = MEDIUM,
        LEVEL_4 = MEDIUM),
     CELLS_PER_OBJECT = 256 -- This was set to 16 but it was much slower
  )

У меня были некоторые проблемы с использованием индекса, но это не так.

Для этих тестов я запустил тестовый поиск (тот, который указан в моем исходном сообщении) с предложением WITH(INDEX(...)) для каждого из моих индексов (тестирование различных настроек для размера сетки и ячеек на объект), и один без любой намек. Я также запустил sp_help_spatial_geometry_index, используя каждый индекс и одну и ту же форму поиска. Указанный выше индекс работал быстрее всего, а также был указан как наиболее эффективный в sp_help_spatial_geometry_index.

При запуске поиска я получаю эту статистику:

(1 row(s) affected)
Table 'MapTesting'. Scan count 0, logical reads 361142, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'extended_index_592590491_384009'. Scan count 1827, logical reads 8041, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 6735 ms,  elapsed time = 13499 ms.

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

Наши данные являются адресами по всему штату, поэтому есть несколько областей с очень высокой плотностью, но в основном это редкие данные. Я думаю, проблема в том, что никакие настройки размеров сетки не работают хорошо для обоих. С сетками, установленными на HIGH индекс возвращает слишком много ячеек в областях с низкой плотностью и с сетками, установленными на LOW, сетки бесполезны в областях высокой плотности (в MEDIUM, это не так плохо, но все равно не хорошо).

Я могу использовать индекс, просто он не помогает. Каждый тест выполнялся с включенным "показывать фактический план выполнения", и он всегда показывает индекс.

Answer 1

Я только что провел день по аналогичной проблеме. В частности, мы выполняем запрос типа точка-полигон, в котором был относительно небольшой набор полигонов, но каждый полигон был большим и сложным.

Решение оказалось следующим для пространственного индекса таблицы полигонов:

1. Используйте "геометрическую автоматическую сетку" вместо старого MMLL и т. Д. Это дает 8 уровней индексации вместо старого 4, и настройки являются автоматическими. А ТАКЖЕ...
2. Установите "ячейки на объект" на 2000 или 4000. (Не легко догадаться, учитывая, что по умолчанию 16!)

Это имело огромное значение. Он был в 10 раз быстрее, чем пространственный индекс в конфигурации по умолчанию, и в 60 раз быстрее, чем вообще без индекса.

Answer 2

Вот несколько замечаний о пространственных расширениях SQL-Server и о том, как обеспечить эффективное использование индекса:

• http://sqlskills.com/BLOGS/BOBB/post/How-to-ensure-your-spatial-index-is-being-used.aspx

По-видимому, планировщик испытывает трудности с построением хорошего плана, если он не знает фактическую геометрию во время разбора. Автор предлагает вставить exec sp_executesql:

Заменить:

-- does not use the spatial index without a hint
declare @latlonPoint geometry = geometry::Parse('POINT (45.518066 -122.767464)')
select a.id, a.shape.STAsText() 
from zipcodes a 
where a.shape.STIntersects(@latlonPoint)=1
go

с:

-- this does use the spatial index without using a hint
declare @latlonPoint geometry = geometry::Parse('POINT (45.518066 -122.767464)')
exec sp_executesql 
N'select a.id, a.shape.STAsText() 
from zipcodes a 
where a.shape.STIntersects(@latlonPoint)=1', N'@latlonPoint geometry', @latlonPoint
go

Answer 3

Я считаю, что STIntersects лучше оптимизирован для использования индекса, будет иметь лучшую производительность, чем STWithin, особенно для больших фигур.

Answer 4

Моя внутренняя реакция - "потому что Microsoft не удосужилась сделать это быстро, потому что это не корпоративная функция". Возможно я веду себя цинично.

Я не уверен, почему вы тоже уезжаете из Постгреса.

Answer 5

Вопросы эффективности реализации помимо SQL-сервера используют индекс Quadtree, тогда как PostGIS использует индекс R-дерева.

R-дерево в большинстве случаев является лучшим алгоритмом, особенно для больших наборов данных с изменяющимся размером геометрии.

Answer 6

Вы правильно настроили пространственный индекс? Ваш ограничивающий прямоугольник правильный? Все ли точки внутри? В вашем случае, вероятно, HHMM для GRIDS будет работать лучше всего (опять же, в зависимости от того, что будет лучше).

Можете ли вы попробовать использовать sp_help_spatial_geometry_index, чтобы увидеть, что не так? http://msdn.microsoft.com/en-us/library/cc627426.aspx

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

Что-то не так с вашей настройкой. Пространство действительно новая функция, но это не так уж плохо.

Answer 7

Вы можете попробовать разбить его на два этапа:

1. выбрать кандидатов во временную таблицу ж / .Filter(),
2. запрос кандидатов со .STWithin(),

например:

SELECT * INTO #this FROM PointsTable WHERE Point.Filter(@Shape) = 1
SELECT * FROM #this WHERE Point.STWithin(@Shape) = 1

(замена SELECT * только с фактическими столбцами, вам нужно уменьшить ввод / вывод)

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

Answer 8

Я не знаком с пространственными запросами, но это может быть проблема параметризованного запроса

попробуйте написать запрос (без использования параметров) с фиксированным значением (используйте значение, которое работает медленно с параметризованным запросом) и запустите его. Сравните время с параметризованной версией. Если это намного быстрее, то ваша проблема - параметризованные запросы.

Если вышеупомянутое намного быстрее, то я бы динамически собрал вашу строку sql со значениями параметров, встроенными в строку, чтобы вы могли удалить параметры, вызывающие проблемы.