Я работаю над переносом некоторых возможностей пространственного поиска из 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
, сетки бесполезны в областях с высокой плотностью (в СРЕДНИЙ
, это не так плохо, но все равно не очень хорошо).
Я могу использовать индекс, это просто не помогает. Каждый тест был запущен с включенным «показывать фактический план выполнения», и он всегда показывает индекс.
Моя интуиция такова: «Потому что Microsoft не потрудилась сделать это быстро, потому что это не корпоративная функция». Может, я циничен.
Я тоже не понимаю, почему вы переходите с Postgres.
Вот несколько замечаний о пространственных расширениях SQL-Server и о том, как обеспечить эффективное использование индекса:
Очевидно, планировщику трудно построить хороший план, если он не знает фактическую геометрию во время синтаксического анализа. Автор предлагает вставить 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
Я не знаком с пространственными запросами, но это может быть проблема параметризованного запроса
попробуйте написать запрос (без использования параметров) с фиксированным значением (используйте значение, которое медленно работает с параметризованным запросом) и запустите его. Сравните время выполнения с параметризованной версией. Если она намного быстрее, значит, ваша проблема заключается в параметризованных запросах.
Если вышеописанная версия намного быстрее, то я бы динамически построил вашу sql-строку со значениями параметров, встроенными в строку, таким образом вы сможете устранить параметры, вызывающие проблемы.
Я полагаю, что STIntersects лучше оптимизирован для использования индекса, будет иметь лучшую производительность, чем STWithin, особенно для больших фигур.
Правильно ли вы настроили пространственный индекс? Ваша ограничительная рамка верна? Все точки внутри? В вашем случае, вероятно, HHMM для GRIDS будет работать лучше всего (опять же в зависимости от будящей коробки).
Можете ли вы попробовать использовать sp_help_spatial_geometry_index, чтобы увидеть, что не так? http://msdn.microsoft.com/en-us/library/cc627426.aspx
Попробуйте вместо этого использовать операцию фильтра и сообщите нам, какие показатели производительности вы получили? (он выполняет только первичный фильтр (использует индекс) без прохождения вторичного фильтра (настоящая пространственная операция))
Что-то не так с вашей настройкой. Spatial действительно новая функция, но она не так уж и плоха.
Вы можете попробовать разбить его на два прохода:
.Filter()
. .STWithin()
. например:
SELECT * INTO #this FROM PointsTable WHERE Point.Filter(@Shape) = 1
SELECT * FROM #this WHERE Point.STWithin(@Shape) = 1
(заменив SELECT *
только фактическими столбцами, которые вам нужны, чтобы уменьшить ввод-вывод)
Такой вид микрооптимизации не должен быть необходим, но я видел приличные улучшения производительности раньше. Кроме того, вы сможете оценить, насколько селективен ваш индекс, по соотношению (1) к (2).