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

Откуда: Днепропетровск
Сообщений: 243
SQL Server 2012, Amazon RDS
Есть хп:
ALTER procedure [dbo].[UpdateDeliveryPlan]
			@whereCondition nvarchar(max),
			@itemID int,
			@amount int,
			@useAmountColumn bit
as
begin

	declare @query nvarchar(max)

	declare @curwhereCondition nvarchar(max)
	declare @curitemID int
	declare @curamount int
	declare @curuseAmountColumn bit

	set @curWhereCondition=@whereCondition
	set @curItemID=@itemID
	set @curAmount=@amount
	set @curUseAmountColumn=@useAmountColumn


	set @query = N'update [dbo].[DeliveryPlan]
	set [Amount] = dp.Amount + case when @useAmountColumn=1 and dbo.ConvertToInt(bs.Количество)>0 then dbo.ConvertToInt(bs.Количество) else @amount end
	from BaseSpecification bs join 
		BaseSpecificationStatusType t on (bs.StatusTypeID=t.StatusTypeID) join
		[DeliveryPlan] dp on (dp.BaseSpecificationID=bs.BaseSpecificationID and dp.ItemID=@itemID)
	where ' + @curWhereCondition + N' and t.IsActive=1


	INSERT INTO [dbo].[DeliveryPlan] ([BaseSpecificationID], [ItemID], [Amount])
	select bs.BaseSpecificationID, @itemID, case when @useAmountColumn=1 and dbo.ConvertToInt(bs.Количество)>0 then dbo.ConvertToInt(bs.Количество) else @amount end
	from BaseSpecification bs join 
		BaseSpecificationStatusType t on (bs.StatusTypeID=t.StatusTypeID) left join
		[DeliveryPlan] dp on (dp.BaseSpecificationID=bs.BaseSpecificationID and dp.ItemID=@itemID)
	where ' + @curWhereCondition + N' and t.IsActive=1 and dp.BaseSpecificationID is null'
	EXECUTE sp_executesql @query, N'@itemID int, @amount int, @useAmountColumn bit', @itemID=@curItemID, @amount=@curAmount, @useAmountColumn=@curUseAmountColumn
end


Ее задача обновить столбец Количество, если необходимые строки есть. Если нет - добавить их. При определенных значениях параметра @whereCondition (Например: 'bs.BaseID=3' ) запрос выполняется очень быстро (13 сек на 1 000 000 записей при обновлении или добавлении) и в плане ничего подозрительного не заметил. А при некоторых - виснит наглухо, еще на первом запросе, когда обновлению подлежит 0 записей, используя при этом tembdb (засек через хп для мониторинга).
Сейчас проблема ушла - я добавил некластерный индекс. Но боюсь, что это скорее случайность, т.к. что-то похожее появляется неожиданно на разных запросах и так же неожиданно уходит. Что это могло быть?
п.с.: внутреннее переопределение параметров добавил, чтобы исключить проблему со сниффингом параметров.
на сервере фиксируются висячие подключения (до 50) хотя клиенты все закрыты, т.е. даже если не сработала очистка приложения, должна была сработать очистка системы (CLR).
24 июл 15, 09:47    [17928553]     Ответить | Цитировать Сообщить модератору
 Re: Зависает хп при отсутствии записей и определенных параметрах  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
а зачем вот ето счатье то ?
dbo.ConvertToInt(bs.Количество)
24 июл 15, 09:58    [17928589]     Ответить | Цитировать Сообщить модератору
 Re: Зависает хп при отсутствии записей и определенных параметрах  [new]
MikeKat
Member

Откуда: Днепропетровск
Сообщений: 243
там может быть текст
24 июл 15, 10:00    [17928599]     Ответить | Цитировать Сообщить модератору
 Re: Зависает хп при отсутствии записей и определенных параметрах  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
висячие подключения (до 50) хотя клиенты все закрыты

Чудес не бывает, откат транзакций на безумные where.

Не пишите так, как в процедуры, создайте отдельные подпроцедуры на каждый вариант выборки и вызывайте подпроцедуру кодом-селектором в рамках вашей UpdateDeliveryPlan. Для кода-селектора создайте справочник и выводите его на интерфейс.
список параметров в UpdateDeliveryPlan для подпроцедрур можно передавать в XML формате для универсальности интерфейса.
24 июл 15, 12:52    [17929629]     Ответить | Цитировать Сообщить модератору
 Re: Зависает хп при отсутствии записей и определенных параметрах  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
как в процедуры
как в процедуре, указанной выше
24 июл 15, 12:53    [17929633]     Ответить | Цитировать Сообщить модератору
 Re: Зависает хп при отсутствии записей и определенных параметрах  [new]
MikeKat
Member

Откуда: Днепропетровск
Сообщений: 243
Владислав Колосов,

>> Чудес не бывает, откат транзакций на безумные where.
можете пояснить?

и чем плох такой, смешанный вариант?
24 июл 15, 13:20    [17929754]     Ответить | Цитировать Сообщить модератору
 Re: Зависает хп при отсутствии записей и определенных параметрах  [new]
invm
Member

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

Ваш код подвержен SQL Injection.
24 июл 15, 13:39    [17929886]     Ответить | Цитировать Сообщить модератору
 Re: Зависает хп при отсутствии записей и определенных параметрах  [new]
MikeKat
Member

Откуда: Днепропетровск
Сообщений: 243
invm,
в плане безопасности - согласен. так было проще реализовать
а в плане производительности? update по пустым строкам почему плохо?
24 июл 15, 13:51    [17929982]     Ответить | Цитировать Сообщить модератору
 Re: Зависает хп при отсутствии записей и определенных параметрах  [new]
invm
Member

Откуда: Москва
Сообщений: 9402
MikeKat
а в плане производительности? update по пустым строкам почему плохо?
Разные предикаты в where - могут быть разные планы запросов. Соответственно поведение тоже разное.
Для начала, при "подвисании", смотрите чего ждет запрос в sys.dm_os_waiting_tasks.
24 июл 15, 14:13    [17930131]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить