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

Откуда: Рига, Латвия
Сообщений: 128
Создаю таблицу из 1 млн целых чисел, представляющих собой 10 отдельных интервалов. Хочу написать SELECT, который вернёт эти 10 интервалов в виде 10 строк.

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

+
create table #T
(
	N int primary key
)

insert into
	#T
select
	((((N6 * 20 + N5) * 10 + N4) * 10 + N3) * 10 + N2) * 10 + N1
from
	(select N1 = 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as N1,
	(select N2 = 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as N2,
	(select N3 = 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as N3,
	(select N4 = 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as N4,
	(select N5 = 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as N5,
	(select N6 = 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as N6

select
	FromN,
	ToN
from
	(
		select
			*,
			FromN = N,
			ToN = case when IsRightBound = 1 then N else Lead(N) over (order by N) end
		from
			(
				select
					*
				from
					(
						select
							*,
							IsLeftBound = case when LagNextN is null or LagNextN != N then 1 else 0 end,
							IsRightBound = case when LeadN is null or LeadN != N + 1 then 1 else 0 end
						from
							(
								select
									*,
									LagNextN = Lag(N + 1) over (order by N),
									LeadN = Lead(N) over (order by N)
								from
									#T
							) as Q
					) as Q
				where
					IsLeftBound = 1 or
					IsRightBound = 1
			) as Q
	) as Q
where
	IsLeftBound = 1

select
	R1.FromN,
	R2.ToN
from
	(
	select
		FromN = T1.N,
		iRange = ROW_NUMBER() over (order by T1.N)
	from
		#T as T1
		left join #T as T2 on T2.N = T1.N - 1
	where
		T2.N is null
	) as R1

	inner join
	(
	select
		ToN = T1.N,
		iRange = ROW_NUMBER() over (order by T1.N)
	from
		#T as T1
		left join #T as T2 on T2.N = T1.N + 1
	where
		T2.N is null
	) as R2
	on R1.iRange = R2.iRange


Сообщение было отредактировано: 22 авг 19, 17:51
22 авг 19, 16:05    [21955669]     Ответить | Цитировать Сообщить модератору
 Re: Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)  [new]
Focha
Member

Откуда: Москва
Сообщений: 509
может вам подойдет вот этот вариант

select min(n) as FromN, max(n) as ToN
from #T
group by  FLOOR(n/ 100000.0 )
22 авг 19, 16:37    [21955721]     Ответить | Цитировать Сообщить модератору
 Re: Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)  [new]
Focha
Member

Откуда: Москва
Сообщений: 509
select min(n) as FromN, max(n) as ToN
from #T
group by  n/ 100000
22 авг 19, 16:38    [21955723]     Ответить | Цитировать Сообщить модератору
 Re: Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7769
Андрей Усачёв,

с чего бы молниеносно, если у Вас вложены lead() да еще и внутри case.
22 авг 19, 17:18    [21955782]     Ответить | Цитировать Сообщить модератору
 Re: Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)  [new]
Андрей Усачёв
Member

Откуда: Рига, Латвия
Сообщений: 128
Focha
может вам подойдет вот этот вариант

select min(n) as FromN, max(n) as ToN
from #T
group by  FLOOR(n/ 100000.0 )


Ну здрасьте :) Данные в таблице могут быть произвольные, задача выявить последовательные интервалы. Это только для примера интервалы одинаковые и такие простые :)
22 авг 19, 17:29    [21955792]     Ответить | Цитировать Сообщить модератору
 Re: Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)  [new]
Focha
Member

Откуда: Москва
Сообщений: 509
Андрей Усачёв

Ну здрасьте :) Данные в таблице могут быть произвольные, задача выявить последовательные интервалы. Это только для примера интервалы одинаковые и такие простые :)

тогда более реальный пример и вообще где такое вы используете? мне вот интересно
22 авг 19, 17:32    [21955797]     Ответить | Цитировать Сообщить модератору
 Re: Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)  [new]
Андрей Усачёв
Member

Откуда: Рига, Латвия
Сообщений: 128
Владислав Колосов
Андрей Усачёв,

с чего бы молниеносно, если у Вас вложены lead() да еще и внутри case.


Совершенно же очевидно, что задачу можно выполнить конвейерно:
1) Самый внутренний подзапрос идёт вдоль первичного ключа и добавляет в прочитанные строки 2 поля. Lead() и Lag() на быстродействие не должны влиять, просто при чтении очередной строки у нас готова не она сама, а предыдущая.
2) Результат при этом можно не сохранять на диск, а сразу передавать во 2-й подзапрос. Он всего лишь добавляет несколько полей.
3) Их тоже надо не сохранять на диск, а передавать в 3-й подзапрос. Его задача просто проигнорировать некоторые записи.
4) 4-й подзапрос, как и 1-й, выдаёт строку дальше с задержкой на 1 строку. Сортировка для Lead() во всех подзапросах не меняется.
5) 5-й подзапрос просто игнорирует некоторые строки предыдущего запроса.

То есть первый вариант запроса можно заменить простой функцией на C++ или VB, которая последовательно читает таблицу #T и последовательно обрабатывает её 1-м, 2-м и т. д. 5-м подзапросом. Причём на 3-м и на 5-м обработка может остановиться. Если строка обработалась 5-м подзапросом, её можно выплёвывать пользователю.
22 авг 19, 17:45    [21955807]     Ответить | Цитировать Сообщить модератору
 Re: Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Андрей Усачёв,

;WITH x AS 
(
	SELECT 
		NT = NTILE(10) OVER  (order by N),
		N
	FROM #T
) 
SELECT 
	Nt,
	MIN(n),
	MAX(N)
FROM x
GROUP BY NT
22 авг 19, 17:48    [21955810]     Ответить | Цитировать Сообщить модератору
 Re: Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
а пардон, не то :)
22 авг 19, 17:49    [21955812]     Ответить | Цитировать Сообщить модератору
 Re: Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
;WITH x as 
(
	SELECT  
		N,
		Y =  ROW_NUMBER() OVER (order by N ASC) - N
	FROM  #T
) 
SELECT 	
	MIN(n),MAX(n)
FROM x 
GROUP BY y
ORDER BY 1
22 авг 19, 17:55    [21955818]     Ответить | Цитировать Сообщить модератору
 Re: Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)  [new]
Андрей Усачёв
Member

Откуда: Рига, Латвия
Сообщений: 128
Focha
Андрей Усачёв
Ну здрасьте :) Данные в таблице могут быть произвольные, задача выявить последовательные интервалы. Это только для примера интервалы одинаковые и такие простые :)

тогда более реальный пример и вообще где такое вы используете? мне вот интересно


Я работаю в компании, которая обклеивает алкоголь акцизными марками. Клиенты присылают нам марки и электронные накладные с интервалами марок, которые мы импортируем в БД. Из каждого интервала создаётся множество марок этого интервала. Параллельно этому специальные аппараты считывают с марок штрихкоды DataMatrix и PDF417 (только у марок старого образца) и сохраняют их в БД. Параллельно этому клиенты присылают нам палеты с неоклеенным алкоголем и указания, какие интервалы ранее полученных марок туда клеить. Этим занимаются другие аппараты. При этом некоторые марки могут быть отбракованы, и вместо них нужно взять марки из резерва. В каждый момент времени у марки обязательно есть номер и может быть (а может и не быть) штриход и статус (свободна, использована, отбракована) т. д.

Одна из задач: в виде интервалов (а не огромного списка номеров) показать, какие марки ещё не использованы. Или каким уже присвоены штриходы. Или какие были отбракованы и списаны.
22 авг 19, 18:16    [21955836]     Ответить | Цитировать Сообщить модератору
 Re: Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)  [new]
Андрей Усачёв
Member

Откуда: Рига, Латвия
Сообщений: 128
TaPaK
;WITH x as 
(
	SELECT  
		N,
		Y =  ROW_NUMBER() OVER (order by N ASC) - N
	FROM  #T
) 
SELECT 	
	MIN(n),MAX(n)
FROM x 
GROUP BY y
ORDER BY 1


Вот это похоже на правду. Спасибо, завтра попробую применить к своей задаче.
22 авг 19, 18:21    [21955842]     Ответить | Цитировать Сообщить модератору
 Re: Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1467
Андрей Усачёв,

если прям сильно хочется на LEAD/LAG решение построить, то поищите по форуму start_of_group, чтобы правильно его "приготовить"...
22 авг 19, 18:33    [21955857]     Ответить | Цитировать Сообщить модератору
 Re: Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1467
Щукина Анна
Андрей Усачёв,

если прям сильно хочется на LEAD/LAG решение построить, то поищите по форуму start_of_group, чтобы правильно его "приготовить"...
Пример реализации
22 авг 19, 19:02    [21955881]     Ответить | Цитировать Сообщить модератору
 Re: Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)  [new]
Focha
Member

Откуда: Москва
Сообщений: 509
Андрей Усачёв
Focha
пропущено...

тогда более реальный пример и вообще где такое вы используете? мне вот интересно


Я работаю в компании, которая обклеивает алкоголь акцизными марками. Клиенты присылают нам марки и электронные накладные с интервалами марок, которые мы импортируем в БД. Из каждого интервала создаётся множество марок этого интервала. Параллельно этому специальные аппараты считывают с марок штрихкоды DataMatrix и PDF417 (только у марок старого образца) и сохраняют их в БД. Параллельно этому клиенты присылают нам палеты с неоклеенным алкоголем и указания, какие интервалы ранее полученных марок туда клеить. Этим занимаются другие аппараты. При этом некоторые марки могут быть отбракованы, и вместо них нужно взять марки из резерва. В каждый момент времени у марки обязательно есть номер и может быть (а может и не быть) штриход и статус (свободна, использована, отбракована) т. д.

Одна из задач: в виде интервалов (а не огромного списка номеров) показать, какие марки ещё не использованы. Или каким уже присвоены штриходы. Или какие были отбракованы и списаны.


То есть вы хотите соединить интервал с номером который входит в интервал?
23 авг 19, 08:34    [21956169]     Ответить | Цитировать Сообщить модератору
 Re: Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)  [new]
Андрей Усачёв
Member

Откуда: Рига, Латвия
Сообщений: 128
Focha
То есть вы хотите соединить интервал с номером который входит в интервал?

В накладных данные приходят так:
ААБТ128001 ААБТ129000 (Украина)
07349750001 07349765000 (Россия)
Ձ0014870001 Ձ0014880285 (Армения)

А для учёта по маркам я записываю в таблицы так:
ААБТ128001
ААБТ128002
ААБТ128003
...
07349750001
07349750002
07349750003
...
Ձ0014870001
Ձ0014870002
Ձ0014870003
...
Со ссылкой на исходный интервал, конечно.
23 авг 19, 11:30    [21956323]     Ответить | Цитировать Сообщить модератору
 Re: Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)  [new]
Андрей Усачёв
Member

Откуда: Рига, Латвия
Сообщений: 128
Щукина Анна
Щукина Анна
Андрей Усачёв,

если прям сильно хочется на LEAD/LAG решение построить, то поищите по форуму start_of_group, чтобы правильно его "приготовить"...
Пример реализации


Спасибо большое, но на 1 млн. записей мои изначальные запросы выполнялись 10 и 2 секунд.
Запрос TaPaK 1 секунду.
Ваш 74 секунды.
23 авг 19, 11:38    [21956330]     Ответить | Цитировать Сообщить модератору
 Re: Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)  [new]
Focha
Member

Откуда: Москва
Сообщений: 509
Андрей Усачёв
Focha
То есть вы хотите соединить интервал с номером который входит в интервал?

В накладных данные приходят так:
ААБТ128001 ААБТ129000 (Украина)
07349750001 07349765000 (Россия)
Ձ0014870001 Ձ0014880285 (Армения)

А для учёта по маркам я записываю в таблицы так:
ААБТ128001
ААБТ128002
ААБТ128003
...
07349750001
07349750002
07349750003
...
Ձ0014870001
Ձ0014870002
Ձ0014870003
...
Со ссылкой на исходный интервал, конечно.



я почему вы не можете использовать between?

drop table #d
drop table #l

select *
into #d
from (
		select 1 as id, 'ААБТ128001' as f,'ААБТ128002' as t union all
		select 2 as id, 'ААБТ128003' as f,'ААБТ129000' as t union all
		select 3,	'07349750001'	 ,'07349765000'	    union all
		select 4,	'Ձ0014870001'	 ,'Ձ0014880285'
	 ) as d


select *
into #l
from (
		select 'ААБТ128001' as i union all
		select 'ААБТ128002' 	 union all
		select 'ААБТ128003' 	 union all
		select '07349750001' 	 union all
		select '07349750002' 	 union all
		select '07349750003' 	 union all
		select 'Ձ0014870001'	 union all
		select 'Ձ0014870002' 	 union all
		select 'Ձ0014890003'	 -- маркировка ошибка
	) as l



select *
from #l as l left join #d as d -- left что бы увидеть ошибку
on l.i between d.f and d.t 
23 авг 19, 12:16    [21956365]     Ответить | Цитировать Сообщить модератору
 Re: Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)  [new]
Андрей Усачёв
Member

Откуда: Рига, Латвия
Сообщений: 128
Focha,

Потому что на выходе нужно получить совершенно не те интервалы, из которых сгенерированы марки. Например на склад приняли интервал ААБТ128001...ААБТ129000 (одна запись в вашей таблице #d). Использовали в работе (и пометили в таблице) интервалы
ААБТ128101...ААБТ129200
ААБТ128301...ААБТ129400
Я хочу по таблице марок (1000 записей в вашей таблице #l) максимально быстро получить список свободных марок в виде 3 строчек:
ААБТ128001...ААБТ128100
ААБТ129201...ААБТ128300
ААБТ129401...ААБТ129000

Но мне давно уже ответили, проблема решена.
26 авг 19, 17:59    [21957820]     Ответить | Цитировать Сообщить модератору
 Re: Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)  [new]
Focha
Member

Откуда: Москва
Сообщений: 509
Андрей Усачёв
Focha,

Потому что на выходе нужно получить совершенно не те интервалы, из которых сгенерированы марки. Например на склад приняли интервал ААБТ128001...ААБТ129000 (одна запись в вашей таблице #d). Использовали в работе (и пометили в таблице) интервалы
ААБТ128101...ААБТ129200
ААБТ128301...ААБТ129400
Я хочу по таблице марок (1000 записей в вашей таблице #l) максимально быстро получить список свободных марок в виде 3 строчек:
ААБТ128001...ААБТ128100
ААБТ129201...ААБТ128300
ААБТ129401...ААБТ129000

Но мне давно уже ответили, проблема решена.

хорошо
27 авг 19, 08:44    [21958044]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить