Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Контекст исполнения Exec() и доступ из него ко внешним переменным  [new]
Sergey Kasatkin
Member

Откуда: Новосибирск
Сообщений: 44
 		Пусть есть ХП.
В ней определены какие-то переменные.
Далее в ней выполняем через EXEC() некий SQL-запрос.

Вопрос: почему при этом если в текст этого запроса включить переменные, определённые в ХП, то возникает ошибка "переменная не определена"?

В то же время, если внутри ХП вписать текст запроса с использованием этой же переменной - то всё отлично рабтает?

Видимо запрос из EXEC() выполняется "в другом контексте", где не видны локальные переменные. но где это описано в справке? не могу найти.
И есть ли способ значения "внешних" по отношению к тексту запроса внутри EXEC() использовать в этом запросе?

Пример о чем речь:

DECLARE @id int
SET @id = 5

SELECT * FROM table WHERE id = @id -- так работает

EXEC('SELECT * FROM table WHERE id = @id') -- а так не работает, "неизвестная переменная @id", почему?

Если ли способ внутри запроса EXEC() увидеть @id ?
24 янв 19, 13:53    [21793328]     Ответить | Цитировать Сообщить модератору
 Re: Контекст исполнения Exec() и доступ из него ко внешним переменным  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36096
Sergey Kasatkin
почему при этом если в текст этого запроса включить переменные, определённые в ХП, то возникает ошибка "переменная не определена"?
Потому что у переменных есть документированная область видимости.

Sergey Kasatkin
Если ли способ внутри запроса EXEC() увидеть @id ?
DECLARE @id int
SET @id = 5

exec sp_executesql N'SELECT * FROM table WHERE id = @id', '@id int', @id
24 янв 19, 13:59    [21793341]     Ответить | Цитировать Сообщить модератору
 Re: Контекст исполнения Exec() и доступ из него ко внешним переменным  [new]
zby
Member

Откуда:
Сообщений: 32
 		Sergey Kasatkin,

Для этого используют sp_ExecuteSql
24 янв 19, 14:00    [21793343]     Ответить | Цитировать Сообщить модератору
 Re: Контекст исполнения Exec() и доступ из него ко внешним переменным  [new]
iap
Member

Откуда: Москва
Сообщений: 46656
zby
Sergey Kasatkin,

Для этого используют sp_ExecuteSql
Можно сформировать строку запроса @sql с использованием переменных и выполнить запрос EXEC(@sql) без всякой процедуры.
А вот вернуть наверх значение - это да, только через OUTPUT-параметры sp_executesql
24 янв 19, 14:12    [21793365]     Ответить | Цитировать Сообщить модератору
 Re: Контекст исполнения Exec() и доступ из него ко внешним переменным  [new]
Sergey Kasatkin
Member

Откуда: Новосибирск
Сообщений: 44
 		Спасибо всем ответившим, теперь понял.

Попутно вопрос, если можно.

Верно ли я знаю/помню, что использование запроса в виде
SELECT * FROM table WHERE id = @id

более эффективно (понятно, что для разных случаев мы передаём разные значения @id),
нежели выполнение запроса 
SELECT * FROM table WHERE id = 5

когда на место 5 мы подставляем (динамическим формированием текста SQL-запроса) разные значения?

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

У меня есть какое-то воспоминание, что способ с явным указанием переменных рекомендовался как более эффективный.
Но либо память подводит, либо уже всё давно не так.
Хотелось бы уточнить.
24 янв 19, 14:50    [21793412]     Ответить | Цитировать Сообщить модератору
 Re: Контекст исполнения Exec() и доступ из него ко внешним переменным  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6356
 		Sergey Kasatkin,

Емнип не более эффективно, но создается прецедент прослушивания параметров (в зависимости от настроек параметризации для базы), в результате которого план запроса может быть использован повторно. При указании скаляров будет закеширован новый план для каждого значения.
24 янв 19, 16:40    [21793620]     Ответить | Цитировать Сообщить модератору
 Re: Контекст исполнения Exec() и доступ из него ко внешним переменным  [new]
Yasha123
Member

Откуда:
Сообщений: 1292
Владислав Колосов
Емнип не более эффективно, но создается прецедент прослушивания параметров

вообще-то у него не параметры, а переменные.
ибо даже если внутри процедуры написано
DECLARE @id int
SET @id = 5

SELECT * FROM table WHERE id = @id -- так работает

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

будет ли ТС использовать sp_executesql, еще вопрос.
но если будет, то вот тогда да, сработает прослушивание.
правда, всего лишь в первое выполнение
24 янв 19, 17:12    [21793661]     Ответить | Цитировать Сообщить модератору
 Re: Контекст исполнения Exec() и доступ из него ко внешним переменным  [new]
Dzianis
Member

Откуда:
Сообщений: 71
Yasha123
поэтому как раз в случае переменных не используется статистика.
зато если влепить константу, то как раз используется, так что план, скорее всего,
будет оптимальным.


статистика все таки используется, только не в полном объеме.
(если мы говорим про запрос в принципе, а не конкретный текст select * from вверху)
24 янв 19, 18:27    [21793735]     Ответить | Цитировать Сообщить модератору
 Re: Контекст исполнения Exec() и доступ из него ко внешним переменным  [new]
Yasha123
Member

Откуда:
Сообщений: 1292
Dzianis
Yasha123
поэтому как раз в случае переменных не используется статистика.
зато если влепить константу, то как раз используется, так что план, скорее всего,
будет оптимальным.


статистика все таки используется, только не в полном объеме.
(если мы говорим про запрос в принципе, а не конкретный текст select * from вверху)

это типа как?
1/3 статистики или 1/10?
---
в случае переменной при отсутствии option(recompile)
используется стандартная оценка в % от числа строк таблицы.
никакая колоночная статистика не используется
24 янв 19, 18:37    [21793738]     Ответить | Цитировать Сообщить модератору
 Re: Контекст исполнения Exec() и доступ из него ко внешним переменным  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6356
 		Yasha123,

я же правильно понимаю, что при указании переменной без rebuild выполняется оптимизация for unknown?
24 янв 19, 18:37    [21793739]     Ответить | Цитировать Сообщить модератору
 Re: Контекст исполнения Exec() и доступ из него ко внешним переменным  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6356
 		recompile, конечно же. Ребилдил таблицы, прицепилось
24 янв 19, 18:39    [21793740]     Ответить | Цитировать Сообщить модератору
 Re: Контекст исполнения Exec() и доступ из него ко внешним переменным  [new]
Dzianis
Member

Откуда:
Сообщений: 71
Yasha123
в случае переменной при отсутствии option(recompile)
используется стандартная оценка в % от числа строк таблицы.
никакая колоночная статистика не используется


оценка в % от числа строк таблицы - это то же статистика то )
1 фев 19, 18:13    [21800022]     Ответить | Цитировать Сообщить модератору
 Re: Контекст исполнения Exec() и доступ из него ко внешним переменным  [new]
Yasha123
Member

Откуда:
Сообщений: 1292
 		Dzianis,
да надо же.
оценка "верну одну треть таблицы вне зависимости от числа строк" статистикой и близко не пахнет.
это эмпирическая оценка
1 фев 19, 19:00    [21800070]     Ответить | Цитировать Сообщить модератору
 Re: Контекст исполнения Exec() и доступ из него ко внешним переменным  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2208
Yasha123
Dzianis,
да надо же.
оценка "верну одну треть таблицы вне зависимости от числа строк" статистикой и близко не пахнет.
это эмпирическая оценка
Конечно значение переменной не известно и поэтому гистограмма статистики не используется, но зато используется DENSITY_VECTOR, и не надо говорить что это не часть статистики. Если сделать условие на равенство для достаточно равномерно распределенных значений, то количество ожидаемых строк будет достаточно точным. В случае же если значения неравномерно распределены, то прослушивание паматров и использование реальных значений статистики будет работать хорошо только до тех пор пока случайно не скомпилируется план с "нестандартным" значением и тогда это может оказаться еще хуже, чем использование оценки по DENSITY. Варианты решения в такой ситуации либо OPTION(RECOMPILE), что будет работать в любом случае, хоть переменная, хоть параметр, либо OPTIMIZE(FOR UNKNOWN) что абсолютно тоже самое что и переменная.

Так что оценка по DENSITY конечно не идеальная, но не так уж плоха. Использование константы несомненно лучше, но 100 тысяч одинаковых запросов с разными значениями сожрут кучу памяти и процессорного времени на перекомпиляцию.
5 фев 19, 05:04    [21801656]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить