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

Откуда:
Сообщений: 13
Есть хранимая процедура состоящая из двух запросов. Если я создаю процедуру так :
ALTER PROCEDURE [dbo].[report_List_For_Category_6]
	-- Add the parameters for the stored procedure here
@id INT	
AS
BEGIN


т.е. с входным параметром id, а потом вызываю её

exec [report_List_For_Category_6] @id = 101

То процедура зависает намертво (не дождался окончания выполнения)
Если же создаю без входного параметра и задаю id в теле процедуры
ALTER PROCEDURE [dbo].[report_List_For_Category_6]
AS
BEGIN
declare
	@id INT	
	SET NOCOUNT ON;
    set @id = 101

то время выполнения секунд 5.
Подскажите в чем проблема (MS SQL Server 2008 R2).
Все что нашел в интернете касалось переменных/параметров против констант (переменные и входные параметры рассматривались как один класс).
CREATE PROCEDURE [dbo].[report_List_For_Category_6]
AS
declare
@id INT
BEGIN
	SET NOCOUNT ON;
        set @id = 101

	DECLARE @Date SMALLDATETIME

	SELECT @Date = [Date]
	FROM SSCLists
	WHERE Id = (
			SELECT TOP 1 CLId
			FROM SSLists
			WHERE Id = @id
			);

	WITH latestMarriageDocs
	AS (
		SELECT Id
			,ClientId
			,StatementId
			,RelationId
			,TypeDocMarriageId
			,NUM_DOC
			,DATE_BRAK
			,ORG_NAME
			,ROW_NUMBER() OVER (
				PARTITION BY StatementId ORDER BY DATE_BRAK DESC
				) AS RowNum
		FROM MDocs
		WHERE StatementId IS NOT NULL
		)
		,addresses
	AS (
		SELECT ID
			,StatementId
			,PLACE_KLADR_ID
			,ROW_NUMBER() OVER (
				PARTITION BY StatementId ORDER BY id DESC
				) AS AddrNum
		FROM [Address] a
		WHERE AddressTypeId = 2
			AND StatementId IS NOT NULL
			AND a.StatementId IN (
				SELECT ssli.ClaimId
				FROM SSLItems ssli
				WHERE ssli.ListId = @id
				)
		)
		,prices
	AS (
		SELECT mp.[Id]
			,[DecreeId]
			,[KladrCode]
			,[Date]
			,[Price]
			,ROW_NUMBER() OVER (
				PARTITION BY DecreeId
				,KladrCode
				,a.StatementId ORDER BY [Date] DESC
				) AS PriceNum
			,a.StatementId
		FROM [MPrices] mp
		INNER JOIN addresses a ON a.PLACE_KLADR_ID = mp.KladrCode
			AND a.AddrNum = 1
		WHERE mp.DecreeId = 3
			AND mp.[Date] < @Date
		)
	SELECT *
		,OwedSquare * tab1.Price AS Cost
		,Convert(DECIMAL(10, 2), (OwedSquare * tab1.Price) / (tab1.Relcount - 1)) AS partCost
	FROM (
		SELECT dense_rank() OVER (
				ORDER BY RegDate
					,Id
				) AS Num
			,*
			,(
				CASE 
					WHEN relCount = 2
						THEN 42
					WHEN relCount = 3
						THEN 54
					WHEN relCount > 3
						THEN relCount * 18
					END
				) OwedSquare
		FROM (
			SELECT TOP 10000 s.Id
				,s.AdministrationsId
				,DATE_ORG AS RegDate
				,o.NAME AS omsuName
				,c.id AS clientid
				,dbo.count_Family_relations(s.Id) + 1 AS Relcount --1+ROW_NUMBER() OVER (PARTITION BY S.Id ORDER BY c.id DESC) AS Relcount
				,ISNULL(c.FAMILY, '') + ' ' + ISNULL(c.NAME, '') + ' ' + ISNULL(c.LASTNAME, '') AS ClaimerFIO
				,c.DATE_BORN AS ClaimerBirthday
				,d.SER_DOC + ' ' + d.NUM_DOC AS ClaimerDoc
				,d.SER_DOC AS Series
				,d.NUM_DOC AS Number
				,d.ORG_NAME AS Organ
				,d.DOC_DATE AS DOCDATE
				,r.TypeRelationshipId
				,ISNULL(c2.FAMILY, '') + ' ' + ISNULL(c2.NAME, '') + ' ' + ISNULL(c2.LASTNAME, '') AS rFIO
				,d2.SER_DOC + ' ' + d2.NUM_DOC AS rDoc
				,d2.SER_DOC AS rSeries
				,d2.NUM_DOC AS rNumber
				,d2.ORG_NAME AS rOrgan
				,d2.DOC_DATE AS rDocDATE
				,c2.DATE_BORN rBirthday
				,m.NUM_DOC
				,m.ORG_NAME
				,m.DATE_BRAK
				,prices.Price
			FROM SSLItems i
			LEFT JOIN [Statement] s ON i.ClaimId = s.Id
			LEFT JOIN Client c ON s.clientid = c.Id
			LEFT JOIN Document d ON c.id = d.clientid
			LEFT JOIN Program p ON s.id = p.statementid
			LEFT JOIN Dic_OMSU o ON s.OMSUId = o.Id
			LEFT JOIN Relation r ON r.statementid = s.id
				AND r.TypeRelationshipId IN (
					3
					,4
					,9
					,10
					,23
					,24
					)
				AND RelationClientId IS NOT NULL
			LEFT JOIN Client c2 ON c2.id = r.relationclientid
			LEFT JOIN Document d2 ON c2.id = d2.clientid
				AND (
					d2.typedocumentid = 1
					OR d2.typedocumentid = 3
					)
			LEFT JOIN prices ON prices.StatementId = s.Id
				AND prices.PriceNum = 1
			LEFT JOIN latestMarriageDocs m ON s.id = m.statementid
				AND m.RowNum = 1
			WHERE i.ListId = @id
				AND i.STATE = 0
			ORDER BY Id
			) AS tab
		) AS tab1
	ORDER BY isnull(RegDate, 65535)
		,Id ASC
		,relcount DESC
END
29 янв 13, 16:48    [13845775]     Ответить | Цитировать Сообщить модератору
 Re: Хранимая процедура зависает если использовать параметр  [new]
invm
Member

Откуда: Москва
Сообщений: 9838
http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx
29 янв 13, 16:56    [13845842]     Ответить | Цитировать Сообщить модератору
 Re: Хранимая процедура зависает если использовать параметр  [new]
zanders
Member

Откуда:
Сообщений: 13
invm
http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx


Спасибо помогло вот это:
1. Using dummy variables that are not directly displayed on parameters also ensure execution plan stability without need to add recompile hint, example below:

create procedure dbo.SearchProducts
@Keyword varchar(100)
As
Declare @Keyworddummy as varchar(100)
Set @Keyworddummy = @Keyword
select * from Products where Keyword like @Keyworddummy
30 янв 13, 06:32    [13848365]     Ответить | Цитировать Сообщить модератору
 Re: Хранимая процедура зависает если использовать параметр  [new]
Crimean
Member

Откуда:
Сообщений: 13147
и все равно "влетите" на вариант, когда план будет построен для "редко используемого" значения параметра, после чего начнет тормозить с этим планом до его перестроения для "часто используемого значения параметра". "значение" читать и как "селективность" тоже.. option( recompile ) не предлагаю, так как запрос не из легких, хотя, похоже, не должен интенсивно / конкурентно вызываться, хотя..
30 янв 13, 12:09    [13849720]     Ответить | Цитировать Сообщить модератору
 Re: Хранимая процедура зависает если использовать параметр  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Crimean
и все равно "влетите" на вариант, когда план будет построен для "редко используемого" значения параметра
Не влетит, ибо запрос уже не зависит от параметров процедуры.
1 фев 13, 03:35    [13860013]     Ответить | Цитировать Сообщить модератору
 Re: Хранимая процедура зависает если использовать параметр  [new]
Crimean
Member

Откуда:
Сообщений: 13147
Mind
Crimean
и все равно "влетите" на вариант, когда план будет построен для "редко используемого" значения параметра
Не влетит, ибо запрос уже не зависит от параметров процедуры.


от паматерров? конечно. но план, построенный для одного значения - будет использован и для другого
и все повторится. option( recompile ) наше все, если не планируется конкурентное использование
1 фев 13, 12:11    [13861067]     Ответить | Цитировать Сообщить модератору
 Re: Хранимая процедура зависает если использовать параметр  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Crimean
Mind
пропущено...
Не влетит, ибо запрос уже не зависит от параметров процедуры.


от паматерров? конечно. но план, построенный для одного значения - будет использован и для другого
и все повторится. option( recompile ) наше все, если не планируется конкурентное использование
Да не будет плана построенного для значения. Будет план для некой усредненной фигни. Если ТС это устраивает, то почему нет.
2 фев 13, 01:28    [13865236]     Ответить | Цитировать Сообщить модератору
 Re: Хранимая процедура зависает если использовать параметр  [new]
Crimean
Member

Откуда:
Сообщений: 13147
Mind
Да не будет плана построенного для значения. Будет план для некой усредненной фигни. Если ТС это устраивает, то почему нет.


ой ли.. у меня "на руках" сейчас такая ситуация как раз. пробую обойти через OPTIMIZE FOR UNKNOWN - вроде помогает. но случай не самый простой, правда, по "инфраструктуре"
4 фев 13, 12:17    [13871466]     Ответить | Цитировать Сообщить модератору
 Re: Хранимая процедура зависает если использовать параметр  [new]
Crimean
Member

Откуда:
Сообщений: 13147
дополню. да, ситуация не самая простая - есть секционированное представление и запрос к нему в хранимке
параметры хранимки НЕ имеют дефолтных значений
можно подобрать "удачные" сочетания вызовов, когда после переподнятия / recompile хранимки первый вызов отрабатывает быстро, но "сохраняет" план, на котором второй вызов работает очень долго. причина - разные "субпланы" для разных секций представления
если же воспользоваться указанным хинтом - "субпланы" будут "те, что надо" для всех секций представления
4 фев 13, 14:15    [13872467]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить