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

Откуда: ☭
Сообщений: 72991
Microsoft SQL Server 2000 - 8.00.2305 (Intel X86)

В хранимке такая последовательность команд:

create table #tmp
insert into #tmp select
delete from #tmp
update #tmp
delete from #tmp
update #tmp
select from #tmp
drop table #tmp

Если выполнить их просто в окне ssms, то выполнение занимает секунд 10, а если завернуть в хранимую процедуру и выполнить exec sp, то почти минуту. А VBA-клиент вообще отваливается по таймауту в три минуты.
В чем может быть проблема?
6 май 16, 14:26    [19142784]     Ответить | Цитировать Сообщить модератору
 Re: Хранимка выполняется медленнее набора инструкций  [new]
iap
Member

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

недоговариваете. Там, небось, параметры используются?
6 май 16, 14:32    [19142809]     Ответить | Цитировать Сообщить модератору
 Re: Хранимка выполняется медленнее набора инструкций  [new]
Antonariy
Member

Откуда: ☭
Сообщений: 72991
iap
Antonariy,

недоговариваете. Там, небось, параметры используются?
да
6 май 16, 14:33    [19142817]     Ответить | Цитировать Сообщить модератору
 Re: Хранимка выполняется медленнее набора инструкций  [new]
Antonariy
Member

Откуда: ☭
Сообщений: 72991
4 шт, все int
6 май 16, 14:35    [19142826]     Ответить | Цитировать Сообщить модератору
 Re: Хранимка выполняется медленнее набора инструкций  [new]
Glory
Member

Откуда:
Сообщений: 104751
Antonariy
iap
Antonariy,

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

А в ssms наверное уже не параметры, а констатны, да ?
6 май 16, 14:35    [19142827]     Ответить | Цитировать Сообщить модератору
 Re: Хранимка выполняется медленнее набора инструкций  [new]
Antonariy
Member

Откуда: ☭
Сообщений: 72991
Glory
Antonariy
пропущено...
да

А в ssms наверное уже не параметры, а констатны, да ?
да, через declare.
6 май 16, 14:37    [19142843]     Ответить | Цитировать Сообщить модератору
 Re: Хранимка выполняется медленнее набора инструкций  [new]
Glory
Member

Откуда:
Сообщений: 104751
Antonariy
Glory
пропущено...

А в ssms наверное уже не параметры, а констатны, да ?
да, через declare.

Ну так и план выполнения другой получается
6 май 16, 14:39    [19142851]     Ответить | Цитировать Сообщить модератору
 Re: Хранимка выполняется медленнее набора инструкций  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Antonariy
iap
Antonariy,

недоговариваете. Там, небось, параметры используются?
да
Вот в них всё дело.
Вне процедуры все значения всех параметров известны заранее.
И под эти значения выбирается план выполнения.
А в процедуре все параметры в момент компиляции имеют как бы "всевозможные значения".
План строится "универсальный", неоптимальный ни для одного конкретного набора значений параметров.
Называется всё это "parameter sniffing". Масса статей можно найти по этим ключевым словам.
Попробуйте пересоздать процедуру с перекомпиляцией при каждом запуске -
тогда каждый раз будет строиться хороший план. Для нечастых запусков - нормально.
6 май 16, 14:39    [19142852]     Ответить | Цитировать Сообщить модератору
 Re: Хранимка выполняется медленнее набора инструкций  [new]
Antonariy
Member

Откуда: ☭
Сообщений: 72991
iap
Попробуйте пересоздать процедуру с перекомпиляцией при каждом запуске -
тогда каждый раз будет строиться хороший план.
Пробовал exec with recompile - та же картина.

А вот когда объявил локальные переменные и присвоил им значения параметров (и соответственно заменил далее по тексту), то хранимка заработала как надо.

Вообще в первый раз с такой ерундой сталкиваюсь, спасибо за объяснение.
6 май 16, 14:50    [19142930]     Ответить | Цитировать Сообщить модератору
 Re: Хранимка выполняется медленнее набора инструкций  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Antonariy
Пробовал exec with recompile - та же картина.

CREATE PROCEDURE

В 2000-м, помнится, точно такой же синтаксис, связанный с WITH RECOMPILE.
То есть, можно попробовать пересоздать процедуру с этой опцией.
6 май 16, 14:54    [19142960]     Ответить | Цитировать Сообщить модератору
 Re: Хранимка выполняется медленнее набора инструкций  [new]
Antonariy
Member

Откуда: ☭
Сообщений: 72991
А хотя заработала только в SSMS, но в ней уже самой процедуре константы передаются.
В общем, понятно куда копать.
6 май 16, 14:57    [19142973]     Ответить | Цитировать Сообщить модератору
 Re: Хранимка выполняется медленнее набора инструкций  [new]
Antonariy
Member

Откуда: ☭
Сообщений: 72991
iap
Antonariy
Пробовал exec with recompile - та же картина.

CREATE PROCEDURE

В 2000-м, помнится, точно такой же синтаксис, связанный с WITH RECOMPILE.
То есть, можно попробовать пересоздать процедуру с этой опцией.
Не помогло.
6 май 16, 15:09    [19143023]     Ответить | Цитировать Сообщить модератору
 Re: Хранимка выполняется медленнее набора инструкций  [new]
Antonariy
Member

Откуда: ☭
Сообщений: 72991
С клиента такой запрос приходит:

declare @P1 varchar(50)
set @P1='41'
declare @P2 int
set @P2=90
declare @P3 varchar(50)
set @P3='1'
declare @P4 int
set @P4=30
exec sp_executesql N'DECLARE @store As VarChar(50);
select @store= @P1
DECLARE @notsold As Integer;
select @notsold = @P2
DECLARE @col As VarChar(50);
select @col = @P3
DECLARE @notcount As Integer;
select @notcount = @P4


exec usp_Report_test @store, @notsold, @col, @notcount;
', N'@P1 varchar(50) OUTPUT,@P2 int OUTPUT,@P3 varchar(50) OUTPUT,@P4 int OUTPUT', @P1 output, @P2 output, @P3 output, @P4 output
select @P1, @P2, @P3, @P4
6 май 16, 15:19    [19143067]     Ответить | Цитировать Сообщить модератору
 Re: Хранимка выполняется медленнее набора инструкций  [new]
Antonariy
Member

Откуда: ☭
Сообщений: 72991
И в ssms он обрабатывается быстро. А у клиента таймаут.
6 май 16, 15:25    [19143094]     Ответить | Цитировать Сообщить модератору
 Re: Хранимка выполняется медленнее набора инструкций  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31960
Antonariy
И в ssms он обрабатывается быстро. А у клиента таймаут.
Нужно проверить, какие установки коннекта в SSMS и в программе.
Раз вы вызываете не конечные запросы в SSMS, а именно exec usp_Report_test..., то дело не в изменении планов из за констант...
6 май 16, 15:30    [19143126]     Ответить | Цитировать Сообщить модератору
 Re: Хранимка выполняется медленнее набора инструкций  [new]
Antonariy
Member

Откуда: ☭
Сообщений: 72991
DBCC FREEPROCCACHE помогло.
6 май 16, 15:34    [19143146]     Ответить | Цитировать Сообщить модератору
 Re: Хранимка выполняется медленнее набора инструкций  [new]
Antonariy
Member

Откуда: ☭
Сообщений: 72991
alexeyvg
Antonariy
И в ssms он обрабатывается быстро. А у клиента таймаут.
Нужно проверить, какие установки коннекта в SSMS и в программе.
Раз вы вызываете не конечные запросы в SSMS, а именно exec usp_Report_test..., то дело не в изменении планов из за констант...
Это я еще вчера пробовал.
6 май 16, 15:35    [19143149]     Ответить | Цитировать Сообщить модератору
 Re: Хранимка выполняется медленнее набора инструкций  [new]
Владислав Колосов
Member

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

уберите drop table #tmp из скрипта.
6 май 16, 16:15    [19143328]     Ответить | Цитировать Сообщить модератору
 Re: Хранимка выполняется медленнее набора инструкций  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34705
Antonariy
.
В чем может быть проблема?


в процедуре может быть проблема.
7 май 16, 21:19    [19146583]     Ответить | Цитировать Сообщить модератору
 Re: Хранимка выполняется медленнее набора инструкций  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34705
Antonariy
iap
Попробуйте пересоздать процедуру с перекомпиляцией при каждом запуске -
тогда каждый раз будет строиться хороший план.
Пробовал exec with recompile - та же картина.

А вот когда объявил локальные переменные и присвоил им значения параметров (и соответственно заменил далее по тексту), то хранимка заработала как надо.

Вообще в первый раз с такой ерундой сталкиваюсь, спасибо за объяснение.


что интересно, нужно было делать ровно наоборот. Или ставить хинты.
7 май 16, 21:21    [19146593]     Ответить | Цитировать Сообщить модератору
 Re: Хранимка выполняется медленнее набора инструкций  [new]
Сон Веры Павловны
Member

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

и по уже сложившейся традиции при подобных вопросах: Медленно в приложении, быстро в SSMS.
8 май 16, 04:02    [19147315]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить