Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 любители CTE узнали о табличных параметрах (to Mike_za)  [new]
o-o
Guest
как-то меня тут спросили, что в Reason for eEarly Termination of Statement
для наших гениальных запросов от любителей CTE,
а я говорю, переписываю нафиг, не пытаясь скомпилировать.

а сегодня думаю ок, посмотрю.
ну там Time Out, а что ждали

вот планец картинкой.

вот проца как образец применения табличного параметра через ж.
+
create procedure [dbo].[sp_MSA_Get_DataVintage]
(
	@tElencoPratiche dbo.tMSA_Pratica readonly	 
)
as
begin
 
	declare @tPratiche table
	(
		NUM_PRAT int,
		DATARICMAN date
	)
	 
	insert into @tPratiche
	select
		p.NUM_PRAT,
		p.DATARICMAN
		
	from
		coresql7.dbo.legprati p
		inner join @tElencoPratiche e on p.NUM_PRAT = e.NUM_PRAT
		
	 
	;with tUltimaDataDegradoNoTransito as
	(
		select 
			x.NUM_PRAT,
			x.data_valido_al ULTIMA_DATA_DEGRADO 
			
		from
		(
			select
				ROW_NUMBER() over (partition by lps.NUM_PRAT order by lps.data_valido_al desc) RowNumber,
				lps.NUM_PRAT,
				lps.data_valido_al 
			
			from
				(
					select 
						a.NUM_PRAT, 
						a.IDBanca, 
						a.cod_portafoglio, 
						a.CODRESPINT, 
						a.data_valido_al 
						
					from 
						coresql7.dbo.legprati_storico a
						inner join  @tElencoPratiche b on a.NUM_PRAT = b.NUM_PRAT
						
					union 
					
					select 
						a.NUM_PRAT, 
						a.IDBanca, 
						a.cod_portafoglio, 
						a.CODRESPINT ,
						'99990101' 
					
					from 
						coresql7.dbo.legprati a
						inner join  @tElencoPratiche b on a.NUM_PRAT = b.NUM_PRAT
				) lps 

				inner join coresql7.dbo.Collegamenti_contratto_portafoglio ccp on lps.cod_portafoglio = ccp.cod_portafoglio and lps.IDBanca = ccp.idbanca
				inner join coresql7.dbo.legutent lu on lu.CODUTENTE = lps.CODRESPINT
				inner join  coresql7.dbo.profili pf on pf.codprofilo = lu.CODPROFILO
			
			where
				ccp.tipo_contratto in (2, 3, 7)
				and lps.cod_portafoglio  not in (9, 3636, 3637, 3638, 3639, 3640)
				and  pf.codufficio not in (16, 17, 20)
		    
		) x
		
		where
			x.RowNumber = 1 
	 
	)
	--  prima data di degrado per le pratiche degradate
	, tPrimaDataDegradoNoTransito as
	(

		select
			NUM_PRAT,
			data_valido_al PRIMA_DATA_DEGRADO
		
		from
		(
			select 
				ROW_NUMBER() over (partition by y.NUM_PRAT order by lps.data_valido_al desc) RowNumber,
				y.NUM_PRAT,
				lps.data_valido_al  
				
			from
			(
				select	
					*
					
				from
				(
			
					select
						ROW_NUMBER() over (partition by lps.NUM_PRAT order by lps.data_valido_al asc) RowNumber,
						p.NUM_PRAT,
						lps.data_valido_al 
					
					from
						@tPratiche p
						inner join  coresql7.dbo.legprati_storico lps on p.NUM_PRAT = lps.NUM_PRAT
						inner join coresql7.dbo.Collegamenti_contratto_portafoglio ccp on lps.cod_portafoglio = ccp.cod_portafoglio and lps.IDBanca = ccp.idbanca
						inner join coresql7.dbo.legutent lu on lu.CODUTENTE = lps.CODRESPINT
						inner join  coresql7.dbo.profili pf on pf.codprofilo = lu.CODPROFILO
					
					where					
						ccp.tipo_contratto in (2, 3, 7)
						and lps.cod_portafoglio  not in (9, 3636, 3637, 3638, 3639, 3640)
						and  pf.codufficio not in (16, 17, 20)

				) x
				
				where
					x.RowNumber = 1 
				
			) y
			inner join  coresql7.dbo.legprati_storico lps on y.NUM_PRAT = lps.NUM_PRAT
		 
		 where
			lps.data_valido_al < y.data_valido_al
		
		) k
		where 
			k.RowNumber = 1 
	)

	-- data di passaggio a degrado
	, tDataPassaggioDegrado as
	(
		select 
			x.NUM_PRAT,
			x.DATA_PASSAGGIO_DEGRADO
				
		from
		(
			select
				ROW_NUMBER() over (partition by lps.NUM_PRAT order by lps.data_valido_al desc) RowNumber,
				lps.NUM_PRAT,
				case 
					/*se ad oggi non sono degradate ... */
					when ccp2.tipo_contratto not in (2,3,7) then null
					else
					
						/* se hanno degrado e non una prima data degrado allora prendo il prim valore della
						   legprati_storico */
						   isnull(tP.PRIMA_DATA_DEGRADO, lps.data_valido_al) 
						   
				end DATA_PASSAGGIO_DEGRADO
				
			from
				tUltimaDataDegradoNoTransito d 
				left join coresql7.dbo.legprati_storico lps	on d.NUM_PRAT = lps.NUM_PRAT
				left join coresql7.dbo.Collegamenti_contratto_portafoglio ccp on lps.cod_portafoglio = ccp.cod_portafoglio and lps.IDBanca = ccp.idbanca
				left join tPrimaDataDegradoNoTransito tP on d.NUM_PRAT = tP.NUM_PRAT
				left join coresql7.dbo.legprati p on lps.NUM_PRAT = p.NUM_PRAT
				left join coresql7.dbo.Collegamenti_contratto_portafoglio ccp2 on p.cod_portafoglio = ccp2.cod_portafoglio and p.IDBanca = ccp2.idbanca
			
			where
				 cast(lps.data_valido_al as date) < d.ULTIMA_DATA_DEGRADO
				 and ccp.tipo_contratto not in (2, 3, 7)
		    
		) x
		
		where
			x.RowNumber = 1 
	)
	--/*ultima data di transito */
	, tDataUscitaTransito as
	(	
		select 
			NUM_PRAT,
			case 
				when data_valido_al	= '99990101' then null 
				else data_valido_al
			end ULTIMA_DATA_USCITA_TRANSITO
		from
		(
			select
				ROW_NUMBER() over (partition by lps.NUM_PRAT order by lps.data_valido_al desc) RowNumber,
				lps.*

			from
				@tPratiche p
				inner join 
				(
					select 
						a.NUM_PRAT, 
						a.cod_portafoglio, 
						a.CODRESPINT, 
						a.data_valido_al 
					
					from 
						coresql7.dbo.legprati_storico  a
						inner join  @tElencoPratiche b on a.NUM_PRAT = b.NUM_PRAT
						
					union
					 
					select 
						a.NUM_PRAT, 
						a.cod_portafoglio, 
						a.CODRESPINT ,
						'99990101' 
						
					from 
						coresql7.dbo.legprati a
						inner join  @tElencoPratiche b on a.NUM_PRAT = b.NUM_PRAT
				) lps 
				on p.NUM_PRAT = lps.NUM_PRAT
				inner join coresql7.dbo.legutent lu on lu.CODUTENTE = lps.CODRESPINT
				inner join coresql7.dbo.profili pf on pf.codprofilo = lu.CODPROFILO 
				
			where
				(lps.cod_portafoglio  in (9, 3636, 3637, 3638, 3639, 3640)
				or pf.codufficio in (16,17,20))

		) lps

		where
			lps.RowNumber = 1
	)  
	 
	select
		t.NUM_PRAT,
		cast(t.DATA_VINTAGE as date) DATA_VINTAGE

	from
	(
		select
			p.NUM_PRAT,
			case 
				when (d.DATA_PASSAGGIO_DEGRADO is null and t.ULTIMA_DATA_USCITA_TRANSITO is null) then 
						 p.DATARICMAN
						 
					when d.DATA_PASSAGGIO_DEGRADO is not null and t.ULTIMA_DATA_USCITA_TRANSITO is null then  
						case when d.DATA_PASSAGGIO_DEGRADO > p.DATARICMAN then d.DATA_PASSAGGIO_DEGRADO else p.DATARICMAN end
						
					when d.DATA_PASSAGGIO_DEGRADO is not null and t.ULTIMA_DATA_USCITA_TRANSITO is not null then 
						case when d.DATA_PASSAGGIO_DEGRADO > t.ULTIMA_DATA_USCITA_TRANSITO  then d.DATA_PASSAGGIO_DEGRADO else t.ULTIMA_DATA_USCITA_TRANSITO end
				  
					when d.DATA_PASSAGGIO_DEGRADO is null and t.ULTIMA_DATA_USCITA_TRANSITO is not null then 
						t.ULTIMA_DATA_USCITA_TRANSITO
				
			end DATA_VINTAGE 

		from
			@tPratiche  p
			left join tDataUscitaTransito t on p.NUM_PRAT = t.NUM_PRAT
			left join tDataPassaggioDegrado d on p.NUM_PRAT = d.NUM_PRAT

	) t


end


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

какая красота!
сегодня пришлось это все килить после 2ух часов работы.
на входе таблица в 2,5 млн, ей же самое место в переменной,
инкапсуляция же.
сервер считает, что на выходе 1 строка,
и хотя во всех соединениях hash join,
как только выплывает переменная, так сразу Nested Loops.
и уходит в кому
----
думаете, мне сказало спасибо чучелко за переписывание его процедуры,
к-ая теперь 30 секунд работает только превращением CTE в #CTE
и обращением напрямую к таблице в 2,5 млн строк?
не, оно теперь обиженно со мной не разговаривает вообще
----
to Колосов: вот какой тут нафиг OLAP?
это же бубль гум какой-то, вот они, наши мины и максы, считаемые через row_number() = 1, ибо модно.
----
логика их мне неведома, но в результате беспредел на сервере.
если кто желает план, могу подарить.
разумеется, оценочный
----
извиняюсь за выплеск, жаловаться больше некому, просто не поймут

К сообщению приложен файл. Размер - 84Kb
20 окт 16, 16:21    [19805355]     Ответить | Цитировать Сообщить модератору
 Re: любители CTE узнали о табличных параметрах (to Mike_za)  [new]
o-o
Guest
вот отловленный план, это пока типчик еще запускал на 0.5 млн.
вообще периметр на сегодня 2,5 млн

К сообщению приложен файл (slaviero_plan.zip - 24Kb) cкачать
20 окт 16, 16:27    [19805376]     Ответить | Цитировать Сообщить модератору
 Re: любители CTE узнали о табличных параметрах (to Mike_za)  [new]
АвторОтвета
Guest
На лицо личная неприязнь. Не знаю зачем это здесь...
20 окт 16, 16:37    [19805434]     Ответить | Цитировать Сообщить модератору
 Re: любители CTE узнали о табличных параметрах (to Mike_za)  [new]
АвторОтвета
Guest
Хотя выговориться иногда полезно... понимаю
20 окт 16, 16:39    [19805452]     Ответить | Цитировать Сообщить модератору
 Re: любители CTE узнали о табличных параметрах (to Mike_za)  [new]
_human
Member

Откуда:
Сообщений: 569
o-o,

встречал такой же подход с передачей непонятного объема данных в процедуру табличной переменной.
И все было здорово до того момента пока клиент не пожаловался на скорость работы, т.к. процедуры стали виснуть по пару часов.
Только потом переписали через временные таблицы.

Вывод - люди все это знают и видят очевидные проблемы в перспективе, но все равно наступают на грабли, имхо
20 окт 16, 17:11    [19805626]     Ответить | Цитировать Сообщить модератору
 Re: любители CTE узнали о табличных параметрах (to Mike_za)  [new]
komrad
Member

Откуда:
Сообщений: 5906
o-o
как только выплывает переменная, так сразу Nested Loops.
и уходит в кому

это не кома, а кропотливая работа ;)
20 окт 16, 17:13    [19805638]     Ответить | Цитировать Сообщить модератору
 Re: любители CTE узнали о табличных параметрах (to Mike_za)  [new]
o-o
Guest
_human
Вывод - люди все это знают и видят очевидные проблемы в перспективе, но все равно наступают на грабли, имхо

это не про наших людей.
вчера немцы вырубили сервер, ибо упорно падало BO.
чтобы поднять BO логично остановить SQL Server :)
после этого ночные джобы не запустили,
отчетникам написали, что данных нет и не будет, все типа отдыхайте.
и вот тут *на абсолютно пустом сервере* начал тестировать свой дерьмантин вышеупомянутый товарищ.
после 2ух часов поинтересовался,
какая такая нагрузка у нас сегодня на сервере и почему он не получил до сих пор результат
в ответ он получил свой план и переписанную процедуру.
...но сейчас все же сказал спасибо, после вываливания его творения на форум.
наверное потому, что кропотливая работа, спровоцированная его процедурой, никак не желала завершаться.
20 окт 16, 17:20    [19805685]     Ответить | Цитировать Сообщить модератору
 Re: любители CTE узнали о табличных параметрах (to Mike_za)  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
автор
.но сейчас все же сказал спасибо, после вываливания его творения на форум.

результат получен, расходимся :)
а по сабжу когда в руках молоток всё кажется гвоздями
20 окт 16, 17:23    [19805700]     Ответить | Цитировать Сообщить модератору
 Re: любители CTE узнали о табличных параметрах (to Mike_za)  [new]
o-o
Guest
АвторОтвета
На лицо личная неприязнь. Не знаю зачем это здесь...

это просто ответ на вот это 19781469
и вот это 19781747
с картинкой и планом
----
но вообще да, такую личную неприязнь испытываю, что даже кушать не могу
20 окт 16, 17:25    [19805707]     Ответить | Цитировать Сообщить модератору
 Re: любители CTE узнали о табличных параметрах (to Mike_za)  [new]
ппп-пп
Guest
Это я так понимаю человека на заказчика жалуется или программистов заказчика, работая в аутсорсинге.
И зачем тут жаловаться. Озвученные факты ведь резко повышают его стоимость и восстребованность. Тут радоваться надо.

А Вам действительно спасибо надо? Может лучше взять деньгами?
20 окт 16, 18:10    [19805820]     Ответить | Цитировать Сообщить модератору
 Re: любители CTE узнали о табличных параметрах (to Mike_za)  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32167
o-o
да есть уже таблица постоянная, каждый день заполняемая.
вот ее копируют в переменную,
а переменную передают в процедуру

какая красота!
сегодня пришлось это все килить после 2ух часов работы.
на входе таблица в 2,5 млн, ей же самое место в переменной,
инкапсуляция же.
ООП. Смузи и коворкинг.
_human
И все было здорово до того момента пока клиент не пожаловался на скорость работы, т.к. процедуры стали виснуть по пару часов.
Это даже продержится не до жалоб на скорость, это продержится до начала реальной эксплуатации, до тестового, пилотного внедрения.

У них же всё такое, красивый процесс разработки ради красивого процесса разработки, и никто у них не делал что то реальное, то есть не создавал заказчику производственный актив для заколачивания бабла.
20 окт 16, 19:01    [19805947]     Ответить | Цитировать Сообщить модератору
 Re: любители CTE узнали о табличных параметрах (to Mike_za)  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32167
o-o
это просто ответ на вот это 19781469
и вот это 19781747
с картинкой и планом
О, план красив!!!
20 окт 16, 19:09    [19805962]     Ответить | Цитировать Сообщить модератору
 Re: любители CTE узнали о табличных параметрах (to Mike_za)  [new]
o-o
Guest
ппп-пп
Это я так понимаю человека на заказчика жалуется или программистов заказчика, работая в аутсорсинге.
И зачем тут жаловаться. Озвученные факты ведь резко повышают его стоимость и восстребованность. Тут радоваться надо.

А Вам действительно спасибо надо? Может лучше взять деньгами?

Нет, все не так.
И я, и этот тип, мы в одной консалтинговой конторе работаем,
просто в разных городах.
У меня была своеобразная начальница (преставитель клиента, к которому нас заслали) ,
ее свергли.
Начальница же того типа дура полнейшая,
но рангом повыше моей бывшей,
ее вряд ли попрут.
Когда мою свергли, в перспективе меня бы отправили к черту на кулички в смысле логистики,
к другому клиенту, тк здесь за мной некому надзирать.
Моей конторе это невыгодно, здесь им за меня хорошо платят, мне тоже невыгодно ездить в другое место.
Отдел тут расформировали, меня взяли в другой: делать совсем другое.
Типа им нужны понимающие, только там экономисты, я их птичий язык плохо понимаю.
Когда есть время, я интересуюсь тем, что происходит на сервере.
Ну или когда те во главе с дурой в тупике, то тоже спрашивают по старой памяти,
только вот они IT, а я нет.
Вопрос здесь не в том, спасибо или нет,
здесь мораль другая:
неспособные издеватели кладут сервер и даже не желают вникать, что не так.
Но их место возле сервера пока что гарантировано, а мне уже сто раз намекнули, что мое дело совсем не сервер мониторить.
Вывалить я могу только на форум, остальные все равно слов таких не знают, планы еще какие-то...
Про ДБА разговор отдельный, они похлеще девелоперов и они тоже наемные, зотя контора уже не наша.
За сервер обидно, но и пойти особо некуда.
Поэтому спасибо, что выслушали,
надо было иллюстрацией в прошлой теме оформить,
но вот руки зачесались отдельно вынести,
больше все равно ничего сделать не могу
20 окт 16, 19:38    [19806027]     Ответить | Цитировать Сообщить модератору
 Re: любители CTE узнали о табличных параметрах (to Mike_za)  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1908
o-o,

+
Ну рано еще для пятничных тредов. Мог бы потерпеть денек
20 окт 16, 20:13    [19806109]     Ответить | Цитировать Сообщить модератору
 Re: любители CTE узнали о табличных параметрах (to Mike_za)  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
o-o,

Очень познавательно, сочувствую))
20 окт 16, 20:42    [19806225]     Ответить | Цитировать Сообщить модератору
 Re: любители CTE узнали о табличных параметрах (to Mike_za)  [new]
iap
Member

Откуда: Москва
Сообщений: 47194
Помнится, ещё любят в триггере вызывать всякие процедуры,
а для передачи им содержимого deleted и inserted
приходится из этих псевдотаблиц перегонять данные во временные таблицы.
20 окт 16, 21:18    [19806319]     Ответить | Цитировать Сообщить модератору
 Re: любители CTE узнали о табличных параметрах (to Mike_za)  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
where
(lps.cod_portafoglio in (9, 3636, 3637, 3638, 3639, 3640)
or pf.codufficio in (16,17,20))

Магические константы?
20 окт 16, 21:27    [19806339]     Ответить | Цитировать Сообщить модератору
 Re: любители CTE узнали о табличных параметрах (to Mike_za)  [new]
o-o
Guest
у этих вообще в триггере курсоры.
потому что из deleted и inserted читают по одной строке.
мне фиолетово, это вылезло при скриптовании таблицы из восстановленной базы
(у меня выставлены в студии опции скриптовать вообще все,
даже коллэйшены столбцов,
чтобы все тайное стало явным до возможных сюрпризов)
они со своего сервера ресторят нам пару баз, делая их ридонли,
так что нас это не касается,
но просто лишнего индекса от них не допросишься: ой как нам это замедлит вставку,
а у самих триггеры на инсерт с курсорной обработкой
20 окт 16, 21:32    [19806344]     Ответить | Цитировать Сообщить модератору
 Re: любители CTE узнали о табличных параметрах (to Mike_za)  [new]
o-o
Guest
Mike_za
where
(lps.cod_portafoglio in (9, 3636, 3637, 3638, 3639, 3640)
or pf.codufficio in (16,17,20))

Магические константы?

в соседней базе у них есть куча процедур со списком в 200 констант.
и у той базы выставлена forced-параметризация
(в моей уже нет, я за ними тайно зачищаю,
у нас ETL-процедуры и все константы это именно константы,
ничего параметризовать не надо, список не меняется)
вот этот список превращается в 200 магических параметров,
про оценки я молчу
20 окт 16, 21:39    [19806360]     Ответить | Цитировать Сообщить модератору
 Re: любители CTE узнали о табличных параметрах (to Mike_za)  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
o-o,

Вы прям через чур асто вспоминаете своих начальниц ))
20 окт 16, 23:12    [19806571]     Ответить | Цитировать Сообщить модератору
 Re: любители CTE узнали о табличных параметрах (to Mike_za)  [new]
o-o
Guest
TaPaK
o-o,

Вы прям через чур асто вспоминаете своих начальниц ))

начальниц?
рассказов может и много Картинка с другого сайта. но героиня в них всех одна Картинка с другого сайта.
21 окт 16, 00:23    [19806723]     Ответить | Цитировать Сообщить модератору
 Re: любители CTE узнали о табличных параметрах (to Mike_za)  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
+
Ох уж эти страсти итальянские
21 окт 16, 00:34    [19806732]     Ответить | Цитировать Сообщить модератору
 Re: любители CTE узнали о табличных параметрах (to Mike_za)  [new]
aleksrov
Member

Откуда:
Сообщений: 948
Блин, забавно коминты читать
/* se hanno degrado e non una prima data degrado allora prendo il prim valore della legprati_storico */
ощущение как будто включил ящик на канале Россия :)
Я думал, что в международных компаниях коменты на анлийском всегда.
21 окт 16, 06:11    [19806816]     Ответить | Цитировать Сообщить модератору
 Re: любители CTE узнали о табличных параметрах (to Mike_za)  [new]
Pavel1211
Member

Откуда: Екатеринбург
Сообщений: 205
o-o
У меня была своеобразная начальница (преставитель клиента, к которому нас заслали) ,
ее свергли.

Ну наконец-то ))))
21 окт 16, 06:38    [19806824]     Ответить | Цитировать Сообщить модератору
 Re: любители CTE узнали о табличных параметрах (to Mike_za)  [new]
aleks2
Guest
o-o
(в моей уже нет, я за ними тайно зачищаю,


Абсолютная (не сказать бы точнее) глупость - делать чужую работу.
Спасибо не скажут? премию тож не дадут.
В условиях капитализма делать надо только свою работу.
Ну, по возможности, хорошо.
Хотя усираться тоже корысти нету.
21 окт 16, 07:44    [19806884]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить