возникла следующая проблема с написание правильного запроса к MS SQL.
Очень упрощенно ситуация выглядит так:
есть таблица с множеством полей, среди прочих есть spatial поле (Point).
Далее, нужно сделать выборку по ряду условий, среди которых есть также условия попадания Point в заданную область.
Упрощенно запрос выглядит так:
SELECT
*
FROM
T
WHERE
T.A = SomeValue AND
T.B IN (SomeSubquery) AND
... AND
T.Z = AnotherValue AND
@Area.STIntersects(T.Point)
Spatial индекса по полю Point по ряду причин, одна из которых — очень частое обновление этого поля, индекс очень долго обновляется.
Я хочу, чтобы в начале проверялись все условия, кроме последнего — в результате должен быть получен очень небольшой набор записей, который уже затем будет отфильтрован по последнему условию.
На практике же происходит обратное — согласно плану MS SQL сперва сканирует всю таблицу и фильтрует ее по @Area.STIntersects, а потом выбрасывает 99% результатов по остальным условиям. Получается очень долго.
В связи с этим вопрос — как мне ему сказать, чтобы @Area.STIntersects проверялось в последнюю очередь?
Здравствуйте, jedi, Вы писали:
J>Упрощенно запрос выглядит так:
J>
J>SELECT
J> *
J>FROM
J> T
J>WHERE
J> T.A = SomeValue AND
J> T.B IN (SomeSubquery) AND
J> ... AND
J> T.Z = AnotherValue AND
J> @Area.STIntersects(T.Point)
J>
J>На практике же происходит обратное — согласно плану MS SQL сперва сканирует всю таблицу и фильтрует ее по @Area.STIntersects, а потом выбрасывает 99% результатов по остальным условиям. Получается очень долго.
Если из запроса убрать условие AND @Area.STIntersects(T.Point), то план меняется, и запрос начинает выполняться быстрее?
1) подзапросы и/или ЦТЕ — но не факт что поможет, оптимизатор МС СКЛ и не таких хитрых видал
2) загонять "быстые" результаты во временную таблицу, и потом уже на неё применять "медленный" фильтр
3) хинтами заставить использовать другой индекс, если есть
Здравствуйте, Sergei MO, Вы писали:
SM>Если из запроса убрать условие AND @Area.STIntersects(T.Point), то план меняется, и запрос начинает выполняться быстрее?
Да, намного быстрее. Как я уже написал ниже — если сохранить результаты запроса без последнего условия во временную таблицу, а затем выбирать из нее уже — то получается серьезное ускорение. Но это из разряда хаков. Хотелось бы найти цивилизованный способ указать серверу, что я от него хочу.
Здравствуйте, rm822, Вы писали:
R>во первых проверь IO
R>set statistics io on
И? Я по-моему писал, что внимательно изучил план запроса.
R>запрос с хинтами R>запрос без хинтов
Вот я и пытаюсь понять какие же хинты ему нужны, чтобы он оставил проверку дорогого условия напоследок.
R>если без хинтов IO меньше — сиквел прав, просто у тебя база слишком маленькая
Вам не кажется что это бред? Меня не интересует сферическая правота сиквела в вакууме, меня интересует производительность, которую видят наши клиенты. Так вот с его "правильным решением" запрос выполняется 5-7 секунд, а с моим уродским рукопашным с привлечением временной таблицы < 500 ms. Думаю дальнейшие разговоры о правоте можно оставить.
SELECT
*
FROM
(
SELECT * FROM
T
WHERE
T.A = SomeValue AND
T.B IN (SomeSubquery) AND
... AND
T.Z = AnotherValue) subq
WHERE
@Area.STIntersects(subq.Point)
Ну и как вариант грязного хака — в тот запрос, что я писал добавить в подзапрос выборку столбца ROW_NUMBER ( ) ... as RN и во внешнем запросе добавить условие subq.RN>0.
Возможно планировщик передумает.
Здравствуйте, Vyacheslav Benedichuk, Вы писали:
VB>Ну и как вариант грязного хака — в тот запрос, что я писал добавить в подзапрос выборку столбца ROW_NUMBER ( ) ... as RN и во внешнем запросе добавить условие subq.RN>0. VB>Возможно планировщик передумает.
Спасибо за попытку помочь
Грязные хаки я изобретать умею — на данный момент таким грязным хаком в виде временной таблицы и обошелся.
Мне все же хотелось найти "правильное" решение проблемы, но его видимо просто не существует
Здравствуйте, jedi, Вы писали:
J>Спасибо за попытку помочь J>Грязные хаки я изобретать умею — на данный момент таким грязным хаком в виде временной таблицы и обошелся.
J>Мне все же хотелось найти "правильное" решение проблемы, но его видимо просто не существует
Здравствуйте, jedi, Вы писали:
J>Здравствуйте, Sergei MO, Вы писали:
J>Как я уже написал ниже — если сохранить результаты запроса без последнего условия во временную таблицу, а затем выбирать из нее уже — то получается серьезное ускорение. Но это из разряда хаков. Хотелось бы найти цивилизованный способ указать серверу, что я от него хочу.
Если я правильно понял, @Area — это довольно сложный объект, и вызов STIntersects сам по себе занимает много времени. В таком случае промежуточная таблица — это вполне нормальный способ, и боюсь, что единственный из цивилизованных.
Если таблица не нравится, могу предложить ещё такой хак. Нужно создать вспомогательную таблицу:
CREATE TABLE One
(
Value INT NOT NULL
)
INSERT INTO One VALUES (1)
А запрос изменить так:
SELECT *
FROM
(
SELECT
*
FROM
T
WHERE
T.A = SomeValue AND
T.B IN (SomeSubquery) AND
... AND
T.Z = AnotherValue
) Q
INNER JOIN One
ON @Area.STIntersects(Q.Point) = One.Value
OPTION (FORCE ORDER)
Здравствуйте, jedi, Вы писали:
J>Здравствуйте, avpavlov, Вы писали:
A>>2) загонять "быстые" результаты во временную таблицу, и потом уже на неё применять "медленный" фильтр
J>Я так и сделал на данный момент, и получил увеличение скорости раз в десять. Но мне это решение не нравится. Думаю, не надо объяснять почему
попробуй CTE, где-то там внутри будет почти тоже самое что и временные таблицы.
— на данный момент таким грязным хаком в виде временной таблицы и обошелся.
