Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Оптимизация рабаты с BIG DATA  [new]
Shanga
Member

Откуда:
Сообщений: 10

Всем привет!

У меня такая проблема, есть Зоны (числовые коды) в количестве 60 штук

CREATE TABLE [dbo].[Zones]
(
[ID] INT IDENTITY(1,1) NOT NULL,
val INT,
...
)

Необходимо сотавить каты Зон по 10 штук, так, чтобы зоны не повторялись внутри строки и чтобы каждая строка была уникальной.

Т.е.

SELECT
zone01.val,
zone02.val,
zone03.val,
zone04.val,
zone05.val,
zone06.val,
zone07.val,
zone08.val,
zone09.val,
zone10.val
FROM
[PremierShipping].[dbo].[Zones] AS zone01
RIGHT JOIN [dbo].[Zones] AS zone02 ON zone02.val > zone01.val
RIGHT JOIN [dbo].[Zones] AS zone03 ON zone03.val > zone02.val
RIGHT JOIN [dbo].[Zones] AS zone04 ON zone04.val > zone03.val
RIGHT JOIN [dbo].[Zones] AS zone05 ON zone05.val > zone04.val
RIGHT JOIN [dbo].[Zones] AS zone06 ON zone06.val > zone05.val
RIGHT JOIN [dbo].[Zones] AS zone07 ON zone07.val > zone06.val
RIGHT JOIN [dbo].[Zones] AS zone08 ON zone08.val > zone07.val
RIGHT JOIN [dbo].[Zones] AS zone09 ON zone09.val > zone08.val
RIGHT JOIN [dbo].[Zones] AS zone10 ON zone10.val > zone09.val
WHERE
ISNULL(zone01.val , 0) <> 0
AND ISNULL(zone02.val , 0) <> 0
AND ISNULL(zone03.val , 0) <> 0
AND ISNULL(zone04.val , 0) <> 0
AND ISNULL(zone05.val , 0) <> 0
AND ISNULL(zone06.val , 0) <> 0
AND ISNULL(zone07.val , 0) <> 0
AND ISNULL(zone08.val , 0) <> 0
AND ISNULL(zone09.val , 0) <> 0
AND ISNULL(zone10.val , 0) <> 0

Это не сложная задача была бы, если бы не одно но, количество зон... (т.е. 60 шт)

По моим расчетам это 75 394 027 566 возможных результатов

С примерным расчетным временем 872 сутокб что не приемлемо.

Так же, количество зон будет расти со временем.


Помогите оптимизировать запрос плиз

13 ноя 18, 22:01    [21733614]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация рабаты с BIG DATA  [new]
Shanga
Member

Откуда:
Сообщений: 10
я имел ввиду 8,72 суток
13 ноя 18, 22:06    [21733616]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация рабаты с BIG DATA  [new]
court
Member

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

1
LEFT JOIN должен быть ... или знак в ON поменяй
2
достаточно одного условия ISNULL(zone01.val , 0) <> 0 (это если на ЛЕФТ поменяешь)
13 ноя 18, 22:55    [21733638]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация рабаты с BIG DATA  [new]
Shanga
Member

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

Пробовал, результат практически идентичен... (((
13 ноя 18, 23:00    [21733640]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация рабаты с BIG DATA  [new]
court
Member

Откуда:
Сообщений: 1956
даа, и почему джоин по val ?
джойни по ID (это ж ПК, надеюсь ?)
Или val не уникальный ?
13 ноя 18, 23:00    [21733641]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация рабаты с BIG DATA  [new]
PizzaPizza
Member

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

у вас "зоны" видимо цифровые, раз вы их сравниваете по >, но почему именно знак больше непонятно.

Этот вот кусок
WHERE
	ISNULL(zone01.val , 0) <> 0


у вас val может быть null? Зачем вы null превращаете в 0 и потом сравниваете с нулем когда есть конструкция is not null? Это лишние операции конвертации, которые вам совсем не нужны.
13 ноя 18, 23:19    [21733647]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация рабаты с BIG DATA  [new]
Shanga
Member

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

Т.к. выборка каждой зоны из одного и того же массива
то надо данные различать (они не должны повторяться)

Для этого знак ">", можно знак "<" иначе данные могут дублироваться

Пример:

[dbo].[Zones] 1276, 5684, 3254, 4323, 1589, 412, 893, 8543... и так 60шт

при SELECT > будет

412, 893, 1276, 1589, 3254, 4323, 5684, ...

893, 1276, 1589, 3254, 4323, 5684, 8543...
13 ноя 18, 23:32    [21733652]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация рабаты с BIG DATA  [new]
Shanga
Member

Откуда:
Сообщений: 10
PizzaPizza,
В случае, когда будут задействованы последние значения [dbo].[Zones]
ос тавшиеся зоны будут NULL

Пример:

[dbo].[Zones] ...1276, 5684, 3254, 4323, 1589, 412, 893, 8543


zone01.val = 3254
zone02.val = 4323
zone03.val = 5684
zone04.val = 8543 (данные закончились)
zone05.val = NULL
zone06.val = NULL
zone07.val = NULL
zone08.val = NULL
zone09.val = NULL
zone10.val = NULL
13 ноя 18, 23:36    [21733656]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация рабаты с BIG DATA  [new]
Shanga
Member

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

ISNULL очень быстрая операция, практически сравнимая с IS NOT NULL
13 ноя 18, 23:38    [21733659]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация рабаты с BIG DATA  [new]
Shanga
Member

Откуда:
Сообщений: 10
Изменил, не помогает... (((

SELECT
zone01.val,
zone02.val,
zone03.val,
zone04.val,
zone05.val,
zone06.val,
zone07.val,
zone08.val,
zone09.val,
zone10.val
FROM
[dbo].[Zones] AS zone10
LEFT JOIN [dbo].[Zones] AS zone09 ON zone09.ID < zone10.ID
LEFT JOIN [dbo].[Zones] AS zone08 ON zone08.ID < zone09.ID
LEFT JOIN [dbo].[Zones] AS zone07 ON zone07.ID < zone08.ID
LEFT JOIN [dbo].[Zones] AS zone06 ON zone06.ID < zone07.ID
LEFT JOIN [dbo].[Zones] AS zone05 ON zone05.ID < zone06.ID
LEFT JOIN [dbo].[Zones] AS zone04 ON zone04.ID < zone05.ID
LEFT JOIN [dbo].[Zones] AS zone03 ON zone03.ID < zone04.ID
LEFT JOIN [dbo].[Zones] AS zone02 ON zone02.ID < zone03.ID
LEFT JOIN [dbo].[Zones] AS zone01 ON zone01.ID < zone02.ID
WHERE
zone01.val IS NOT NULL
14 ноя 18, 00:01    [21733676]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация рабаты с BIG DATA  [new]
PizzaPizza
Member

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

Наверное я не очень понимаю задачу.
Судя по числу 75 394 027 566 вам нужны сочетания (все комбинации по 10 элементов из множества 60 элементов).
При соединении с правилом Bn>An что случится с вариантами, когда An<Bn? Сдается мне вы получите разное количество элементов.

AB
12765684 3254 4323 1589
56848543
32545684 4323 8543
43235684 8543
15895684 3254 4323 8543
4121276 5684 3254 4323 1589 412 893 8543
8931276 5684 3254 4323 1589 893 8543
8543
14 ноя 18, 00:06    [21733680]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация рабаты с BIG DATA  [new]
Shanga
Member

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

Условие ">" или "<" покрывает все возможные комбинации (без дубликатов)
Порядок не важен, важны лишь уникальные карты Зон

Пример:

1 2 3 4 5

1 2 3
1 2 4
1 2 5
1 3 4
1 3 5
1 4 5
2 3 4
2 3 5
2 4 5
3 4 5

идентичны (в моем случае)

5 4 3 2 1

5 4 3
5 4 2
5 4 1
5 3 2
5 3 1
5 2 1
4 3 2
4 2 1
3 2 1
14 ноя 18, 05:06    [21733718]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация рабаты с BIG DATA  [new]
PizzaPizza
Member

Откуда:
Сообщений: 309
Я наверное к вечеру не очень понимаю.

У вас данные отсортированные? Иначе у меня получается вот такое

1 2 3 5 4

123
125
124
135
134
15
235
234
245
35
14 ноя 18, 05:26    [21733719]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация рабаты с BIG DATA  [new]
Shanga
Member

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

Сортировка не проблема можно с ней, а можно и без нее по ID
главное уникальность комбинации

134
15

именно для таких случаев у меня проверка на NULLL
14 ноя 18, 05:43    [21733723]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация рабаты с BIG DATA  [new]
Oleg_SQL
Member

Откуда: Khabarovsk
Сообщений: 19
DECLARE @T TABLE (ID INT PRIMARY KEY)

INSERT @T VALUES (1), (2), (3), (4)
        
SELECT * 
  FROM @T AS Column1
       RIGHT JOIN @T AS Column2 ON Column1.Id <> Column2.Id
       RIGHT JOIN @T AS Column3 ON Column1.Id <> Column3.Id AND Column2.Id <> Column3.Id
       RIGHT JOIN @T AS Column4 ON Column1.Id <> Column4.Id AND Column2.Id <> Column4.Id  AND Column3.Id <> Column4.Id
ORDER BY 1, 2, 3, 4



Здесь только 4, а не 10. Но суть одна..
14 ноя 18, 10:18    [21733831]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация рабаты с BIG DATA  [new]
Charles Weyland
Member

Откуда: Feorina "Fury" 161
Сообщений: 4331
Во-первых, пока ты писал название поста тебя почему-то глючило несколько раз, в конце вообще на английский переключился. Суть в том, что BIG DATA тут вообще ни при чём. BIG DATA - это кластеры, хадуп и пр.

Во-вторых, я вообще не понимаю, зачем ты используешь Right join, позволяющий работать с NULL-полями и тут же пишешь "но только не NULL", причём череж зопу - путём конвертации в ноль.
Убери все проверки на NULL и замени все RIGHT JOIN на INNER JOIN. Он будет подразумевать обязательный не-null.

В-третьих,
В целом, у меня ощущение, что тут БД вообще не пахнет. Надо писать какой-нибудь внешний модуль, который нагенерирует тебе столько значений. Тут C++ и распараллеливание нужно.
Вот, у меня были сложные запросы - они возвращали миллионы записей (не миллиарды), при этом в результате оптимизации выяснялось, что где-то можно индексы вставить, от нескольких сотен тысяч записей отказаться, что-то редко изменяемое предварительно агрегировать по ночам либо триггерами в отдельную таблицу или столбцы и т.д. Т.е. там был простор для оптимизаций.

У тебя здесь банально - нагенерировать все сочетания без повторений 10 элементов из 60. SQL для этого не стоит использовать.
14 ноя 18, 10:18    [21733832]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация рабаты с BIG DATA  [new]
iap
Member

Откуда: Москва
Сообщений: 46953
PizzaPizza
у вас val может быть null? Зачем вы null превращаете в 0 и потом сравниваете с нулем когда есть конструкция is not null? Это лишние операции конвертации, которые вам совсем не нужны.
Условие
ISNULL(zone01.val,0)<>0
эквивалентно следующему
zone01.val<>0
Причём это условие превращает запрос в SARGable
14 ноя 18, 10:19    [21733835]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация рабаты с BIG DATA  [new]
Charles Weyland
Member

Откуда: Feorina "Fury" 161
Сообщений: 4331
iap
PizzaPizza
у вас val может быть null? Зачем вы null превращаете в 0 и потом сравниваете с нулем когда есть конструкция is not null? Это лишние операции конвертации, которые вам совсем не нужны.
Условие
ISNULL(zone01.val,0)<>0
эквивалентно следующему
zone01.val<>0
Причём это условие превращает запрос в SARGable

бред какой-то.

1. Как раз то выражение, которое ты идентифицировал словом условие, делает его не оптимизируемым ("не саргблеа") банально из-за использования функции.
2. Первое и второе выражения не являются эквивалентными, поскольку
ISNULL(zone01.val,0)<>0

эквивалентно
zone01.val !=0 and zone01.val is not null


3. Почему нельзя вместо
ISNULL(zone01.val , 0) <> 0

написать
zone01.val is not null

чтобы как раз оно стало оптимизируемым?
Я уверен, что val не содержит скалярных нулей.

4. Нафига писать сначала right join, который разрешает использование null, а затем тут же запрещать использование null какими-то условиями? (кажется, я уже писал про это, но ты проигнорировал).
14 ноя 18, 10:36    [21733849]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация рабаты с BIG DATA  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Charles Weyland,

бред какой-то

zone01.val !=0 and zone01.val is not null
и это не равно zone01.val !=0? как же так
14 ноя 18, 10:43    [21733859]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация рабаты с BIG DATA  [new]
iap
Member

Откуда: Москва
Сообщений: 46953
Charles Weyland
бред какой-то
Полегче!
Charles Weyland
Почему нельзя вместо
ISNULL(zone01.val , 0) <> 0
написать
zone01.val is not null
Потому что результат этих выражений разный

в WHERE различаются результаты булевых выражений TRUE и NOT TRUE.
Если zone01.val IS NULL, то zone01.val<>0 возвращает UNKNOWN, то есть NOT TRUE.
Таким образом, NULL фильтруется и без всякого ISNULL().
14 ноя 18, 10:55    [21733877]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация рабаты с BIG DATA  [new]
PizzaPizza
Member

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

А можно пояснить?

Имеет ли преимущества выражение
ISNULL(zone07.val , 0) <> 0

в случае, когда не подразумевается 0 как возможное знание (насколько я понял у тс это используется именно для фильтрации null от outer joinов) ?
А в случае, если возможны и null и 0 это выражение лучше чем два @ <> 0 and @ is not null?
14 ноя 18, 11:08    [21733905]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация рабаты с BIG DATA  [new]
Cristiano_Rivaldo
Member

Откуда:
Сообщений: 323
Возможно недопонимаю постановку , но запрос ТС неправильный (взять на 4 ) :
Запрос
WITH Zones (id,VAL)
AS 
(
	SELECT 1,1
	UNION ALL
	SELECT 12,2
	UNION ALL
	SELECT 7,3
	UNION ALL
	SELECT 5,4
)
SELECT	zone01.val,
		zone02.val,
		zone03.val,
		zone04.val
FROM Zones zone01
	RIGHT JOIN Zones zone02 ON zone02.val > zone01.val
	RIGHT JOIN Zones zone03 ON zone03.val > zone02.val
	RIGHT JOIN Zones zone04 ON zone04.val > zone03.val

вернет меньше комбинаций,чем
WITH Zones (Id ,val)
AS 
(
	SELECT 1, 1 UNION ALL SELECT 12,2 UNION ALL SELECT 7,3 UNION ALL SELECT 5,4
)
,
cte (id,val,c_path)
AS
(
	SELECT DISTINCT  tt.id, tt.val ,CAST(tt.val AS VARCHAR(MAX))
	FROM Zones tt 
	
	UNION ALL
	
	SELECT	zn.id,
			zn.val,
			c.c_path + ',' + CAST(zn.val AS VARCHAR(MAX))
	FROM cte c INNER JOIN  Zones zn ON zn.val > c.val 
)

SELECT val,c_path 
FROM cte

Возможно в последнем запросе нужно написать
SELECT val,c_path 
FROM cte
WHERE c_path LIKE '1.%'

чтобы результаты были идентичными
14 ноя 18, 11:10    [21733908]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация рабаты с BIG DATA  [new]
iap
Member

Откуда: Москва
Сообщений: 46953
PizzaPizza
iap,

А можно пояснить?

Имеет ли преимущества выражение
ISNULL(zone07.val , 0) <> 0


в случае, когда не подразумевается 0 как возможное знание (насколько я понял у тс это используется именно для фильтрации null от outer joinов) ?
А в случае, если возможны и null и 0 это выражение лучше чем два @ <> 0 and @ is not null?
Однако, если не равно 0, то автоматически IS NOT NULL.
Отдельное IS NOT NULL - лишнее.
Что касается именно функции ISNULL(), то сервер во многих случаях умеет использовать индексы и в случае её применения.
Но полный перечень таких случаев я дать не могу.
Общее правило - по возможности не накладывать ограничение на функцию от поля,
а стараться накладывать его на само поле.
14 ноя 18, 11:16    [21733920]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация рабаты с BIG DATA  [new]
iap
Member

Откуда: Москва
Сообщений: 46953
iap
Что касается именно функции ISNULL(), то сервер во многих случаях умеет использовать индексы и в случае её применения.
Но полный перечень таких случаев я дать не могу.
Вот, например, если в таблице прописано, что поле NOT NULL, то сервер реагирует на ISNULL(поле,...) так, как будто никакого ISNULL() нет.
14 ноя 18, 11:20    [21733925]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация рабаты с BIG DATA  [new]
Shanga
Member

Откуда:
Сообщений: 10
Charles Weyland,

Да, я уже думал о другой платформе для генерирования, но уж слишком большой результат 75 394 027 566 и он будет расти т.к. количество зон, будет расти.

Пока не нашел ничего подходящего... (((
14 ноя 18, 17:42    [21734463]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить