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

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

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

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

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

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

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

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

EDIT: Я также разместил это на форуме 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, это не так плохо, но все же не очень хорошо).

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

Ответ 1

Вот некоторые замечания о пространственных расширениях 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

Ответ 2

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

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

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

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

Ответ 3

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

Ответ 4

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

Я не уверен, почему вы тоже уходите от Postgres.

Ответ 5

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

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

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

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

Ответ 6

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

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

например:

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

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

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

Ответ 7

Проблемы с эффективностью внедрения в SQL Server используют Quadtree index, тогда как PostGIS использует R-tree.

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

Ответ 8

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

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

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