Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!) / 20 сообщений из 20, страница 1 из 1
22.08.2019, 16:05
    #39852844
Андрей Усачёв
Андрей Усачёв
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)
Создаю таблицу из 1 млн целых чисел, представляющих собой 10 отдельных интервалов. Хочу написать SELECT, который вернёт эти 10 интервалов в виде 10 строк.

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

 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
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

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.08.2019, 16:37
    #39852871
Focha
Focha
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)
может вам подойдет вот этот вариант

Код: sql
1.
2.
3.
select min(n) as FromN, max(n) as ToN
from #T
group by  FLOOR(n/ 100000.0 )
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.08.2019, 16:38
    #39852873
Focha
Focha
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)
Код: sql
1.
2.
3.
select min(n) as FromN, max(n) as ToN
from #T
group by  n/ 100000
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.08.2019, 17:18
    #39852901
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)
Андрей Усачёв,

с чего бы молниеносно, если у Вас вложены lead() да еще и внутри case.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.08.2019, 17:29
    #39852909
Андрей Усачёв
Андрей Усачёв
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)
Fochaможет вам подойдет вот этот вариант

Код: sql
1.
2.
3.
select min(n) as FromN, max(n) as ToN
from #T
group by  FLOOR(n/ 100000.0 )



Ну здрасьте :) Данные в таблице могут быть произвольные, задача выявить последовательные интервалы. Это только для примера интервалы одинаковые и такие простые :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.08.2019, 17:32
    #39852913
Focha
Focha
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)
Андрей Усачёв
Ну здрасьте :) Данные в таблице могут быть произвольные, задача выявить последовательные интервалы. Это только для примера интервалы одинаковые и такие простые :)
тогда более реальный пример и вообще где такое вы используете? мне вот интересно
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.08.2019, 17:45
    #39852923
Андрей Усачёв
Андрей Усачёв
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)
Владислав КолосовАндрей Усачёв,

с чего бы молниеносно, если у Вас вложены 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-м подзапросом, её можно выплёвывать пользователю.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.08.2019, 17:48
    #39852925
TaPaK
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)
Андрей Усачёв,

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
;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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.08.2019, 17:49
    #39852927
TaPaK
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)
а пардон, не то :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.08.2019, 17:55
    #39852928
TaPaK
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
;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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.08.2019, 18:16
    #39852934
Андрей Усачёв
Андрей Усачёв
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)
FochaАндрей УсачёвНу здрасьте :) Данные в таблице могут быть произвольные, задача выявить последовательные интервалы. Это только для примера интервалы одинаковые и такие простые :)
тогда более реальный пример и вообще где такое вы используете? мне вот интересно

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

Одна из задач: в виде интервалов (а не огромного списка номеров) показать, какие марки ещё не использованы. Или каким уже присвоены штриходы. Или какие были отбракованы и списаны.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.08.2019, 18:21
    #39852937
Андрей Усачёв
Андрей Усачёв
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)
TaPaK
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
;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



Вот это похоже на правду. Спасибо, завтра попробую применить к своей задаче.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.08.2019, 18:33
    #39852944
Щукина Анна
Щукина Анна
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)
Андрей Усачёв,

если прям сильно хочется на LEAD/LAG решение построить, то поищите по форуму start_of_group, чтобы правильно его "приготовить"...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.08.2019, 19:02
    #39852949
Щукина Анна
Щукина Анна
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)
Щукина АннаАндрей Усачёв,

если прям сильно хочется на LEAD/LAG решение построить, то поищите по форуму start_of_group, чтобы правильно его "приготовить"... Пример реализации
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.08.2019, 08:34
    #39853081
Focha
Focha
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)
Андрей УсачёвFochaпропущено...

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

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

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

То есть вы хотите соединить интервал с номером который входит в интервал?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.08.2019, 11:30
    #39853178
Андрей Усачёв
Андрей Усачёв
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)
FochaТо есть вы хотите соединить интервал с номером который входит в интервал?
В накладных данные приходят так:
ААБТ128001 ААБТ129000 (Украина)
07349750001 07349765000 (Россия)
Ձ0014870001 Ձ0014880285 (Армения)

А для учёта по маркам я записываю в таблицы так:
ААБТ128001
ААБТ128002
ААБТ128003
...
07349750001
07349750002
07349750003
...
Ձ0014870001
Ձ0014870002
Ձ0014870003
...
Со ссылкой на исходный интервал, конечно.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.08.2019, 11:38
    #39853181
Андрей Усачёв
Андрей Усачёв
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)
Щукина АннаЩукина АннаАндрей Усачёв,

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

Спасибо большое, но на 1 млн. записей мои изначальные запросы выполнялись 10 и 2 секунд.
Запрос TaPaK 1 секунду.
Ваш 74 секунды.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.08.2019, 12:16
    #39853200
Focha
Focha
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)
Андрей УсачёвFochaТо есть вы хотите соединить интервал с номером который входит в интервал?
В накладных данные приходят так:
ААБТ128001 ААБТ129000 (Украина)
07349750001 07349765000 (Россия)
Ձ0014870001 Ձ0014880285 (Армения)

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


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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.08.2019, 17:59
    #39853842
Андрей Усачёв
Андрей Усачёв
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)
Focha,

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

Но мне давно уже ответили, проблема решена.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.08.2019, 08:44
    #39853997
Focha
Focha
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)
Андрей УсачёвFocha,

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

Но мне давно уже ответили, проблема решена.
хорошо
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!) / 20 сообщений из 20, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]