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

Откуда:
Сообщений: 163
Добрый день.
Есть запрос, который работает хорошо и вариантов которого можно представить несколько, но никак не пойму, как сделать его пригодным для того, чтобы на его основе создать индексированное представление. To нельзя использовать Min/Max, то получается SelfJoin, то еще чего-то.
Подтолкните, пожалуйста, к верному решению.

Таблицы:
SKUsEvents (SKUsEventID, EventDate, SKUsEventsTypeID, ....) - таблица событий, описывает дату события, тип и другие параметры.
SKUsEventsSKUs - (SKUsEventID, SKU, Quan) указывает, какие конкретно товарные позиции были в данном событии, с указанием количества.
SKUsCapsulesTrans - (SKU1, SKU2, ResultSKU) - используется только для определенного типа событий, как раз из-за которого эта вьюха и нужна (сборка, когда точно известно, что из двух конкретных SKU получается один конкретный третий). SKU2 всего больше SKU1.

Таким образом, для всех событий с SKUsEventsTypeID = 11 в таблице SKUsEventsSKUs всегда ТОЛЬКО ДВЕ строки, из которых можно понять, что конкретно взяли для сборки. Задача - получить вьюху "готовых" продуктов.

Решение 1:
SELECT t.SKUsEventID ,
       t.EventDate ,
       t.FromWarehouseID WarehouseID,
       sct.SKUResult SKU,
	   Quan
FROM 
(
	SELECT  se.SKUsEventID, se.EventDate, se.FromWarehouseID, MIN(ses.SKU) SKU1Out, MAX(ses.SKU) SKU2Out, MAX(ses.Quan) Quan
	FROM dbo.SKUsEvents se
	INNER JOIN dbo.SKUsEventsSKUs ses ON ses.SKUsEventID = se.SKUsEventID
	WHERE se.SKUsEventsTypeID = 11
	GROUP BY se.SKUsEventID, se.EventDate, se.FromWarehouseID
) t
INNER JOIN dbo.SKUsCapsulesTrans sct ON sct.SKU1 = t.SKU1Out AND sct.SKU2=t.SKU2Out


Решение 2 (без агрегатов, но с SelfJoin):
SELECT  
	se.SKUsEventID, 
	se.EventDate, 
	se.FromWarehouseID WarehouseID, 
	sct.SKUResult SKU,
	ses.Quan
FROM dbo.SKUsEvents se
INNER JOIN dbo.SKUsEventsSKUs ses ON ses.SKUsEventID = se.SKUsEventID
INNER JOIN dbo.SKUsEventsSKUs ses2  ON ses2.SKUsEventID = ses.SKUsEventID AND ses2.SKU > ses.SKU
INNER JOIN dbo.SKUsCapsulesTrans sct ON sct.SKU1 = ses.SKU AND sct.SKU2= ses2.SKU
WHERE se.SKUsEventsTypeID = 11


Есть мысль переделать SKUsCapsulesTrans в структуру вида
(SKUSource, SKUResult, VariantID), где последняя колонка будет служить для группировки по "правильным сочетаниям", но тут тоже не уверен, что сразу все получится..
22 июл 19, 18:35    [21932028]     Ответить | Цитировать Сообщить модератору
 Re: Переписать запрос для использования в индексированном представлении  [new]
Alexey30
Member

Откуда:
Сообщений: 163
Если SKUsCapsulesTrans переделать в такой вид (SKUSource, SKUResult, VariantID), то получится такой запрос.

SELECT  
	se.SKUsEventID, 
	se.EventDate,
	se.FromWarehouseID,
	sct2.SKUResult SKU,
	ses.Quan
FROM dbo.SKUsEvents se
INNER JOIN dbo.SKUsEventsSKUs ses ON ses.SKUsEventID = se.SKUsEventID
INNER JOIN dbo.SKUsCapsulesTrans2 sct2 ON sct2.SKUSource=ses.SKU
WHERE se.SKUsEventsTypeID = 11
GROUP BY 
	se.SKUsEventID, 
	se.EventDate,
	se.FromWarehouseID,
	sct2.Variant,
	sct2.SKUResult,
	ses.Quan 
HAVING COUNT_BIG(*) = 2


Но HAVING тоже нельзя(((
22 июл 19, 18:50    [21932041]     Ответить | Цитировать Сообщить модератору
 Re: Переписать запрос для использования в индексированном представлении  [new]
Alexey30
Member

Откуда:
Сообщений: 163
Еще один вариант - это разнести на два события с одинаковой датой: первое будет ввод (с двумя SKU), второе - вывод - с одним.
Это тогда решит все проблемы с индексированной вьюхой, но будет избыточность и как-то не очень красиво это.
22 июл 19, 19:04    [21932046]     Ответить | Цитировать Сообщить модератору
 Re: Переписать запрос для использования в индексированном представлении  [new]
Владислав Колосов
Member

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

без наборов данных непонятно - что на входе и что надо получить на выходе.
23 июл 19, 11:14    [21932452]     Ответить | Цитировать Сообщить модератору
 Re: Переписать запрос для использования в индексированном представлении  [new]
Alexey30
Member

Откуда:
Сообщений: 163
Добрый день.
Примерно такой набор тестовых данных, если опустить все лишнее.


CREATE TABLE SKUs (SKU INT, Title VARCHAR(50))
CREATE TABLE SKUsEventsTypes (SKUsEventsTypeID int, Title VARCHAR(50))
CREATE TABLE SKUsEvents (SKUsEventID int, EventDate DATETIME, SKUsEventsTypeID INT, WarehouseID int)
CREATE TABLE SKUsEventsSKUs (SKUsEventID int, SKU int, Quan int)
CREATE TABLE SKUsCapsulesTrans (SKU1 int, SKU2 int, SKUResult INT)

INSERT  dbo.SKUsEventsTypes
        ( SKUsEventsTypeID, Title )
VALUES  ( 1, 'Закупка'), ( 2, 'Перемещение'), ( 11, 'Сборка двух товаров в один')

INSERT dbo.SKUs
        ( SKU, Title )
VALUES  ( 1, 'Товар 1'), (2, 'Товар 2'), (3, 'Товар 3'), (3, 'Товар 4'), (3, 'Товар 5 (состоит из 1 и 2)'), (3, 'Товар 6 (состоит из 1 и 3)')

INSERT dbo.SKUsCapsulesTrans
        ( SKU1, SKU2, SKUResult )
VALUES  (1,2,5), (1,3,6) --указываем, из каких товаров какие можно собрать (всегда два на входе, один на выходе)

INSERT SKUsEvents (SKUsEventID, EventDate, SKUsEventsTypeID, WarehouseID)
VALUES 
	(1, '01.01.2000', 1, 1), --закупка
	(2, '02.01.2000', 11, 1) --сборка

INSERT dbo.SKUsEventsSKUs
        ( SKUsEventID, SKU, Quan )
VALUES  (1, 1, 10), (1, 2, 10), (1, 3, 10), (1, 4, 10) --закупаем товары 1-4 по 10 шт

INSERT dbo.SKUsEventsSKUs
        ( SKUsEventID, SKU, Quan )
VALUES  (2, 1, 4), (2, 2, 4) --собираем Товар 5 из товаров 1 и 2
 
 --далее нужно получить список товаров, которые получились после сборки, используем любой из селектов выше.

 SELECT t.SKUsEventID ,
       t.EventDate ,
       WarehouseID,
       sct.SKUResult SKU,
	   Quan
FROM 
(
	SELECT  se.SKUsEventID, se.EventDate, se.WarehouseID, MIN(ses.SKU) SKU1Out, MAX(ses.SKU) SKU2Out, MAX(ses.Quan) Quan
	FROM dbo.SKUsEvents se
	INNER JOIN dbo.SKUsEventsSKUs ses ON ses.SKUsEventID = se.SKUsEventID
	WHERE se.SKUsEventsTypeID = 11
	GROUP BY se.SKUsEventID, se.EventDate, se.WarehouseID
) t
INNER JOIN dbo.SKUsCapsulesTrans sct ON sct.SKU1 = t.SKU1Out AND sct.SKU2=t.SKU2Out


На выходе получаем
SKUsEventID	EventDate	WarehouseID	SKU	Quan
2	2000-01-02 	1	5	4
23 июл 19, 11:54    [21932513]     Ответить | Цитировать Сообщить модератору
 Re: Переписать запрос для использования в индексированном представлении  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
если так уж хочется имитировать индексированное представление,
сами храните и поддерживайте в отдельной таблице свои MIN(SKU), MAX(SKU).
сервер это не делает по простой причине:
если в вашей таблицы проайпэйтит строку, то пересчитывать COUNT_BIG(*) ему не надо:
счетчик просто не изменится
(при вставке строки увеличится, при удалении уменьшится)
а как, простите, пересчитать MIN и MAX, не сканируя всю таблицу?
может, вы как раз своим апдэйтом истребили предыдущий MIN/MAX?
23 июл 19, 12:13    [21932532]     Ответить | Цитировать Сообщить модератору
 Re: Переписать запрос для использования в индексированном представлении  [new]
Владислав Колосов
Member

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

от сортировка Вы никуда не уйдёте. Для быстрой выборки вам надо создать витрину, которая будет обновлять min/max при изменениях исходных таблиц. Всегда жертвуешь или скоростью или объёмом хранения.
23 июл 19, 13:45    [21932651]     Ответить | Цитировать Сообщить модератору
 Re: Переписать запрос для использования в индексированном представлении  [new]
uaggster
Member

Откуда:
Сообщений: 827
Alexey30, Просто разделите ваш запрос на 2 представления.
В одном, индексированном, храните исходные данные, а второе, с группировками, мин и макс - сделайте обычным.
Ну, т.е. если весь запрос нельзя сделать индексированным представлением, сделайте максимально возможную заготовку, и обычное представление, его использующее.
23 июл 19, 14:40    [21932718]     Ответить | Цитировать Сообщить модератору
 Re: Переписать запрос для использования в индексированном представлении  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
uaggster
Alexey30, Просто разделите ваш запрос на 2 представления.
В одном, индексированном, храните исходные данные, а второе, с группировками, мин и макс - сделайте обычным.
Ну, т.е. если весь запрос нельзя сделать индексированным представлением, сделайте максимально возможную заготовку, и обычное представление, его использующее.

смысл сего действа в чем?
индексированное представление не пересчитывает агрегаты типа count()
при обновлении таблиц.
а если их все равно придется пересчитывать в вашем "просто представлении" (скан + сорт),
то зачем весь этот огород?
23 июл 19, 15:19    [21932781]     Ответить | Цитировать Сообщить модератору
 Re: Переписать запрос для использования в индексированном представлении  [new]
uaggster
Member

Откуда:
Сообщений: 827
Yasha123
uaggster
Alexey30, Просто разделите ваш запрос на 2 представления.
В одном, индексированном, храните исходные данные, а второе, с группировками, мин и макс - сделайте обычным.
Ну, т.е. если весь запрос нельзя сделать индексированным представлением, сделайте максимально возможную заготовку, и обычное представление, его использующее.

смысл сего действа в чем?
индексированное представление не пересчитывает агрегаты типа count()
при обновлении таблиц.
а если их все равно придется пересчитывать в вашем "просто представлении" (скан + сорт),
то зачем весь этот огород?

Ну сузить же набор данных, с которым работает запрос, который потом считает min, max и count. И построить по этому набору кастомные индексы, если их напрямую нельзя по каким-то причинам построить по исходным таблицам.
Для чего еще indexed view нужны?
Нет, я понимаю, что может быть, в данном конкретном случае этот совет сродни "мыши, станьте ежиками".
Но, вообще - нормально это работает. Вырезать из звездообразного соединения те поля, которые нужны, отсечь, например, началом года, и построить индексы по полям, которые в родных таблицах в качестве индексируемых - не фигурируют.
Чем плохо то?
Даже по вертикально суженной выборке - агрегаты будут быстрее считаться, в отличие от исходных "широких таблиц".
И потом, columnstore можно и поверх индексированного представления создать
http://www.nikoport.com/2016/10/10/columnstore-indexes-part-87-indexed-views/
(не пробовал, кстати).
23 июл 19, 16:38    [21932900]     Ответить | Цитировать Сообщить модератору
 Re: Переписать запрос для использования в индексированном представлении  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
Alexey30
Если SKUsCapsulesTrans переделать в такой вид (SKUSource, SKUResult, VariantID)
Можете так извратиться:
create MyIndexedView
with schemabinding
as
SELECT  
	se.SKUsEventID, 
	se.EventDate,
	se.FromWarehouseID,
    sct2.Variant,
	sct2.SKUResult,
	ses.Quan,
    COUNT_BIG(*) as cnt
FROM dbo.SKUsEvents se
INNER JOIN dbo.SKUsEventsSKUs ses ON ses.SKUsEventID = se.SKUsEventID
INNER JOIN dbo.SKUsCapsulesTrans2 sct2 ON sct2.SKUSource=ses.SKU
WHERE se.SKUsEventsTypeID = 11
GROUP BY 
	se.SKUsEventID, 
	se.EventDate,
	se.FromWarehouseID,
	sct2.Variant,
	sct2.SKUResult,
	ses.Quan ;
go

create unique clustered index CUIX_MyIndexedView on MyIndexedView (cnt, SKUsEventID, EventDate, FromWarehouseID, Variant, SKUResult, Quan);
go

create MyView
as
select
 SKUsEventID, 
 EventDate,
 FromWarehouseID,
 SKUResult SKU,
 Quan
from
 MyIndexedView with (noexpand)
where
 cnt = 2;
go
23 июл 19, 16:41    [21932906]     Ответить | Цитировать Сообщить модератору
 Re: Переписать запрос для использования в индексированном представлении  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
uaggster
Даже по вертикально суженной выборке - агрегаты будут быстрее считаться, в отличие от исходных "широких таблиц".
И потом, columnstore можно и поверх индексированного представления создать

так columnstore и без вью можно создать.
и считать агрегаты быстрее.
---
у него же нет там никакой кучи соединений,
у него считаются 2 агрегата, по которым потом и фильтруется остальное.
я и говорю: если эти мин и макс можно как-то посчитать отдельно,
то самому и надо считать.
например, тот же мин может не меняться вовсе,
а макс -- только увеличиваться при вставке,
а поле и вовсе необновляемое.

вот тогда хранить их в таблице в 1 строку и самому обновлять
23 июл 19, 16:47    [21932917]     Ответить | Цитировать Сообщить модератору
 Re: Переписать запрос для использования в индексированном представлении  [new]
uaggster
Member

Откуда:
Сообщений: 827
Yasha123
uaggster
Даже по вертикально суженной выборке - агрегаты будут быстрее считаться, в отличие от исходных "широких таблиц".
И потом, columnstore можно и поверх индексированного представления создать

так columnstore и без вью можно создать.
и считать агрегаты быстрее.

Флейма для: Дык весь смысл вырезать из звездочки заготовку, и уже по ней построить колумнстор. Эдакое эрзац - хранилище.
23 июл 19, 17:04    [21932933]     Ответить | Цитировать Сообщить модератору
 Re: Переписать запрос для использования в индексированном представлении  [new]
Владислав Колосов
Member

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

это называется "витрина".
23 июл 19, 18:21    [21932980]     Ответить | Цитировать Сообщить модератору
 Re: Переписать запрос для использования в индексированном представлении  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
uaggster,
а кск вы колумнсторы в 2014 Standard делаете?
23 июл 19, 18:45    [21933011]     Ответить | Цитировать Сообщить модератору
 Re: Переписать запрос для использования в индексированном представлении  [new]
Alexey30
Member

Откуда:
Сообщений: 163
Огромное спасибо всем за уделенное время и внимание.
Видимо, остановлюсь на варианте от invm.

invm, отдельное спасибо :)
И вопрос. Стоит ли включать столбец Quan в индекс, если по нему НЕ будет производиться выборка в будущем, он используется только внутри агрегата для подсчета остатков на момент времени?
23 июл 19, 18:53    [21933020]     Ответить | Цитировать Сообщить модератору
 Re: Переписать запрос для использования в индексированном представлении  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
Alexey30
Стоит ли включать столбец Quan в индекс, если по нему НЕ будет производиться выборка в будущем, он используется только внутри агрегата для подсчета остатков на момент времени?
В данном случае в ключ индекса включены все столбцы из предложения group by для обеспечения его уникальности.
23 июл 19, 20:05    [21933078]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить