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

Откуда:
Сообщений: 148
Добрый день!
Товарищи, помогите оптимизировать цикл.
Есть таблица договоров ~ 140 тыс.
Необходимо в цикле пройтись по каждому договору в первом цикле и во втором цикле по месяцам от даты открытия до текущей даты.
В результате должно получиться вот такая таблица:
PRODUCTID	OPEN_DATE	MOB	Active
1568748	27.12.2010	1	0
1568748	27.12.2010	2	1
1568748	27.12.2010	3	1
1568748	27.12.2010	4	1
1568748	27.12.2010	5	1


Данный запрос на 140 тыс. отрабатывается ~ 11 часов.


-- Создаем врем. табл. для заливкиданных для винтажа. 
CREATE TABLE [RB].[Vintage_CreditCard_tmp](
	[PRODUCTID] [int] NULL,
	[OPEN_DATE] [date] NULL,
	--[acc_md_open] [date] NULL,
	--[Mnth_Activation] [int] NULL,
	[MOB] [int] NULL,
	[Active] [int] NULL
) ON [PRIMARY]



-- Создаем таблицу с ID полем
CREATE TABLE ##CreditCard(
	[id] [int] IDENTITY(1,1) NOT NULL,
	[PRODUCTID] [int] NULL,
	[OPEN_DATE] [date] NULL,
	[acc_md_open] [date] NULL,
	[Mnth_Activation] [int] NULL,
	[MOB] [int] NULL	
) ON [PRIMARY]

-- Создание индекса
CREATE NONCLUSTERED INDEX [ind_id] ON ##CreditCard
(id ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]



--Truncate table RB.Vintage_CreditCard

--Выбираем договора КК
Insert into ##CreditCard
SELECT TOP 100 
	t1.[PRODUCTID]
	,t2.STARTDATE as OPEN_DATE
	,t1.[acc_md_open]     
	,Case 
		When t1.[acc_md_open] is not null Then DATEDIFF(mm, t2.STARTDATE, t1.[acc_md_open])
		Else null
	End as 'Mnth_Activation'
	,DATEDIFF(mm,t2.STARTDATE,GETDATE()) as 'MOB'
FROM [DWH_Financial_Analysis].[RB].[car_products] t1 Join (SELECT
																t1.PRODUCTID
																,convert(date,max(STARTDATE),104) as STARTDATE
															FROM RB.car_productactionhistory t1 Join RB.car_products t2 on (t1.PRODUCTID = t2.PRODUCTID)
															WHERE t1.[PRODUCTACTIONID]='1129' and t2.STATUSID = 113
															Group By t1.PRODUCTID) t2 on (t1.PRODUCTID = t2.PRODUCTID)
Where [STATUSID] = 113 and CREDITMODULETYPEINBANKSYSTEM = 'CC'
	




--Цикл по договорам
Declare @MinItem int
Declare @MaxItem int
Declare @Active int
Declare @NextMOB int

Declare @PRODUCTID int
Declare @OPEN_DATE date
Declare @acc_md_open date
Declare @Mnth_Activation int
Declare @MOB int 



Select @MinItem = MIN(id) From ##CreditCard
Select @MaxItem = MAX(id) From ##CreditCard



--Set @CountCC = 10-- (Select COUNT(*) From ##CreditCard)

--print '************************************************'
--print '@MinItem: ' + cast(@MinItem as varchar(10))
--print '@MaxItem: ' + cast(@MaxItem as varchar(10))
--print '@MOB: ' + cast(@MOB as varchar(10))
--print '************************************************'

While @MaxItem >=  @MinItem
Begin
	If @MinItem = (Select id From ##CreditCard Where id = @MinItem)
		Begin
			Set @Active = 0
			Set @NextMOB = 0
			
			Select @PRODUCTID = PRODUCTID From ##CreditCard Where id = @MinItem
			Select @OPEN_DATE = OPEN_DATE From ##CreditCard Where id = @MinItem
			Select @acc_md_open = acc_md_open From ##CreditCard Where id = @MinItem
			Select @Mnth_Activation = Mnth_Activation From ##CreditCard Where id = @MinItem
			Select @MOB = MOB From ##CreditCard Where id = @MinItem
			
			--print 'PRODUCTID: '+ cast(@PRODUCTID as varchar(20)) 
			--		+ ' OPEN_DATE: ' + cast(@OPEN_DATE as varchar(10)) 
			--		+ ' acc_md_open: ' + cast(@acc_md_open as varchar(10)) 
			--		+ ' Mnth_Activation: ' + cast(@Mnth_Activation as varchar(10)) 
			--		+ ' MOB: ' + cast(@MOB as varchar(10)) 
			
			While @MOB >= @NextMOB
			Begin
				Set @Active = (Select 
									Case 
										When @NextMOB >= @Mnth_Activation Then 1 
										Else 0 
									End   
								From ##CreditCard Where id = @MinItem)
				
				--Print 'NextMOB: ' + cast(@NextMOB as varchar(10)) + '  MOB ' + cast(@MOB as varchar(10)) 
				--	+ ' Active: ' + cast(@Active as varchar(10))
				
				Insert into RB.Vintage_CreditCard_tmp
				(
					[PRODUCTID]
					,[OPEN_DATE]
					--,[acc_md_open]
					--,[Mnth_Activation]
					,[MOB]
					,[Active]
				)
				Values (
					@PRODUCTID
					,@OPEN_DATE
					--,@acc_md_open
					--,@Mnth_Activation
					,@NextMOB
					,@Active
				)
				Set @NextMOB += 1
			End 
		End
	Set @MinItem += 1
End;


--exec RB.imp_Vintage_CreditCard
--Select * From RB.Vintage_CreditCard


Declare @count int
Select @count = COUNT(*)  From RB.Vintage_CreditCard

If @count > 1000000
Begin
	Truncate table RB.Vintage_CreditCard
	
	Insert into RB.Vintage_CreditCard
	Select * From RB.Vintage_CreditCard_tmp
End



Drop table RB.Vintage_CreditCard_tmp
Drop table ##CreditCard


10 май 11, 11:27    [10628059]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать цикл  [new]
Glory
Member

Откуда:
Сообщений: 104751
Замените ваш цикл на один запрос
10 май 11, 11:30    [10628075]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать цикл  [new]
stavdius
Member

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

если не сложно, подскажите, как сделать?
10 май 11, 11:33    [10628108]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать цикл  [new]
iljy
Member

Откуда:
Сообщений: 8711
stavdius
Glory,

если не сложно, подскажите, как сделать?

Не сложно. Но сначала выполните.
10 май 11, 11:35    [10628117]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать цикл  [new]
Начинающий SQL 2008
Member

Откуда:
Сообщений: 438
stavdius,
не совсем понятно...

Приведите простые заполненные тестовые таблицы для результата
PRODUCTID	OPEN_DATE	MOB	Active
1568748	27.12.2010	1	0
1568748	27.12.2010	2	1
1568748	27.12.2010	3	1
1568748	27.12.2010	4	1
1568748	27.12.2010	5	1

Что-то типа такого:
declare @t table (Parent varchar(50), Child varchar(50))
insert @t
 select 'Depart 1','Podrazdel 1_1' union all select 'Depart 1','Podrazdel 1_2' union all select 'Depart 2','Podrazdel 2_1' union all
 select 'Depart 2','Podrazdel 2_2' union all select 'Podrazdel 1_1','Otdel 1_1' union all select 'Podrazdel 1_1','Otdel 1_2' 

select * from @t
10 май 11, 11:38    [10628138]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать цикл  [new]
stavdius
Member

Откуда:
Сообщений: 148
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)



Запрос с примером:


-- Создаем врем. табл. для заливкиданных для винтажа. 
CREATE TABLE ##Vintage_CreditCard_tmp(
	[PRODUCTID] [int] NULL,
	[OPEN_DATE] [date] NULL,
	--[acc_md_open] [date] NULL,
	--[Mnth_Activation] [int] NULL,
	[MOB] [int] NULL,
	[Active] [int] NULL
) ON [PRIMARY]


declare @CreditCard table (
				[id] [int] IDENTITY(1,1) NOT NULL,
				[PRODUCTID] [int] NULL,
				[OPEN_DATE] [date] NULL,
				[acc_md_open] [date] NULL,
				[Mnth_Activation] [int] NULL,
				[MOB] [int] NULL
				)
insert @CreditCard
 select '865010', '2008-01-14', '2008-01-28', '0', '40' 
 union all 
 select '884531', '2008-01-20', '2008-04-18', '3', '40' 
 union all 
 select '884533', '2008-02-02', '2008-08-07', '6', '39' 
 union all 
 select '884996', '2008-01-10', '', '', '40' 
  union all 
 select '885105', '2008-01-09', '2008-11-05', '10', '40' 

select * from @CreditCard






--Truncate table RB.Vintage_CreditCard





--Цикл по договорам
Declare @MinItem int
Declare @MaxItem int
Declare @Active int
Declare @NextMOB int

Declare @PRODUCTID int
Declare @OPEN_DATE date
Declare @acc_md_open date
Declare @Mnth_Activation int
Declare @MOB int 



Select @MinItem = MIN(id) From @CreditCard
Select @MaxItem = MAX(id) From @CreditCard



--Set @CountCC = 10-- (Select COUNT(*) From @CreditCard)

--print '************************************************'
--print '@MinItem: ' + cast(@MinItem as varchar(10))
--print '@MaxItem: ' + cast(@MaxItem as varchar(10))
--print '@MOB: ' + cast(@MOB as varchar(10))
--print '************************************************'

While @MaxItem >=  @MinItem
Begin
	If @MinItem = (Select id From @CreditCard Where id = @MinItem)
		Begin
			Set @Active = 0
			Set @NextMOB = 0
			
			Select @PRODUCTID = PRODUCTID From @CreditCard Where id = @MinItem
			Select @OPEN_DATE = OPEN_DATE From @CreditCard Where id = @MinItem
			Select @acc_md_open = acc_md_open From @CreditCard Where id = @MinItem
			Select @Mnth_Activation = Mnth_Activation From @CreditCard Where id = @MinItem
			Select @MOB = MOB From @CreditCard Where id = @MinItem
			
			--print 'PRODUCTID: '+ cast(@PRODUCTID as varchar(20)) 
			--		+ ' OPEN_DATE: ' + cast(@OPEN_DATE as varchar(10)) 
			--		+ ' acc_md_open: ' + cast(@acc_md_open as varchar(10)) 
			--		+ ' Mnth_Activation: ' + cast(@Mnth_Activation as varchar(10)) 
			--		+ ' MOB: ' + cast(@MOB as varchar(10)) 
			
			While @MOB >= @NextMOB
			Begin
				Set @Active = (Select 
									Case 
										When @NextMOB >= @Mnth_Activation Then 1 
										Else 0 
									End   
								From @CreditCard Where id = @MinItem)
				
				--Print 'NextMOB: ' + cast(@NextMOB as varchar(10)) + '  MOB ' + cast(@MOB as varchar(10)) 
				--	+ ' Active: ' + cast(@Active as varchar(10))
				
				Insert into ##Vintage_CreditCard_tmp
				(
					[PRODUCTID]
					,[OPEN_DATE]
					--,[acc_md_open]
					--,[Mnth_Activation]
					,[MOB]
					,[Active]
				)
				Values (
					@PRODUCTID
					,@OPEN_DATE
					--,@acc_md_open
					--,@Mnth_Activation
					,@NextMOB
					,@Active
				)
				Set @NextMOB += 1
			End 
		End
	Set @MinItem += 1
End;


Select * From ##Vintage_CreditCard_tmp


10 май 11, 11:59    [10628287]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать цикл  [new]
Alexandr Kr.
Member

Откуда: Украина, Харьков
Сообщений: 165
stavdius,


declare @CreditCard table (
[id] [int] IDENTITY(1,1) NOT NULL,
[PRODUCTID] [int] NULL,
[OPEN_DATE] [date] NULL,
[acc_md_open] [date] NULL,
[Mnth_Activation] [int] NULL,
[MOB] [int] NULL
)
insert @CreditCard
select '865010', '2008-01-14', '2008-01-28', '0', '40'
union all
select '884531', '2008-01-20', '2008-04-18', '3', '40'
union all
select '884533', '2008-02-02', '2008-08-07', '6', '39'
union all
select '884996', '2008-01-10', '', '', '40'
union all
select '885105', '2008-01-09', '2008-11-05', '10', '40'

select a.PRODUCTID,a.OPEN_DATE,b.number,case when b.number>=Mnth_Activation then 1 else 0 end as Active
from @CreditCard a join master..spt_values b on a.MOB>=b.number and b.type='P'
10 май 11, 12:09    [10628335]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать цикл  [new]
stavdius
Member

Откуда:
Сообщений: 148
Alexandr Kr.,

спасибо, я такого способа и не знал (((
Сейчас запущу, проверю быстродействие.
еще раз спасибо!
10 май 11, 12:29    [10628452]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать цикл  [new]
stavdius
Member

Откуда:
Сообщений: 148
Alexandr Kr.,

результат: 56 сек!
спасибо!
10 май 11, 13:11    [10628720]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить