Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Оптимизация Ad Hoc запроса exec ('select ...')  [new]
oleg2163
Member

Откуда:
Сообщений: 18
Имеется запрос который реализует сложный поиск, например -
"я ищу женщину которой от 20 до 40 лет, живет в Маскве, чернокожая и не менее двух детей". Возможно добавление еще десятка-полтора параметров.
Реализовывать его обычным образом типа WHERE (@CityID IS NULL or CityID = @CityID)... плюс еще 15 аналогичных неправильно - индексы использовать невозможно, выполняется долго.
Поэтому сделано в виде динамического запроса exec ('select ... WHERE CityID = 1237 OPTION (RECOMPILE)')

Почитав переписку в другой ветке, задался вопросом а не стоит ли этот код заменить на

exec ('DECLARE @CityID INT;
SET CityID = 1237;
select ... WHERE CityID = @CityID')

без опции RECOMPILE чтобы планы выполнения сохранялись.

Есть знакомые с этой проблемой?
Mnior, наверно вы что то сможете сказать по этому поводу?
24 авг 12, 11:46    [13059069]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Ad Hoc запроса exec ('select ...')  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31863
oleg2163
Почитав переписку в другой ветке, задался вопросом а не стоит ли этот код заменить на

exec ('DECLARE @CityID INT;
SET CityID = 1237;
select ... WHERE CityID = @CityID')

без опции RECOMPILE чтобы планы выполнения сохранялись.
Лучше использовать sp_executesql, передавая туда параметры.

Сохранение планов выполнения может иметь негативный эффект - неправильный план для каких то особенных значений переменных.

Но скорее всего, для вашей ситуации это будет действительно оптимально
24 авг 12, 11:56    [13059134]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Ad Hoc запроса exec ('select ...')  [new]
iap
Member

Откуда: Москва
Сообщений: 47083
А разве динамический SQL не приводит к рекомпиляции всегда?
24 авг 12, 12:05    [13059188]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Ad Hoc запроса exec ('select ...')  [new]
Читатель неместный
Guest
Судя по статье
http://www.t-sql.ru/post/optimize_for.aspx
Перекомпиляция желательна для данного вида запросов, то есть когда время выполнения больше явно времени компиляции...
..имхо конешна
То есть параметризовывать в sp_executesql нежелательно, что бы в дальнейшем план сильно не попортился.
24 авг 12, 12:14    [13059238]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Ad Hoc запроса exec ('select ...')  [new]
oleg2163
Member

Откуда:
Сообщений: 18
iap, похоже хранятся - с этого и возникла проблема - там хранится 3000 разных вариантов этого вызова, каждый вызывался по 1-10 раз
24 авг 12, 12:15    [13059246]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Ad Hoc запроса exec ('select ...')  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31863
Читатель неместный
То есть параметризовывать в sp_executesql нежелательно, что бы в дальнейшем план сильно не попортился.
Это необнозначно, тут нужно ТС самому выбирать.

Скорее всего запросы несложные, так что sp_executesql с параметрами будет в самый раз.
24 авг 12, 12:20    [13059276]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Ad Hoc запроса exec ('select ...')  [new]
Почитав переписку
Guest
oleg2163,

план выполнения сможет подхватиться только если тело запроса абсолютно точно такое же.
т.е. только при sp_executesql с параметрами.

зачем нужна OPTION (RECOMPILE) в первом варианте, у которого и так будет recompile, не понятно.
причем, если запрос внезапно попадет в существующий план, то рекомпайл таки будет не нужен.

для большого количества adhoc'ов в 2008 есть спец-опция
(там же про планы немного расписано)

по задаче:
сложный поиск бывает более сложным и менее сложным. насколько он сложен _на_самом_деле_, отсюда не видно.
присмотритесь, скорее всего поиск вполне себе довольно строго структурирован.

например, вполне можно выделить какое-то основное условие и выбирать статическим запросом пусть даже более широкий диапазон данных во временную табличку, а потом уже короткими быстрыми "ad hoc'ами" обрезать полученный предварительный результат.
24 авг 12, 22:41    [13062667]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Ad Hoc запроса exec ('select ...')  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
oleg2163
Имеется запрос который реализует сложный поиск, например -
"я ищу женщину которой от 20 до 40 лет, живет в Маскве, чернокожая и не менее двух детей". Возможно добавление еще десятка-полтора параметров.
Реализовывать его обычным образом типа WHERE (@CityID IS NULL or CityID = @CityID)... плюс еще 15 аналогичных неправильно - индексы использовать невозможно, выполняется долго.
Поэтому сделано в виде динамического запроса exec ('select ... WHERE CityID = 1237 OPTION (RECOMPILE)')
Чето вы все в кучу намешали. Зачем вам сразу и константы и динамика и OPTION (RECOMPILE) ?
Сделайте все в статике, через параметры + OPTION (RECOMPILE)
Если вы знаете что очень много запросов выполняется с определенными фильтрами, то можно вынести их отдельно, опять же в статику с параметрми, но без RECOMPILE.
25 авг 12, 02:03    [13063126]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Ad Hoc запроса exec ('select ...')  [new]
oleg2163
Member

Откуда:
Сообщений: 18
sp_executesql с параметрами хорошо если список параметров постоянный.
Что-то не вижу как его применить в моем случае.
Например
поиск 1: есть возраст, пол
поиск 2: есть возраст, цвет кожи
поиск 3: есть возраст, пол, цвет кожи
поиск 4: есть пол, цвет кожи
поиск 5: есть возраст, пол, город
поиск 6: есть возраст, город
28 авг 12, 10:02    [13073863]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Ad Hoc запроса exec ('select ...')  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31863
oleg2163
sp_executesql с параметрами хорошо если список параметров постоянный.
Что-то не вижу как его применить в моем случае.
Очень просто - передаёте всегда все параметры, то есть вызов всегда один и тот же.

А текст внутри уже формируете какой надо, используя или не используя переданные параметры.
28 авг 12, 10:29    [13074030]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Ad Hoc запроса exec ('select ...')  [new]
oleg2163
Member

Откуда:
Сообщений: 18
alexeyvg, так какой же в этом смысл????? то есть план будет каждый раз один и тот же, хотя переменные не используются
28 авг 12, 11:45    [13074580]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Ad Hoc запроса exec ('select ...')  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31863
oleg2163
alexeyvg, так какой же в этом смысл????? то есть план будет каждый раз один и тот же, хотя переменные не используются
С чего вы взяли??? Запросы разные, планы разные.

Как вообще могут быть связаны переменные и планы???
28 авг 12, 11:54    [13074655]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Ad Hoc запроса exec ('select ...')  [new]
о чем топик
Guest
alexeyvg
oleg2163
sp_executesql с параметрами хорошо если список параметров постоянный.
Что-то не вижу как его применить в моем случае.
Очень просто - передаёте всегда все параметры, то есть вызов всегда один и тот же.

А текст внутри уже формируете какой надо, используя или не используя переданные параметры.

...оригинальный способ получить выгоду от фиксированного текста запроса
просто каждый раз менять текст запроса, а список параметров - всеегдааа ооодиииииин!..
автор
поиск 1: есть возраст, пол
поиск 2: есть возраст, цвет кожи
поиск 3: есть возраст, пол, цвет кожи
поиск 4: есть пол, цвет кожи
поиск 5: есть возраст, пол, город
поиск 6: есть возраст, город

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

если у тебя _текст_запросов_всегда_разный_ и ты ничего с этим не собираешься делать, то в чем, собственно, вопрос?
о чем топик?
28 авг 12, 12:36    [13075127]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Ad Hoc запроса exec ('select ...')  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31863
о чем топик
если у тебя _текст_запросов_всегда_разный_ и ты ничего с этим не собираешься делать, то в чем, собственно, вопрос?
о чем топик?
А вы прочтите топик.

Вопрос ТС как раз в том, как сделать _текст_запросов_всегда_разный_, при этом параметризированный.
о чем топик
...оригинальный способ получить выгоду от фиксированного текста запроса
просто каждый раз менять текст запроса, а список параметров - всеегдааа ооодиииииин!..
Оригинального ничего нет, стандартный удобный шаблон для использования динамики с параметрами. Ну и не только удобный, а единственный осуществимый технически, других не бывает.
28 авг 12, 12:46    [13075217]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Ad Hoc запроса exec ('select ...')  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Меня тут призывают в ветку.
1. Проблем тут больше, чем кажется.
2. Нужно сразу разделить и выявить первую нерешёную проблему - это когда запрос сложный, точнее когда обращаешься к сложному VIEW. Тогда компиляция будет жутко долгой.
3. Нужно понять как должно происходить взаимодействия клиента и сервера, в каком формате
4. Какие средства и мы имеем и какие мы должны иметь

Видно что проблемы и решения немного не коррелируют, но с другой стороны инструменты пересекаются.

Никто не вспомнил ORM. То, что должно генерировать запросы к скулю. Никто не упомянул что запросы с константами параметризуются, но только если они просты. Поэтому будет уже набор параметризованных запросов.
Но если у вас будет на эти 15 параметров 100500 вариантов, то ничего хорошего не получится. И решений для этого нет, всё взвалили на нас, мы должны подумать за сервер, мы должны на основании имеющихся индексов выделять костяк и выёживаться с запросом.

Хотя мы видим что сервер способен выделить костях запроса (на основании имеющихся индексов) и откинуть остальные параметры из первичных.

Должен ли ORM сам параметризовывать? Должен.
Должен ли протокол взаимодействия быть более заточенный/специализированный? Да, сервер не резиновый.
Делает ли кто-то что либо? Нет, не делается ничего.
Реагируем ли мы на это правильно? Нет, мы говорим пользователям - это твоя проблема, вот тебе набор лопат.

Возвращаясь к сложному VIEW - я не представляю пока как это можно разруливать. Так что пока только молча пользуемся головой и руками.
Нужно ли сообщать серверу что запросы такого рода часты? Нужно. Как это делается? Несколько вариантов:
От собственноручно написанных процедур, до самостоятельного набора статистики сервером.
Хватает ли этого? Не хватает. ORM и оные должны помогать серверу, а не скрывать заложенный в клиент код.
Нужно ли сообщать серверу что компиляция плана не менее важна чем выдача результата? Нужно.
В запросах общего поиска компилятор должен выложится по максимому, даже если это долго.
Как сообщить серверу это?

Почему мне не нравится опция "Ad Hoc"? Потому что она на весь сервер, ему монописуально, что можно на основании типов запросов уже разделить на те которые выгодно часто компилить, и те которые лучше хранить подольше.

Так что тут очень всё зыбко и сыро. И костылями болезнь не вылечить.
29 авг 12, 11:24    [13080754]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Ad Hoc запроса exec ('select ...')  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Жуть какая
+
DECLARE	@Test TABLE (
	 ID	Int    IDENTITY	 PRIMARY KEY
	,Price	Money	NULL	,UNIQUE (Price,ID)
)INSERT	@Test (Price) VALUES (NULL),(1),(2)
DECLARE	 @Price1	Money
	,@Price2	Money
	,@Price3	Money
	,@Price4	Money
	,@Price5	Money

SELECT * FROM @Test WHERE Price = @Price1 OR Price IS NULL AND @Price1 IS NULL
SELECT * FROM @Test WHERE Exists(SELECT Price INTERSECT SELECT @Price1)

SELECT * FROM @Test WHERE Price IN (@Price1,@Price2,@Price3,@Price4)
	   OR @Price1 IS NULL OR @Price2 IS NULL OR @Price3 IS NULL OR @Price4 IS NULL OR @Price5 IS NULL

SELECT * FROM @Test WHERE
	   Exists(SELECT Price INTERSECT SELECT @Price1)
	OR Exists(SELECT Price INTERSECT SELECT @Price2)
	OR Exists(SELECT Price INTERSECT SELECT @Price3)
	OR Exists(SELECT Price INTERSECT SELECT @Price4)
	OR Exists(SELECT Price INTERSECT SELECT @Price5)

SELECT * FROM @Test WHERE
	   Exists(SELECT Price INTERSECT SELECT @Price1
	UNION ALL SELECT Price INTERSECT SELECT @Price2
	UNION ALL SELECT Price INTERSECT SELECT @Price3
	UNION ALL SELECT Price INTERSECT SELECT @Price4
	UNION ALL SELECT Price INTERSECT SELECT @Price5)
И всё мимо.
29 авг 12, 11:53    [13081067]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Ad Hoc запроса exec ('select ...')  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Не туда запостил.
29 авг 12, 15:37    [13083212]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Ad Hoc запроса exec ('select ...')  [new]
oleg2163
Member

Откуда:
Сообщений: 18
Mnior, ????
31 авг 12, 12:56    [13094242]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Ad Hoc запроса exec ('select ...')  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
oleg2163
Mnior, ????

13081067 - выкинуть
13080754 - а это по теме. Или что-то не понятно?
Просто описываю что "Ad Hoc" это слишком слабо.
31 авг 12, 21:49    [13097746]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить