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

Откуда: Прага
Сообщений: 247
Добрый день,
на выходе алгоритма получается таблица (#rr), в которой нужно вывести периоды, которые содержат вложенные с большим параметром MinStay (запрос в конце кода).
Запрос то конечно считается, только при JOIN получается огромное количество комбинаций, просчет которых жрет время. Есть ли идеи куда двигаться, чтобы сократить время поиска вложенных периодов до 5-30 сек? Всем спасибо!

	create table #rr (datefrom smalldatetime,dateto smalldatetime,stay int,freedays int)
	create table #mms (datefrom smalldatetime,dateto smalldatetime,stay int,freedays int)
	create table #rez (datefrom smalldatetime,dateto smalldatetime,stay int,freedays int)
	CREATE UNIQUE CLUSTERED  INDEX foo ON #rez(datefrom,dateto,stay,freedays)


	
	INSERT INTO #rr (datefrom,dateto,stay,freedays)
	SELECT '20120101','20130101',1,0
	UNION ALL
	SELECT '20120101','20120301',5,0
	
	declare @stay int,@freedays int,@datefrom smalldatetime,@dateto smalldatetime,@bd int
	
	
			declare rsq cursor local static read_only for
			SELECT S.Stay,S.FreeDays,S.datefrom,S.dateto 
			FROM #rr S
			ORDER BY S.Stay ASC,S.FreeDays ASC
			OPEN rsq
			while 1=1
			begin
				fetch next FROM rsq INTO @stay,@freedays,@datefrom,@dateto
				if @@fetch_status=-1 
				   break
				if @@fetch_status=-2
				   continue


				SET @bd=@stay
				

				while @bd<=31
				begin
							INSERT INTO #mms
							(
								DateFrom,
								dateto,
								stay,
								freedays
							)
							SELECT D.datefrom,D.dateto,@stay,@freedays
							FROM 
							(
								SELECT D.Date datefrom,dateadd(day,@bd-1,D.Date) dateto
								FROM [master].[dbo].[Dates] D
								WHERE (D.Date>=@datefrom AND D.Date<=@dateto) AND dateadd(day,@bd,D.Date)<=dateadd(day,1,@dateto)
							) D

					set @bd=@bd+1
				end		
				
				
			
				DELETE M
				FROM #rez M INNER JOIN #mms X ON
				M.DateFrom=X.DateFrom AND M.dateto=X.dateto

				INSERT INTO #rez (datefrom,dateto,freedays,stay)
				SELECT datefrom,dateto,freedays,stay FROM #mms
				DELETE FROM #mms		
			end
			
	SELECT COUNT(*) FROM #rez		
	
	SELECT DISTINCT D.* 
	FROM #rez D INNER JOIN #rez T ON
	T.Stay>D.Stay OR (T.Stay=D.Stay AND T.FreeDays>D.FreeDays)
	AND (D.DateFrom<=T.DateFrom AND D.dateto>=T.dateto)
	
	
	
	drop table #rr
	drop table #mms
	drop table #rez
				
6 дек 11, 21:44    [11717172]     Ответить | Цитировать Сообщить модератору
 Re: Пересечения большого количества периодов  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
cavalero, алгоритм предлагаете нам восстановить самостоятельно, по приведенному фрагменту кода? Лучше опубликуйте задачу, а не ваш способ решения. Желательно согласно п.п. 4 и 6 Рекомендаций по оформлению сообщений.

ЗЫ: Пользовательские таблицы в системных БД -- моветон.
6 дек 11, 22:20    [11717383]     Ответить | Цитировать Сообщить модератору
 Re: Пересечения большого количества периодов  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Для начало уберите курсор нафиг.
7 дек 11, 02:48    [11718401]     Ответить | Цитировать Сообщить модератору
 Re: Пересечения большого количества периодов  [new]
cavalero
Member

Откуда: Прага
Сообщений: 247
Mnior
Для начало уберите курсор нафиг.

Курсор использован для формирования основной таблицы, JOIN в которой и вызывает проблемы. Время его работы по сравнению с JOIN'ом, приведенным ниже принебрежительно мало.
	SELECT DISTINCT D.* 
	FROM #rez D INNER JOIN #rez T ON
	T.Stay>D.Stay OR (T.Stay=D.Stay AND T.FreeDays>D.FreeDays)
	AND (D.DateFrom<=T.DateFrom AND D.dateto>=T.dateto)


Задача этого запроса вывести все периоды D, в которые входят периоды T, с условием того, что T.Stay>D.Stay OR (T.Stay=D.Stay AND T.FreeDays>D.FreeDays), что видно из запроса. Если делать это вот таким образом, как сделал я, то время вычисления неприемлемо. А как получить вложенность периодов не прибегая к такому JOIN'у мне не приходит в голову :(
7 дек 11, 13:25    [11720859]     Ответить | Цитировать Сообщить модератору
 Re: Пересечения большого количества периодов  [new]
aleks2
Guest
cavalero
	SELECT DISTINCT D.* 
	FROM #rez D INNER JOIN #rez T ON
	T.Stay>D.Stay OR (T.Stay=D.Stay AND T.FreeDays>D.FreeDays)
	AND (D.DateFrom<=T.DateFrom AND D.dateto>=T.dateto)



1.
create table #rez (datefrom smalldatetime,dateto smalldatetime,stay int,freedays int, primary key clustered(datefrom, dateto, stay))

2.

]
	SELECT D.* 
	FROM #rez D 
             WHERE exists(select * FROM #rez T WHERE 
                                 (D.DateFrom<=T.DateFrom AND D.dateto>=T.dateto)
	                    AND 
	                    T.Stay>=D.Stay AND (T.Stay>D.Stay OR T.FreeDays>D.FreeDays)
                        )   


3. Ну, и многое зависит от числа вложений по
(D.DateFrom<=T.DateFrom AND D.dateto>=T.dateto)
в сравнении с общим числом интервалов
7 дек 11, 14:18    [11721537]     Ответить | Цитировать Сообщить модератору
 Re: Пересечения большого количества периодов  [new]
cavalero
Member

Откуда: Прага
Сообщений: 247
aleks2
cavalero
	SELECT DISTINCT D.* 
	FROM #rez D INNER JOIN #rez T ON
	T.Stay>D.Stay OR (T.Stay=D.Stay AND T.FreeDays>D.FreeDays)
	AND (D.DateFrom<=T.DateFrom AND D.dateto>=T.dateto)



1.
create table #rez (datefrom smalldatetime,dateto smalldatetime,stay int,freedays int, primary key clustered(datefrom, dateto, stay))

2.

]
	SELECT D.* 
	FROM #rez D 
             WHERE exists(select * FROM #rez T WHERE 
                                 (D.DateFrom<=T.DateFrom AND D.dateto>=T.dateto)
	                    AND 
	                    T.Stay>=D.Stay AND (T.Stay>D.Stay OR T.FreeDays>D.FreeDays)
                        )   


3. Ну, и многое зависит от числа вложений по
(D.DateFrom<=T.DateFrom AND D.dateto>=T.dateto)
в сравнении с общим числом интервалов



50 секунд для данной комбинации... непозволительная роскошь
7 дек 11, 14:57    [11721998]     Ответить | Цитировать Сообщить модератору
 Re: Пересечения большого количества периодов  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
cavalero
Курсор использован для формирования основной таблицы
Оно и без курсора делается.
7 дек 11, 19:20    [11724656]     Ответить | Цитировать Сообщить модератору
 Re: Пересечения большого количества периодов  [new]
cavalero
Member

Откуда: Прага
Сообщений: 247
Mnior
cavalero
Курсор использован для формирования основной таблицы
Оно и без курсора делается.

Я не люблю рекурсию. Вы предложите что-нибудь по второй части, о которой собственно и вопрос. aleks2 дал хорошее решение. Правда у нас что то с сервером разработки творится и результаты скачут от 10 до 50 секунд при исполнении одного и того же запроса. На самом деле то количество периодов, которое в тесте (около 10 тыс) меньше раз в 10 чем то, что будет в реальности. Поэтому отпадает :(
7 дек 11, 19:32    [11724738]     Ответить | Цитировать Сообщить модератору
 Re: Пересечения большого количества периодов  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
cavalero
Я не люблю рекурсию.
Нет там никакой рекурсии. С такой формулировкой не каждый решится обдумывать задачу:
-- Struct
CREATE TABLE #Test (
	 DateFrom	SmallDateTime
	,DateTo		SmallDateTime
	,Stay		Int
	,FreeDays	Int
	,CONSTRAINT [PK_#rez] PRIMARY KEY (
		 DateFrom
		,DateTo
		,Stay
		,FreeDays
	)
)
GO
--------------------------------------------------------------------------------
-- Generate Test data
DECLARE	@Periods TABLE (DateFrom SmallDateTime ,DateTo SmallDateTime,Stay Int, FreeDays Int)
INSERT	@Periods VALUES
 ('20120101','20130101',1,0)
,('20120101','20120301',5,0)

INSERT	#Test
SELECT	 DateAdd(Day,D.number               ,S.DateFrom)
	,DateAdd(Day,D.number + N.number - 1,S.DateFrom)
	,Max(S.Stay)
	,Max(S.FreeDays)
FROM	     @Periods			S
	JOIN master.dbo.spt_values	N ON N.[type]	 = 'P'
					 AND N.number	>= S.Stay
					 AND N.number	<= 31
	JOIN master.dbo.spt_values	D ON D.[type]	 = 'P'
					 AND D.number	<= DateDiff(Day,S.DateFrom,S.DateTo) - N.number + 1
GROUP BY DateAdd(Day,D.number               ,S.DateFrom)
	,DateAdd(Day,D.number + N.number - 1,S.DateFrom)
--------------------------------------------------------------------------------
GO
-- Need optimize this:
SELECT	DISTINCT D.*
FROM	     #Test	D
	JOIN #Test	T ON T.Stay	>  D.Stay
			  OR T.Stay	 = D.Stay
			 AND T.FreeDays	>  D.FreeDays
			 AND T.DateFrom	<= D.DateFrom
			 AND T.DateTo	>= D.DateTo
GO
-- DROP TABLE #Test
8 дек 11, 11:09    [11727335]     Ответить | Цитировать Сообщить модератору
 Re: Пересечения большого количества периодов  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
cavalero
нужно вывести периоды, которые содержат вложенные с большим параметром MinStay
Поправка к первоначальному запросу:
SELECT	DISTINCT D.*
FROM	     #Test	D
	JOIN #Test	T ON T.DateFrom	<= D.DateFrom
			 AND T.DateTo	>= D.DateTo
			 AND(T.Stay	>  D.Stay
			  OR T.Stay	 = D.Stay
			 AND T.FreeDays	>  D.FreeDays)
aleks2
SELECT	D.* 
FROM	#Test D 
WHERE	Exists(	SELECT	*
		FROM	#Test T
		WHERE	    T.DateFrom	>= D.DateFrom
			AND T.DateTo	<= D.DateTo
			AND T.Stay	>= D.Stay
			AND(T.Stay	>  D.Stay
			 OR T.FreeDays	>  D.FreeDays)
	)
Очень существенная поправка.
SELECT	D.* 
FROM	#Test D 
WHERE	Exists(	SELECT	*
		FROM	#Test T
		WHERE	    T.DateFrom	>= D.DateFrom
			AND T.DateFrom	<= D.DateTo
			AND T.DateTo	<= D.DateTo
			AND T.Stay	>= D.Stay
			AND(T.Stay	>  D.Stay
			 OR T.FreeDays	>  D.FreeDays)
	)
aleks2, стареете?
SET STATISTICS IO ON
Число просмотров 10929, логических чтений 366499 (cavalero)
Число просмотров 10929, логических чтений 317303 (aleks2)
Число просмотров 10929, логических чтений 59552 (Mnior)

А ещё прикол в том что результаты у нас с первоначальным запросом отличаются.
8 дек 11, 11:29    [11727509]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить