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

Откуда: Санкт-Петербург
Сообщений: 499
У меня работает служба обменивающаяся принимающая заказы с сайта, записывающая их в базу MSSQL 2012 и высылающая их изменения в базе на сайт. Для высылки получения списка изменений (чтобы потом выслать их на сайт) использовалась одна хранимая процедура, которая пробегала каждый раз по всему списку заказов (огромному за несколько лет) искала изменения сверяя с датой последней синхронизации каждого заказа. И вызывалась она после записи каждого нового заказа (вообще в томже потоке). Я решил сделать чтобы после записи нового заказа она пробегала только по нему, и только через определенный тоже не очень большой интервал пробегала по всем. Там было также сделано чтобы если процедура выполняется дольше 100сек она обрывалась. Поставил в её запрос условие "and (@UID1='' or z.[UID]=@UID1)". И после этого начались чудеса. Сначала все было хорошо, заказы полетели намного быстрее потом спустя день в тех моментах где процедура выполнялась на всех заказах ( с параметром @UID1='') начались жуткие тормоза, она тормозила более чем в 5 раз дольше чем прежде, не укладывалась в лимит и обрывалась и так каждый раз. То есть практически сдохла. Я долго мучился с ней. Один раз запустишь сделает за пол секунды, а через минуту запустишь намертво зависнит. И в следующий раз продолжает виснуть, причем если её параметр переименовать (например из @UID2 в @UID2, то довольно долгое время начинает нормально работать). Какой-то дикий глюк MSSQL с построением плана запроса. И решил эту задачу (по крайней мере уже 2 недели работает) только написав в этой хранимой процедуре 2 отдельных запроса различающихся только строкой "z.[UID]=@UID1", то есть сделав так if @UID1='' запросПолный else запрос1заказ. Эстетически безобразно конечно, но по иному никак. Можете ли вы что-нибудь пояснить по поводу такого явления.
31 мар 18, 21:44    [21302212]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение запроса одной записью вызвало очень малообъяснимые тормоза  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5112
читать про parameter sniffing
либо закладывайтесь на усреднённый план передавая значения входных параметров во внутренние переменные
либо перекомпилируйте процедуру каждый раз (with recompile)
31 мар 18, 22:02    [21302230]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение запроса одной записью вызвало очень малообъяснимые тормоза  [new]
bilov
Member

Откуда: Санкт-Петербург
Сообщений: 499
Весело с ней было. Параметр этот изначально назывался @UID. Я его переименовал, все сразу заработало. Я сразу придумал " UID - зарезервированное слово MSSQL и именно это причина всего...". Потом переименовал такие параметры ещё 4х других хранимых процедур, перелопатил массу кода где они упоминались и даже в репозитории-тортузе написал сообщение "Никогда, никогда не используйте параметры @UID"
31 мар 18, 22:19    [21302248]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение запроса одной записью вызвало очень малообъяснимые тормоза  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1172
bilov
Весело с ней было. Параметр этот изначально назывался @UID. Я его переименовал, все сразу заработало. Я сразу придумал " UID - зарезервированное слово MSSQL и именно это причина всего...". Потом переименовал такие параметры ещё 4х других хранимых процедур, перелопатил массу кода где они упоминались и даже в репозитории-тортузе написал сообщение "Никогда, никогда не используйте параметры @UID"


бред.
1 апр 18, 01:21    [21302379]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение запроса одной записью вызвало очень малообъяснимые тормоза  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33239
Блог
bilov
перелопатил массу кода где они упоминались и даже в репозитории-тортузе написал сообщение "Никогда, никогда не используйте параметры @UID"


У вас просто код рекомпилировался после alter
1 апр 18, 01:52    [21302387]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение запроса одной записью вызвало очень малообъяснимые тормоза  [new]
bilov
Member

Откуда: Санкт-Петербург
Сообщений: 499
felix_ff,

Это то я потом понял, когда спустя выходные снова перестало работать. См первое сообщение. А тогда такая логически стройная картина нарисовалась, прямо думал открытие совершил.
1 апр 18, 08:57    [21302474]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение запроса одной записью вызвало очень малообъяснимые тормоза  [new]
bilov
Member

Откуда: Санкт-Петербург
Сообщений: 499
Я ешё год или два назад заметил что случались зависания если параметры хранимой процедуры по-умолчанию = NULL и участвуют в запросе. Это миф из той же серии или действительно что-то такое есть?
1 апр 18, 14:49    [21302942]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение запроса одной записью вызвало очень малообъяснимые тормоза  [new]
bilov
Member

Откуда: Санкт-Петербург
Сообщений: 499
Дедушка,

значит чтобы процедура работала стабильно независимо от каких-нибудь директив лучше чтобы параметры не участвовали в запросах в хранимой процедуре, а передавались переменным. Спасибо, не знал о таком, учту
1 апр 18, 14:53    [21302954]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение запроса одной записью вызвало очень малообъяснимые тормоза  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1172
bilov,

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

если у вас есть процедура:
create procedure [some_proc] 
@id int
as
select columnA, columnB, columnN from [some_table] where [columnID] = @id;


это будет хорошо работать в случае если у вас процедура скомпилируется с параметром значения @id по которому происходит большинство запросов.
к примеру почти все запросы выполняющие процедуру выбирают строки с значением [columnID] = 1
в таком случае первый раз запущенная процедура
exec [some_proc] 1;


построит план оптимизированный под выборку строк для запросов по columnId = 1
для запросов которые будут выбирать [columnID] = 2 а статистика распределения строк сильно различается (к примеру 80 % на значение 1 и 20 % на значение 2) план используемый процедурой будет не оптимален для выборок по значению 2.

если вы выбираете из таблицы где [columnID] - уникальные значения, то в таком случае вы можете переписать процедуру что бы использовать статистические данные (равносильно тому же если вы будете вместо параметра использовать переменные)

create procedure [some_proc] 
@id int
as
select columnA, columnB, columnN from [some_table] where [columnID] = @id option(optimize for unknown);

тоже самое
create procedure [some_proc] 
@id int
as
declare @var_id int
set @var_id = @id;
select columnA, columnB, columnN from [some_table] where [columnID] = @var_id;



самый лучший вариант будет использование перекомпиляции отдельной инструкции в процедуре.
но это только в том случае если издержки на рекомпиляцию имеют менее затратную стоимость по сравнению с операциями выборки данных в зависимости от изменения значения параметра.
create procedure [some_proc] 
@id int
as
select columnA, columnB, columnN from [some_table] where [columnID] = @id option(recompile); 


если у вас процедура запускается очень часто, то такой подход может сказаться на производительности.
1 апр 18, 16:44    [21303094]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение запроса одной записью вызвало очень малообъяснимые тормоза  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
felix_ff,
автор
наоборот прослушивание параметров очень неплохая оптимизация, если учесть нюансы ваших выборок.

ну что за бредятина, для этого есть OPTIMIZE FOR с конкретным значением и не маятся с тем с каким же значением скомпелировалось
2 апр 18, 09:05    [21303818]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение запроса одной записью вызвало очень малообъяснимые тормоза  [new]
bilov
Member

Откуда: Санкт-Петербург
Сообщений: 499
У меня поле [UID] - первичный ключ таблицы. Название отвратительное, зарезервированное MSSQL, но пока не меняем. И это varchar(50). Понимаю что это тоже плохо и неудобно. От сайта на котором заказы делаются это повелось, пока тоже менять не будем. Параметр @UID1 это либо ключ причем только что добавленное значение либо @UID1=''. Я недавно перенес вызов с @UID1='' в отдельный процесс, потом как-нибудь в отдельном потоке одного процесса сделаю.
2 апр 18, 09:52    [21303905]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение запроса одной записью вызвало очень малообъяснимые тормоза  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1172
TaPaK,

автор хотел поменьше хардкодить.

<optimize for @var = > иногда не очень удобно в плане внесения изменений в код.

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

Не очень удобно вылавливать где программеры там написали, если не брать в расчет поиск регулярками
option (optimize for (@var = 1))
option (optimize for (@var =1))
option (optimize for (@var=1))
option (optimize for (   @var = 1))
option (optimize for (@x = 2015, @var = 1))
option (optimize for (@a=1, @b=2, @var =   1))
2 апр 18, 12:21    [21304696]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение запроса одной записью вызвало очень малообъяснимые тормоза  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
felix_ff,

ещё раз: ваш комментарий в тему "что может быть не плохо" ниочём, ибо план будет тот что скомпелирует первый, при этом всякие дба любют сбрасывать кеши и тп, и какой план будет в кеше неведомо, так что стабильно либо полностью Recompile, либо OPTIMIZE FOR если предпологаемый план более подходящий из-за массы запросов или оптимален по статистике
2 апр 18, 12:24    [21304715]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить