Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Время выполнения SELECT в хранимой процедуре через EXEC и как элементарный запрос  [new]
Maksym_t
Member

Откуда: Екатеринбург
Сообщений: 29
Добрый день!

За пару лет работы с MS SQL, не так давно столкнулся с единственной проблемой, для которой и по сей день не могу найти объяснения.

Суть проблемы в следующем:

Есть SELECT, который в окне Management Studio выполняется за ~2 секунды. Ничего особенного, обычный отчет.
Однако, ежели, встроить этот SELECT в хранимую процедуру и запустить уже как EXEC dbo.[Имя процедуры] Параметр1, Параметр2 то результата выполнения можно дождаться в лучше случае через 5, а то и 7 минут.

Сам текст запроса, мне кажется мало чем может влиять так сильно, по этому не буду выкладывать.
Разница ~2 секунды и ~5 минут поражает.

P.S. гуглиг, пользовался поиском и на этом форуме, объяснения на данный момент не нашлось. Кидайтесь ссылками с удовольствием почитаю инфу. Проблема не стоит остро, просто не дает покоя и нужно как-то с этим бороться, а возможно и со своим отсутствием необходимых знаний.

За ранее всем спасибо, кто откликнется, и удачи в работе!

+ Хранимая процедура (Ничего особенного, имена процедуры, параметров вымышлены)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[Имя процедуры]
	Параметр1 varchar(10), Параметр2 int
AS
BEGIN

Select...<Текст запроса, достаточно громоздкий>

END
+ Версия сервера:
Microsoft SQL Server 2005 - 9.00.5057.00 (Intel X86) Mar 25 2011 13:50:04 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
5 сен 14, 12:58    [16539724]     Ответить | Цитировать Сообщить модератору
 Re: Время выполнения SELECT в хранимой процедуре через EXEC и как элементарный запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 46999
Maksym_t,

parameter sniffing
5 сен 14, 13:04    [16539784]     Ответить | Цитировать Сообщить модератору
 Re: Время выполнения SELECT в хранимой процедуре через EXEC и как элементарный запрос  [new]
pegoopik
Member

Откуда: Новосибирск
Сообщений: 54
Maksym_t,

Parameter Sniffing Problem and Possible Workarounds
5 сен 14, 13:42    [16540106]     Ответить | Цитировать Сообщить модератору
 Re: Время выполнения SELECT в хранимой процедуре через EXEC и как элементарный запрос  [new]
o-o
Guest
ну раз такая пьянка, то уж вот всем ссыль на самый фундаментальный труд по этому поводу (ИМХО):
Slow in the Application, Fast in SSMS?
там же есть ссылка и на достойнейший перевод на русский
5 сен 14, 13:56    [16540246]     Ответить | Цитировать Сообщить модератору
 Re: Время выполнения SELECT в хранимой процедуре через EXEC и как элементарный запрос  [new]
Maksym_t
Member

Откуда: Екатеринбург
Сообщений: 29
Спасибо всем откликнувшимся!

Проблема решена!

Суть решения:,
Действительно оказалось, что план выполнения SELECT и EXEC dbo.[Имя процедуры] Параметр1, Параметр2, совершенно разные, спасибо 16540106 за это полезное сообщение. Победил проблему динамическим запросом.
+ Обманка оптимизатора
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[Имя процедуры]
	Параметр1 varchar(10), Параметр2 int
AS
BEGIN

DECLARE @sql_script varchar(MAX)

SET @sql_script = 'declare Параметр1 varchar(10), Параметр2 int' +
'select Параметр1 = ''' + Параметр1 + ''', Параметр2 = ' + Параметр2 +
'SELECT ... <Текст запроса, достаточно громоздкий>'

EXEC(@sql_script)

END

Теперь выполнение процедуры занимает ~2 секунды.
8 сен 14, 14:47    [16550038]     Ответить | Цитировать Сообщить модератору
 Re: Время выполнения SELECT в хранимой процедуре через EXEC и как элементарный запрос  [new]
Maksym_t
Member

Откуда: Екатеринбург
Сообщений: 29
Перепутал, вот где была разгадка 16540246
8 сен 14, 14:49    [16550064]     Ответить | Цитировать Сообщить модератору
 Re: Время выполнения SELECT в хранимой процедуре через EXEC и как элементарный запрос  [new]
Ice_and_Fire
Guest
Maksym_t,
автор
Теперь выполнение процедуры занимает ~2 секунды.
зато читаемость упала ниже плинтуса
Уж лучше OPTION или локальные переменные, их всего две
8 сен 14, 15:43    [16550440]     Ответить | Цитировать Сообщить модератору
 Re: Время выполнения SELECT в хранимой процедуре через EXEC и как элементарный запрос  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Maksym_t
Спасибо всем откликнувшимся!

Проблема решена!

Суть решения:,
Действительно оказалось, что план выполнения SELECT и EXEC dbo.[Имя процедуры] Параметр1, Параметр2, совершенно разные, спасибо 16540106 за это полезное сообщение. Победил проблему динамическим запросом.
Вы выбрали самое ужасное решение из всех возможных. Если для этого конкретно запроса нужно чтобы сервер не делал оптимизацию под значения параметров, то просто используйте локальные переменные в запросе вместо параметров. Для версии 2008+ есть еще лучше решение - OPTIMIZE FOR UNKNOWN.
Но использовать тут динамику, это извращение. Мало того что читаемость никакая, так еще и кэш планов будет загажен сотнями одинаковых запросов с разными значениями параметров.
9 сен 14, 01:56    [16552448]     Ответить | Цитировать Сообщить модератору
 Re: Время выполнения SELECT в хранимой процедуре через EXEC и как элементарный запрос  [new]
Maksym_t
Member

Откуда: Екатеринбург
Сообщений: 29
Ice_and_Fire, Mind, Спасибо за критику.

Согласен с вами, что решение не самое оптимальное.
Ice_and_Fire
Уж лучше OPTION или локальные переменные, их всего две
Mind
Если для этого конкретно запроса нужно чтобы сервер не делал оптимизацию под значения параметров, то просто используйте локальные переменные в запросе вместо параметров.
Именно так и сделал, в результате получил:
+ Прототип SP с локальными переменными
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[Имя процедуры]
    Параметр1 varchar(10), Параметр2 int
AS
BEGIN

    --Локальные переменные
DECLARE @Параметр1_локальный varchar(10), Параметр2_локальный int
SELECT @Параметр1_локальный = Параметр1, Параметр2_локальный = Параметр2

    --Запрос
SELECT ... <Текст запроса, достаточно громоздкий, использующий только локальные переменные>

END
Время выполнения ~2 секунды. Планы запроса одинаковые как для динамического запроса так и с локальными переменными.
9 сен 14, 09:36    [16552791]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить