ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)
20 сообщений из 20, страница 1 из 1
Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)
    #39852844
Андрей Усачёв
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Андрей Усачёв
Участник
Создаю таблицу из 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:05
| Ответить | Цитировать | Написать  
Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)
    #39852871
Фотография Focha
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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, 16:37
| Ответить | Цитировать | Написать  
Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)
    #39852873
Фотография Focha
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Focha
Участник
Код: sql
1.
2.
3.
select min(n) as FromN, max(n) as ToN
from #T
group by  n/ 100000
...
Рейтинг: 0 / 0
22.08.2019, 16:38
| Ответить | Цитировать | Написать  
Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)
    #39852901
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Андрей Усачёв,

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

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

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

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

Одна из задач: в виде интервалов (а не огромного списка номеров) показать, какие марки ещё не использованы. Или каким уже присвоены штриходы. Или какие были отбракованы и списаны.
...
Рейтинг: 0 / 0
22.08.2019, 18:16
| Ответить | Цитировать | Написать  
Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)
    #39852937
Андрей Усачёв
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Андрей Усачёв
Участник
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:21
| Ответить | Цитировать | Написать  
Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)
    #39852944
Фотография Щукина Анна
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Щукина Анна
Участник
Андрей Усачёв,

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

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

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

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

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

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

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

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

Спасибо большое, но на 1 млн. записей мои изначальные запросы выполнялись 10 и 2 секунд.
Запрос TaPaK 1 секунду.
Ваш 74 секунды.
...
Рейтинг: 0 / 0
23.08.2019, 11:38
| Ответить | Цитировать | Написать  
Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)
    #39853200
Фотография Focha
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Focha
Участник
Андрей Усачёв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
23.08.2019, 12:16
| Ответить | Цитировать | Написать  
Почему так медленно работаетконвейер из Lag() и Lead() с одинаковыми условиями (по ключу!)
    #39853842
Андрей Усачёв
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Андрей Усачёв
Участник
Focha,

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

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

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

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


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