Comments 179
View: В параметризованные представления во FROM можно передавать только константы
SELECT shipmentDetail.id, b.quantity
FROM shipmentDetail
JOIN shipment ON shipmentDetail.shipment = shipment.id
JOIN balance(shipment.date) b ON b.stock = shipment.stock AND b.product = shipmentDetail.product
WHERE shipmentDetail.quantity = 5Это можно реализовать на MS SQL с использованием CROSS APPLY (нет блока ON придется функцию с 3 параметрами делать), на PostgreSQL LATERAL в помощь.
Дальше читать не стал, может быть большая часть и имеет место быть.
SELECT FROM balance('01.01.2019')Получается что это уже не виртуальная таблица (представление), а именно функция будет.
CREATE FUNCTION balance (
@date DATE
, @stockid INT
, @productid INT
)
RETURNS TABLE
AS
RETURN
SELECT stock, product, SUM(quantity) AS quantity
FROM
(SELECT receipt.stock, product, quantity
FROM receiptDetail
JOIN receipt ON receiptDetail.receipt = receipt.id
WHERE receipt.date < @date and (receipt.stock = @stockid or @stockid is null) and (product = @productid or @productid is null)
UNION ALL
SELECT shipment.stock, product, -quantity
FROM shipmentDetail
JOIN shipment ON shipmentDetail.shipment = shipment.id
WHERE receipt.date < @date and (shiopment.stock = @stockid or @stockid is null) and (product = @productid or @productid is null)
) details
GROUP BY stock, productВ SQL Server эти OR'ы не помешают использованию индекса, он поймёт что от него хотят.
Вообще, меня удивил выбор СУБД — SQL Server 2019 — это даже не RTM, его ещё пилят во всю.
Но тут конечно нюанс не начнет ли он CROSS APPLY безусловно nested loop'ом выполнять.
То есть если вы сделаете:
SELECT FROM Product pr CROSS APPLY balance('01.01.2019',pr.id,NULL).
Не начнет ли он бежать по Product и рассчитывать подзапрос для каждого товара, сумеет ли он из product=@productid hash probe сделать. Надо будет потестировать.
Но в любом случае вы же понимаете, что это жесткий хак и чтобы его нормально использовать, вам придется все представления такими хаками делать. Плюс возможно с WITH RECOMPILE, чтобы MS SQL не дурел с планами.
Вообще, меня удивил выбор СУБД — SQL Server 2019 — это даже не RTM, его ещё пилят во всю.
Ну это чтобы не было комментариев: «в последней версии это скорее всего исправили» :) Просто у Oracle 19c у MS SQL тоже хотелось 19.
Про остальное сверху вроде ответили.
Выше есть одна здравая мысль — надо протестировать. Пожалуйста, тестируйте.
Остальное (как, например, функцию с WITH RECOMPILE) я комментировать там не буду, чтобы остаться в рамках приличий.
Выше есть одна здравая мысль — надо протестировать. Пожалуйста, тестируйте.
Протестировал и изменил статью. Сможете прокомментировать? Вообще как я понял CROSS APPLY это практически всегда Nested Loop. То есть план с Hash построить SQL сервера не могут, и на таких запросах сразу кладут всю базу. Или я что-то не так делаю.
Покажите, пожалуйста, запрос в комментариях — с cross apply и без.
Там внутри есть UPD выделен жирным там и запрос и план MS SQL есть в частности (хотя исходный запрос чуть выше UPD).
Чуть ниже под спойлером запрос с ручным JPPD. Вот его план (выполняется 12 секунд без параллелизма).
952139 1 |--Compute Scalar(DEFINE:([Expr1021]=CASE WHEN [Expr1059]=(0) THEN NULL ELSE [Expr1060] END)) 952139 1 |--Hash Match(Aggregate, HASH:([test].[dbo].[shipmentdetail].[id]) DEFINE:([Expr1059]=COUNT_BIG([Union1016]), [Expr1060]=SUM([Union1016]))) 10948565 1 |--Hash Match(Inner Join, HASH:([test].[dbo].[shipment].[stock], [test].[dbo].[shipmentdetail].[product])=([Union1013], [Union1014]), RESIDUAL:([Union1013]=[test].[dbo].[shipment].[stock] AND [Union1014]=[test].[dbo].[shipmentdetail].[product] AND [Union1015]<[test].[dbo].[shipment].[date])) 999730 1 |--Hash Match(Inner Join, HASH:([test].[dbo].[shipment].[id])=([test].[dbo].[shipmentdetail].[shipment])) 100001 1 | |--Hash Match(Inner Join, HASH:([test].[dbo].[shipment].[date])=([test].[dbo].[shipment].[date]), RESIDUAL:([test].[dbo].[shipment].[date]=[test].[dbo].[shipment].[date])) 300 1 | | |--Hash Match(Aggregate, HASH:([test].[dbo].[shipment].[date]), RESIDUAL:([test].[dbo].[shipment].[date] = [test].[dbo].[shipment].[date])) 999730 1 | | | |--Hash Match(Inner Join, HASH:([test].[dbo].[shipment].[id])=([test].[dbo].[shipmentdetail].[shipment])) 100001 1 | | | |--Clustered Index Scan(OBJECT:([test].[dbo].[shipment].[PK__shipment__3213E83F4B7B4D07])) 999730 1 | | | |--Clustered Index Scan(OBJECT:([test].[dbo].[shipmentdetail].[PK__shipment__3213E83F996CFFF4]), WHERE:([test].[dbo].[shipmentdetail].[quantity]=(5.000))) 100001 1 | | |--Clustered Index Scan(OBJECT:([test].[dbo].[shipment].[PK__shipment__3213E83F4B7B4D07])) 999730 1 | |--Clustered Index Scan(OBJECT:([test].[dbo].[shipmentdetail].[PK__shipment__3213E83F996CFFF4]), WHERE:([test].[dbo].[shipmentdetail].[quantity]=(5.000))) 11000002 1 |--Concatenation 1000001 1 |--Hash Match(Inner Join, HASH:([test].[dbo].[receipt].[id])=([test].[dbo].[receiptdetail].[receipt])) 10001 1 | |--Clustered Index Scan(OBJECT:([test].[dbo].[receipt].[PK__receipt__3213E83FE2F580DF])) 1000001 1 | |--Clustered Index Scan(OBJECT:([test].[dbo].[receiptdetail].[PK__receiptd__3213E83FE8063B8C])) 10000001 1 |--Hash Match(Inner Join, HASH:([test].[dbo].[shipment].[id])=([test].[dbo].[shipmentdetail].[shipment])) 100001 1 |--Clustered Index Scan(OBJECT:([test].[dbo].[shipment].[PK__shipment__3213E83F4B7B4D07])) 10000001 1 |--Compute Scalar(DEFINE:([Expr1012]= -[test].[dbo].[shipmentdetail].[quantity])) 10000001 1 |--Clustered Index Scan(OBJECT:([test].[dbo].[shipmentdetail].[PK__shipment__3213E83F996CFFF4]))
SELECT shipmentDetail.id, b.quantity
FROM shipmentDetail
JOIN shipment ON shipmentDetail.shipment = shipment.id
JOIN (SELECT stock, product, dates.date, SUM(quantity) AS quantity
FROM
(SELECT receipt.stock, product, receipt.date, quantity
FROM receiptDetail
JOIN receipt ON receiptDetail.receipt = receipt.id
UNION ALL
SELECT shipment.stock, product, shipment.date, -quantity
FROM shipmentDetail
JOIN shipment ON shipmentDetail.shipment = shipment.id
) details
JOIN
(SELECT shipment.date
FROM shipmentDetail
JOIN shipment ON shipmentDetail.shipment = shipment.id
WHERE shipmentDetail.quantity = 5
GROUP BY shipment.date
) dates ON details.date < dates.date
GROUP BY stock, product, dates.date
) b ON b.stock = shipment.stock AND b.product = shipmentDetail.product AND b.date = shipment.date
WHERE shipmentDetail.quantity = 5 SELECT shipmentDetail.id, b.quantity
FROM shipmentDetail
JOIN shipment ON shipmentDetail.shipment = shipment.id
cross apply
(
SELECT SUM(quantity) AS quantity
FROM
(SELECT date, stock, product, quantity
FROM receiptDetail
inner JOIN receipt ON receiptDetail.receipt = receipt.id
WHERE (receipt.stock = shipment.stock) and (product = shipmentDetail.product)
UNION ALL
SELECT date, stock, product, -quantity
FROM shipmentDetail sd
inner JOIN shipment s ON sd.shipment = s.id
WHERE (s.stock = shipment.stock) and (sd.product = shipmentDetail.product)
) details
join
(SELECT ss.date
FROM shipmentDetail ssd
JOIN shipment ss ON ssd.shipment = ss.id
WHERE ss.date = shipment.date
GROUP BY ss.date
) dates ON details.date < dates.date
group by stock, product, dates.date
) b
WHERE shipmentDetail.quantity = 5
SELECT shipmentDetail.id, b.quantity
into #t1
FROM shipmentDetail
JOIN shipment ON shipmentDetail.shipment = shipment.id
JOIN (SELECT stock, product, dates.date, SUM(quantity) AS quantity
FROM
(SELECT receipt.stock, product, receipt.date, quantity
FROM receiptDetail
JOIN receipt ON receiptDetail.receipt = receipt.id
UNION ALL
SELECT shipment.stock, product, shipment.date, -quantity
FROM shipmentDetail
JOIN shipment ON shipmentDetail.shipment = shipment.id
) details
JOIN
(SELECT shipment.date
FROM shipmentDetail
JOIN shipment ON shipmentDetail.shipment = shipment.id
WHERE shipmentDetail.quantity = 5
GROUP BY shipment.date
) dates ON details.date < dates.date
GROUP BY stock, product, dates.date
) b ON b.stock = shipment.stock AND b.product = shipmentDetail.product AND b.date = shipment.date
WHERE shipmentDetail.quantity = 5
SELECT shipmentDetail.id, b.quantity
into #t2
FROM shipmentDetail
JOIN shipment ON shipmentDetail.shipment = shipment.id
cross apply
(
SELECT SUM(quantity) AS quantity
FROM
(SELECT date, stock, product, quantity
FROM receiptDetail
inner JOIN receipt ON receiptDetail.receipt = receipt.id
WHERE (receipt.stock = shipment.stock) and (product = shipmentDetail.product)
UNION ALL
SELECT date, stock, product, -quantity
FROM shipmentDetail sd
inner JOIN shipment s ON sd.shipment = s.id
WHERE (s.stock = shipment.stock) and (sd.product = shipmentDetail.product)
) details
join
(SELECT ss.date
FROM shipmentDetail ssd
JOIN shipment ss ON ssd.shipment = ss.id
WHERE ss.date = shipment.date
GROUP BY ss.date
) dates ON details.date < dates.date
group by stock, product, dates.date
) b
WHERE shipmentDetail.quantity = 5
select *
from #t1 t1
full join #t2 t2 on t1.id = t2.id
where t1.quantity <> t2.quantity or t1.id is null or t2.id is null or t1.quantity is null or t2.quantity is null
drop table #t1
drop table #t2«хак» с таблицей дат крутой, признаю.
Теоретически, с inline UDF результат должен быть тот же самый, но прав на создание UDF у гостевой учётки нет :)
«хак» с таблицей дат крутой, признаю.
Не, я не спорю что с этим «хаком» и CROSS APPLY он выполнится нормально. Вопрос догадается ли SQL сервер этот хак сделать самостоятельно, если ему дать ему базовый запрос (с balance, я такой view уже создал на тестовой базе). Как я проверил нет, можете тоже проверить, если хотите (balance там ровно как с статье). А UDF с этим хаком не имеет смысла, так как там внутри по сути контест внешнего запроса.
Вообще этот хак, это не более чем «JPPD в общем случае» контурно описанный в статье.
APPLY — это практически гарантировано nested loop join (вариант с hash join SQL сервера даже не рассматривают)
Вроде ваши слова в статье, нет?
И почему такая UDF не будет иметь смысла? Вы сможете передавать ей нужные параметры и использовать её как «представление», если нужно. Как это сделать писал выше.
(SELECT ss.date
FROM shipmentDetail ssd
JOIN shipment ss ON ssd.shipment = ss.id
WHERE ss.date = shipment.date
GROUP BY ss.date
)А в другом запросе в контексте могут совсем другие таблицы быть (например product). Какую UDF для остатков на дату соответственно создавать?
Вы написали, что при CROSS APPLY «вариант с hash join SQL сервера даже не рассматривают». Я показал, что это не так на вашем же примере.
Если выполнить:
SET STATISTICS TIME, IO ON;
SELECT shipmentDetail.id, b.quantity
FROM shipmentDetail
JOIN shipment ON shipmentDetail.shipment = shipment.id
JOIN (SELECT stock, product, dates.date, SUM(quantity) AS quantity
FROM
(SELECT receipt.stock, product, receipt.date, quantity
FROM receiptDetail
JOIN receipt ON receiptDetail.receipt = receipt.id
UNION ALL
SELECT shipment.stock, product, shipment.date, -quantity
FROM shipmentDetail
JOIN shipment ON shipmentDetail.shipment = shipment.id
) details
JOIN
(SELECT shipment.date
FROM shipmentDetail
JOIN shipment ON shipmentDetail.shipment = shipment.id
WHERE shipmentDetail.quantity = 5
GROUP BY shipment.date
) dates ON details.date < dates.date
GROUP BY stock, product, dates.date
) b ON b.stock = shipment.stock AND b.product = shipmentDetail.product AND b.date = shipment.date
WHERE shipmentDetail.quantity = 5
SELECT shipmentDetail.id, b.quantity
FROM shipmentDetail
JOIN shipment ON shipmentDetail.shipment = shipment.id
cross apply
(
SELECT SUM(quantity) AS quantity
FROM
(SELECT date, stock, product, quantity
FROM receiptDetail
inner JOIN receipt ON receiptDetail.receipt = receipt.id
WHERE (receipt.stock = shipment.stock) and (product = shipmentDetail.product)
UNION ALL
SELECT date, stock, product, -quantity
FROM shipmentDetail sd
inner JOIN shipment s ON sd.shipment = s.id
WHERE (s.stock = shipment.stock) and (sd.product = shipmentDetail.product)
) details
join
(SELECT ss.date
FROM shipmentDetail ssd
JOIN shipment ss ON ssd.shipment = ss.id
WHERE ss.date = shipment.date
GROUP BY ss.date
) dates ON details.date < dates.date
group by stock, product, dates.date
) b
WHERE shipmentDetail.quantity = 5
SET STATISTICS TIME, IO OFF;увидите что-то вроде:
(затронуто строк: 952139)
Table 'shipmentdetail'. Scan count 9, logical reads 183601, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'shipment'. Scan count 9, logical reads 4125, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'receiptdetail'. Scan count 3, logical reads 5517, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'receipt'. Scan count 3, logical reads 136, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
(затронута одна строка)
SQL Server Execution Times:
CPU time = 11750 ms, elapsed time = 14761 ms.
(затронуто строк: 952139)
Table 'shipmentdetail'. Scan count 9, logical reads 144521, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'shipment'. Scan count 9, logical reads 4125, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'receiptdetail'. Scan count 3, logical reads 5517, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'receipt'. Scan count 3, logical reads 136, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
(затронута одна строка)
SQL Server Execution Times:
CPU time = 11240 ms, elapsed time = 17012 ms.
и можете оценить эффективность, она примерно одинакова (примерно одинаковый CPU time, логических чтений меньше у CROSS APPLY, на elapsed time особого внимания обращать внимания не стоит, тут сеть очень может повлиять).
Сейчас вы придумываете ситуации и хотите чтобы я их опроверг. А мне уже дальше не интересно, извините.
А речь в разделе шла именно о случае, когда есть параметр и внутри нет ни одного предиката эквивалентности с ним, а только >, < (когда эквивалентность есть, можно обойтись обычными VIEW и JOIN). И у SQL сервер два варианта либо nested loop, либо JPPD в общем случае («хак»), который он делать не умеет.
Шикарная статья, спасибо! Но я бы всё-таки различал три вещи: абстрактная реляционная модель, язык запросов к ней, реализация всего этого в современных СУБД. Из названия статьи хочется заподозрить, что речь пойдет о втором пункте, но на самом деле речь идёт о всех трёх. Как-то бы переименовать статью, быть может (но не знаю как).
На практике же дело приходится иметь с ними всеми вместе взятыми + на самом деле обертками еще из логики представлений, ORM. То есть и так общей картины у большинства людей нет, а если еще раздробить информацию, то совсем какой-то информационный шум получится.
И ещё важная проблема — не все базы хорошо переводятся на «кластерный» способ работы, как в NoSQL.
Кстати это модный тренд сейчас — Distributed SQL с ACID. Google Spanner, Yugabyte'ы всякие. Но они к сожалению даже window функций вроде не поддерживают, не то что рекурсивных CTE. Но надеюсь это пока.
Есть еще, например, Exasol, у них довольно сильная поддержка стандарта. Не уверен, правда, насчет рекурсивных CTE.
Что касается Exasol… Тут граница проходит не по ACID, а по типу нагрузки. ACID в Exasol идеальный, более того, уровень изоляции транзакций serializable. Но база адаптирована под OLAP профиль (редкие большие записи и много сложных вычиток), а не под OLTP, когда идёт высоко-конкуррентное чтение-изменение данных в одной таблице.
О да, с "проталкиванием" параметров во VIEW буквально на днях столкнулся в полный рост.
Есть, скажем, таблица с историей статусов, и на ее основе создана VIEW, показывающая последний статус для каждой сущности. И если запрашивать данные по ID, план получается хороший, с index scan, запрос быстрый:
select v.last_status from status_view v where v.id = 123Но если это же условие применяется в join-е, и даже если запись, к которой происходит join, будет строго одна в силу других условий, планировщик не парится, и делает full table scan, по всем миллионам записей:
select v.status
from entity e
inner join status_view v on v.id = e.id
where e.number = 'xxx'Получается, что VIEW призвана абстрагировать разработчика от особенностей хранения данных, но, как и многие другие абстракции, норовит "протечь", заставляя разработчика идти на разнообразные ухищрения.
VIEW простая, в ней "всего лишь" фильтрация по результату оконной функции.
Что-то вроде
select * from (
select id, status, date, rank() over (partition by id order by date desc) r
from raw_statuses
) t
where r = 1Как же не хватает агрегатных функций типа FIRST и LAST. Все способы обойти это выглядят костылями в той или иной степени...
Кстати в Posgres эти агрегатные функции не очень сложно создаются:
CREATE OR REPLACE FUNCTION last_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE AS $$
SELECT $2;
$$;
DROP AGGREGATE IF EXISTS last(anyelement) CASCADE;
CREATE AGGREGATE last (
sfunc = last_agg,
basetype = anyelement,
stype = anyelement
);
Но это потому что в PostgreSQL в custom aggregate функциях ORDER clause поддерживают.
В MS SQL бодаться с созданием таких функцией куда больше. Там и ORDER'а нет и их на C# надо писать.
Что там в Oracle если честно не помню.
Вообще, там есть FIRST_VALUE/LAST_VALUE — они делают что-то другое?
Это оконные функции, а речь про агрегатные. Они немного по-разному работают и не всегда взаимозамеяемы.
SELECT LAST(id ORDER BY date,id) FROM shipment
В MSSQL ЕМНИП WITHIN GROUP есть но у конкретных агрегирующих функций. У custom aggregate нет. Во всяком случае лет 5 назад вроде так было.
Про FIRST_VALUE и LAST_VALUE уже ответили. Это оконные функции.
«абстрагировать разработчика от особенностей хранения данных» — sql-разработчик должен понимать, что под капотом view — сканы или поиски по индексу.
В вашем случае вместо тяжелой оконной конструкции почему бы не применить cross apply или табличную функцию —
select o.ObjectID, x.LastStatus
from MyObjects o
cross apply ( select top 1 s.Status as LastStatus
from MyObjectStatuses s
where s.ObjectID = o.ObjectID
order by s.Date desc -- любое нужное условие
) x
В оракле такие штуки можно решить хинтами. Чтобы сработал пуш предиката, нужно, чтобы джоин был методом nested loops, и это можно сделать примерно так:
select /*+ leading(t) use_nl(v) */
v.*
from table1 t,
view1 v
where t.some_id = 123
and t.another_id = v.another_id;То, что идет после слова select – это хинт. Он говорит ораклу, начинать джоины с таблицы t, а при присоединении представления v, использовать nested loops.
Таким же образом можно поступать, если мы из ведущей таблицы берем поле, которое в ведомой является ключом секционирования. Чтобы в таком случае не перебирать все партиции, нужен опять же nested loops. Такое не-перебирание партиций называется partition pruning.
И у них вроде как работают «лучшие в мире инженеры».
Из свежего…
Две структуры. В одной 3 параметра VARCHAR2, в другой два.
Использование не той Array структур (Oracle) в параметре — выдает четкое предупреждение, что не те параметры.
Использование не той Array структур внутри другой структуры как параметра приводит к неопределенному поведение хоста Oracle (стек или память портится на ХОСТОВОЙ части) и любимой ошибке ORA-00600: internal error code или вообще Listener не отвечает Jdbc.
Из за одной опечатки в коде пол дня потрачено на поиск. Причем на на разных версиях Oracle проявлялось по разному.
Чем, кстати, не дырка для атаки на хостовую часть...
Ну и еще до кучи всякого в реализации JDBC встречалось… Лень находить артефакты для комментария. И код JDBC при трассировке оставляет странное впечатление (возможно он еще с java 1.4… но стиль написания забавен)
Собственно, я когда тестировал матпредставления, если честно так и не смог их заставить работать с UPDATE при FAST REFRESH ON COMMIT. При INSERT обновляются, а при UPDATE нет. Но я не стал уже это в статье писать, уверен что где-то есть секретное знание, как это нужно делать. Просто мне не удалось его найти.
Ну и вообще если погуглить, про проблемы с производительностью FAST REFRESH матпредставлений, там такие веселые костыли выплывают, но я с Oracle в какой-то момент уже перестал чему-то удивляться.
- Зачем делать материализованное представление, чтоб потом упереться в его ограничения. Материализованное представление — самый негибкий способ поддержки синхронности исходных и вычисленных данных, используется в простейших случаях. В приближенных к жизни сценариях синхронизация делается отдельным кодом, не обязательно синхронно с изменениями исходной таблицы.
- Какие-то странные выводы про неудобства табличных функций в cross apply — вполне можно и параметры функции использовать, и далее во where накладывать условия — inline-функция раскроется в запросе, и все условия сработают.
- Проблема N+1 в данном случае высосана из пальца. Вопрос привычки и опыта.
- Триггеры уровня оператора в postgreSQL вполне себе поддерживают получение старых и измененных данных — иначе, действительно, зачем они нужны, этакий чемодан без ручки.
Зачем делать материализованное представление, чтоб потом упереться в его ограничения. Материализованное представление — самый негибкий способ поддержки синхронности исходных и вычисленных данных, используется в простейших случаях. В приближенных к жизни сценариях синхронизация делается отдельным кодом, не обязательно синхронно с изменениями исходной таблицы.
Так, а как поддерживать остатки в таком случае? Ну и разработчик может изначально не знать, нужно ли материализовывать данное представление или нет.
Какие-то странные выводы про неудобства табличных функций в cross apply — вполне можно и параметры функции использовать, и далее во where накладывать условия — inline-функция раскроется в запросе, и все условия сработают.
Это как? Можно пример?
Проблема N+1 в данном случае высосана из пальца. Вопрос привычки и опыта.
Так а что делать если у вас написана хранимка, и ее надо для 1000 записей вызвать?
Триггеры уровня оператора в postgreSQL вполне себе поддерживают получение старых и измененных данных — иначе, действительно, зачем они нужны, этакий чемодан без ручки.
Тут вопрос есть на stackoverflow.
NEW
Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is null in statement-level triggers and for DELETE operations.
OLD
Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is null in statement-level triggers and for INSERT operations.
Есть transition tables, но это как я понял не совсем то. Во всяком случае примеры там какие то совсем странные.
Так а что делать если у вас написана хранимка, и ее надо для 1000 записей вызвать?Я тоже не понял, в чем заключается проблема N+1.
1. Если у вас есть хранимка, и ее надо один раз выполнить для 1000 записей, то берете и выполняете.
2. Если у вас была хранимка, которая выполнялась для одной записи, а теперь нужно будет регулярно выполнять ее для 1000 записей, значит, у вас изменились требования и код надо дописывать/переписывать. А раз все равно надо переписывать, то переписываем хранимку так, чтобы ее можно было выполнить для любого числа записей от 1 до 1000 (в идеале — до плюс бесконечности).
А мысль была в том, что SQL сам мог бы компилировать циклы в запросы.Понятно, что если есть какие-то сложные рекурсивные зависимости: когда команда изменяет данные, которые читает следующая команда в процедуре, тогда да такую, штуку тяжело оптимизировать. Но очень большое количество простых процедур (без таких зависимостей) можно и нужно оптимизировать. Проверено на практике.
А полиморфизм с наследованием это очень даже декларативные концепции. Я бы даже сказал более декларативные чем SQL, если декларативностью вообще меряться можно. И я в статье написал зачем:
возможности эффективно декомпозировать задачи, а также не наращивать технический долг по мере роста сложности системы
Они не входят в стандарт, про что вам сразу и сказали. И более того они не декларативны по своей сути.
А полиморфизм с наследованием это очень даже декларативные концепции.
В каком месте и каким боком они к SQL подходят и как они туда ложатся? Единственное применение наследование которое я использовал в том же PostgreSQL это для секционирования таблиц, для другого оно как-то плохо применимо. Приведите хоть один вариант использования когда это прям дает какой-то внятный профит. Я вот не припомню ни одного применения где полиморфизм и наследование давали какой-то внятный эффект. Более того чем дальше тем больше в нем разочаровываются и откатываются к функцинальщине.
Они не входят в стандарт, про что вам сразу и сказали. И более того они не декларативны по своей сути.
Стандарт SQL вообще очень забавная штука. Они UPDATE и агрегирующие функции стандартизировать не могут. Ну я нигде и не говорил про стандарт, это чисто практическая статья, а хранимки и триггеры при разработке на SQL используются очень часто.
В каком месте и каким боком они к SQL подходят и как они туда ложатся? Единственное применение наследование которое я использовал в том же PostgreSQL это для секционирования таблиц, для другого оно как-то плохо применимо. Приведите хоть один вариант использования когда это прям дает какой-то внятный профит. Я вот не припомню ни одного применения где полиморфизм и наследование давали какой-то внятный эффект. Более того чем дальше тем больше в нем разочаровываются и откатываются к функцинальщине.
Функциональщина перпендикулярна наследованию и полиморфизму. А вообще возьмите крупный проект на C++ или Java и посчитайте там количество extends /: и abstract / virtual.
функции стандартизировать не могут.
update и агрегирующие функции хотя бы ложатся в канву.
А вообще возьмите крупный проект на C++ или Java и посчитайте там количество extends /: и abstract / virtual.
Угу весьма здорово привести ООП языки как довод. Давайте уж тогда ORM еще припомним и что уши N+1 оттуда торчат.
А вообще возьмите крупный проект на C++ или Java и посчитайте там количество extends /: и abstract / virtual.
Зачем полиморфизм в Java мы и так знаем. А вот в SQL он зачем? Какую задачу в SQL вы хотите решить при помощи полиморфизма, которая без полиморфизма решается плохо или не решается, например, нормализацией?
А можно все сделать нормально классами, наследованием и полиморфизмом. Вот пример кусочка дерева классов например:
А какой ещё, простите, функционал вы хотели бы добавить к объекту "Запись Таблицы SKU"? Других-то объектов в большинстве RDBMS нет, потому что именно тип "Запись Таблицы<T>" лучше всего решает основную задачу базы данных — хранения данных/фактов о <T>.
Полиморфизм самих данных в духе
class Location {}
class Airport extends Location {}решается нормализацией данных — имеем отдельно таблицы Location и Airport, и в Airport также имеем foreign key к Location. Просто, оптимизируемо, и — внимание! — логически верно, так как
NOT(IS_A(Airport, Location)) && HAS_A(Airport, Location)Или вы бы хотели хранить как значение произвольные объекты? Ну так и этого у DBMS тоже есть, и даже с полиморфизмом объектов (и иногда с неявным приведением типов, как в Oracle).
UPD: Пригляделся к вашей картинке. Судя по названиям, неправильно вы домен готовите. Если заменить наследование композицией, иерархия будет заметно проще. Всем этим документам всего-лишь нужно обладать некой общей частью, а выглядит будто вы заставляете их ещё и вести себя одинаково только по факту наличия этой общей части, но не по факту действительного наличия общего поведения. Сужу исключительно по наличию нескольких типов с одинаковым названием.
решается нормализацией данных — имеем отдельно таблицы Location и Airport, и в Airport также имеем foreign key к Location. Просто, оптимизируемо, и — внимание! — логически верно, так как
Так Airport это не Location. А вот Capital это City. А Роза — цветок.
Я не совсем понял, вы считаете что наследование не нужно и всегда можно обойтись композицией? Очень смелое утверждение.
То почему произвольными объектами плохо я писал в статье в разделе про наследование и полиморфизм. И как бы я хотел тоже там писал. И уже несколько раз тут отвечал, почему наследование таблиц не подходит.
Сужу исключительно по наличию нескольких типов с одинаковым названием.
В lsFusion просто множественное наследование есть. Это одинаковые классы. Просто так граф в виде дерева выглядит.
И всё же, так и нет ответа на исходный вопрос: какие задачи вы хотите решить полиморфизмом в RDBMS? Какие части реляционной модели полиморфизм улучшит?
А вот Capital это City. А Роза — цветок.
А в чём функциональное различие capital и city, кроме единственного флага, который, к тому же, меняет своё значение во времени? Какие свойства есть у столицы? Почему, вы считаете, недостаточно сделать
type Capital {CityId, ...},
или даже вообще по-рабоче-крестьянски
type City { bool IsCapital, ... },
а вот обязательно надо иметь
type Capital extends City?
Что улучшится при втором подходе? Как при наследовании быть если столицу перенесли? Будем удалять город? Или всё-таки только статус предадим от одного города другому?
То же самое с розами. При моделировании растений вообще намного проще иметь Traits, потому что на практически любое свойство роз, кроме ботанического определения (которое есть натуральный ключ), найдётся вид-другой роз, у которых это свойство отсутствует.
вы считаете что наследование не нужно и всегда можно обойтись композицией
С точки зрения (под)системы хранения данных — да, определённо. В общем для ООП — зависит от доменной модели.
Я лично без наследования и полиморфизма не могу. Но есть к примеру 1С: УТ, где весь код привет 70-е и ничего люди покупают и дописывают.
Я так-то вообще всего лишь пытаюсь выяснить, для чего вам в SQL (или PL/SQL?) понадобился полиморфизм, и полиморфизм чего именно вы вообще имеете в виду.
А вместо этого вижу то столицы, то розы, то код из семидесятых.
в SQL
Чтобы работало быстро
понадобился полиморфизм, и полиморфизм чего именно вы вообще имеете в виду
Полиморфизм вычислений (в SQL читай представлений).
Чтобы если мне нужно было добавить новый вид цветка, я бы добавил класс Роза, унаследовал от класса Цветок, во все необходимые абстрактные представления добавил реализации (подзапросы) для этого класса. И все запросы с цветами начали бы учитывать эти подзапросы.
Полиморфизм… представлений
Ну смотрите:
with raw_data (id, type, quantity) as (
select
shipment_id, 'Shipment', shipped_quantity
from shipment
union all
select
receipt_id, 'Receipt', received_quantity
from receipt
)
select * from raw_dataВ данном случае подзапрос определяет подтип строки таблицы, выглядящий примерно как
type raw_data_type is (
id number, type varchar, quantity number
)И обе части UNION'a приводятся к этому типу, в итоге основное тело запроса работает с супертипом, а не с каждым подтипом отдельно. Вот это — вполне полиморфизм, с точки зрения хранилища фактов, коим SQL является, но вы же вроде бы чего-то другого добиваетесь, не так ли? Вот и объяснили бы, чего. Эти вот притянутые примеры, которыми вы продолжаете кидаться, картину не проясняют, в них слишком мало анализа с вашей стороны недостатков вашего подхода и преимуществ других подходов. Например, в чём конкретно преимущество подхода, когда вы храните все розы отдельно от остальных цветов, так что вам приходится объединять подклассы вручную через представления?
… во все необходимые абстрактные представления добавил реализации (подзапросы) для этого класса
Так а где тут полиморфизм-то? Так вы в этих представлениях по сути наплодите в итоге кучу тех самых if-ов (подзапросов, если точнее, но не суть), о которых так нелестно отзываетесь в соседней ветке комментариев. И после каждого нового класса будете плодить ещё больше "if-ов".
Тогда как при решении композицией у вас для вашей розы будет самодостаточная запись каталога цветов и рядом вспомогательная таблица атрибутов именно роз, и никаких абстрактных представлений переписывать будет не нужно. Хотите цветы — читаете из цветов. Хотите розы — читаете из роз, объединяете с цветами (и вот для этого-то какое-нибудь представление прямо идеально подходит).
В SQL не хватает наследования (Правда в определенном виде он есть в PostgreSQL)
А так таблицы-это классы, а их строки-экземпляры.
Сложный тип реализуется через внешние ключи.
Не хватает возможности встраивания кусков кода в виде например функций для множественного использования с разными входными параметрами в разных местах вызовов. Вроде в MS SQL Server 2019 обещают это сделать.
А вообще с выходом 2019 скуля что-то затянули
Под встраиванием кусков кода вы имеете в виду хранение функций в виде данных, и в каждой строке таблицы иметь возможность хранить свою функцию?
Какую задачу вы бы хотели так решить?
Правда в определенном виде он есть в PostgreSQL
В Postgres больше смахивает на синтаксический сахар над теми же внешними ключами. По-прежнему не могу понять, чем эти протекающие на раз-два абстракции лучше простого и честного разделения на таблицы с честными же связями.
под встраивание я имею в виду использование одного кода в разных конструкциях, т е те же функции, но которые работают нормально в плане оптимизации, а не как сейчас (хранимки не подойдут-они так легко не встраиваются)
под встраивание я имею в виду использование одного кода в разных конструкциях
Не понял. Разных — это каких? Предикат, проекция, CASE, другая процедура — это разные конструкции? Или вы имеете в виду разные конструкции в форме разграничения statement/expression? Если же вы под встраиванием пытаетесь выразить inline-включение тела функции в тело главного запроса, то этого вы никаким наследованием не добьётесь. Рискну предположить, что это вообще силами SQL невозможно для общего случая, потому что предполагает слишком сильное переписывание и запроса, и самой функции. Ну не сможет никто, кроме вас, чисто преобразовать
function get_customer_name(id number) as begin
return select customer_name from customer
where customer_id = id;
end;
select
purchase_date,
get_customer_name(customer_id) customer_name
from purchasesВ
select
purchase_date,
customer_name
from purchases
left join customer using(customer_id)хранимки не подойдут
Мне кажется, вы не совсем верно понимаете чем ограничены оптимизации функций. Это зависит вовсе не от принадлежности "хранимке", а от детерминированности самой функции (в терминах ФП — от чистоты). Но при этом сам вызов особо не оптимизировать. Если детерминирована — то вызов можно мемоизировать, только и всего.
посмотрите пример 42.7 про триггер уровня оператора. Да, transition tables, все работает.
Так, а как поддерживать остатки в таком случае?
Например, так. Нужен журнал операций, отдельный код асинхронно «бежит» по журналу и по операциям «материализует» остатки в отдельной таблице.
Изменение реестра остатков делается либо триггером на таблице проводок, либо в API функциях, которые выполняют запись в таблицу проводок
Я так понимаю вы 1Скую схему продвигаете (я знаю как она работает). Вы же в курсе, что они редактирование по сути удалением \ добавлением предполагают делать (что я собственно и писал в статье)?
В таблице проводок каждая запись — это: артикул, количество-дельта изменения остатка, ссылка на документ-основание.
Суммирование всех записей в таблице проводок с group by по артикулу приводит к получению актуальных остатков. Но делать это никогда не приходится, так как эта информация хранится в реестре остатков — таблице, которая в общем по структуре напоминает матвью такого запроса.
Сам факт изменения остатков (в плюс или минус) фиксируется добавлением записи в таблицу проводок. Как и откуда это инициируется — более широкая тема. Ну допустим, в терминах 1С при «проведении документа-основания». Но ничто не мешает делать это индивидуально по каждой строке документа. В зависимости от правил системы можно запретить на таблице любые dml кроме insert: откат проводок только через сторнирование. А можно и разрешить удаление и редактирование. В любом случае, в триггере уровня записи на таблице проводок мы знаем без лишних запросов, какой update выполнить на реестр, чтобы поддержать там актуальные остатки.
Ну и естественно, в эти таблицы можно добавить внешний ключ на склады, типы остатков или другие разрезы, кому что необходимо в учете
Ну и плюс вы возможно создали лишнюю материализацию (таблицу).
Я привел рабочую схему. И да, я сделал материализацию, которая НА ПРАКТИКЕ решает проблемы с производительностью, которые вызывает ваша очень теориетически правильная вьюха остатков.
Не вижу никакой проблемы с обовлением таблицы проводок. Блокиурется запись реестра, выполняется добаление/изменение/удаление проводки, снимается блокировка с записи реестра.
проблемы с производительностью, которые вызывает ваша очень теориетически правильная вьюха остатков.
Как раз теоретически правильная никаких проблем с производительностью не вызывает.
Не вижу никакой проблемы с обовлением таблицы проводок. Блокиурется запись реестра, выполняется добаление/изменение/удаление проводки, снимается блокировка с записи реестра.
Это только если количество меняется к примеру. А если к примеру у вас внутреннее перемещение и в нем меняется склад прихода, то вам нужно руками к примеру найти и удалить все приходные и расходные проводки старого склада прихода и добавить все приходные и расходные проводки нового склада прихода. И как вы вообще собираетесь определять ситуации изменения и что именно там изменилось (количество или склад)? Триггерами?
Плюс если усложнить логику остатков (скажем двойным учетом) или логику каких-нибудь задолженностей взять (со всякими отсрочками). Там знаете сколько таких сценариев будет (например когда тип договора меняется)?
Именно. В триггере из :old берем старую строку реестра по ключам артикул/склад, вычитаем старое кол-во. Из :new берем новую строку и увеличиваем количество.
Но вообще такая операция, как изменение склада в «выполненном» документе (документе в финальном статусе, в терминах 1С можно сказать в проведенном) — недопустима. Так как на разных складах разные мат.ответственные лица. И это должно делаться в два этапа: откат документа из финального статуса делает мат отвестветвенный старого склада, а проставление нового склада и перевод в финальный статус — мат.ответственный нового склада. Таким образом с точки зрения СУБД все сводится у удалению старых проводок и созданию новых. Изменение проводок не требуется.
По поводу двойного учета и у типа договора не готов дискутировать, потому что не понимаю, какая предметная область и как реализовано. На первый взгляд, изменение типа договора вообще не должно влиять на реестр остатков, так как договор не является документом товародвижения, то есть основанием для проводок.
Именно. В триггере из :old берем старую строку реестра по ключам артикул/склад, вычитаем старое кол-во. Из :new берем новую строку и увеличиваем количество.
Не, это как по таблице проводок остатки обновлять. Это отдельная дополнительная задача (хотя тут даже INDEXED VIEW в MS SQL возможно справится).
Вопрос как по тем же документам проводки обновлять и как изменения в документе отслеживать? Там же нужно изменения и shipmentDetail и shipment и receiptDetail и receipt отслеживать.
Но вообще такая операция, как изменение склада в «выполненном» документе (документе в финальном статусе, в терминах 1С можно сказать в проведенном) — недопустима. Так как на разных складах разные мат.ответственные лица
А если остатки например в разрезе отделов магазина. Там одинаковые ответственные. И они тоже люди и тоже ошибаются. Или они должны дрожащими руками каждое действие делать?
И это должно делаться в два этапа: откат документа из финального статуса делает мат отвестветвенный старого склада, а проставление нового склада и перевод в финальный статус — мат.ответственный нового склада.
И ввод тоже? Но опять таки это уже вопрос конкретного бизнеса, у всех бизнесов разные процессы. А вы сейчас задачу под ответ подгоняете.
так как договор не является документом товародвижения, то есть основанием для проводок.
А причем тут товародвижение, я говорил про представление (регистр) текущей задолженности поставщика (по аналогии с остатками, только в деньгах)
Так вроде смотрим как работают АБС… Есть накопленный итог и хвост проводок, отсчитать можно от любого момента, более того, другого способа для транзакционной системы что-то не припомню (кромки блокировки на запись).
Какие-то странные выводы про неудобства табличных функций в cross apply — вполне можно и параметры функции использовать, и далее во where накладывать условия — inline-функция раскроется в запросе, и все условия сработают.
Кстати изучаю эту тему сейчас. С CROSS APPLY (вместо INNER JOIN) действительно вроде прокатывает (даже вроде не руша JPPD), а что с OUTER APPLY (вместо LEFT JOIN) делать, там то условие соединения в WHERE не перенесешь.
1. Лучше бы разбили статью на несколько отдельных частей по разным пунктам, т.к. сейчас получится каша с кучей комментариев к разным частям вразброс,
2. Еще лучше было бы разбить и по разным СУБД, т.к. уже вижу кучу неточностей по Oracle.
Далее постараюсь (насколько будет хватать времени и не будет мешать лень) прокомментировать по пунктам:
1. View: Материализация представлений поддерживается в очень частных случаях
Это очевидная неточность. Материализация представление поддерживается всегда, но не всегда поддерживаются те или иные опциональные возможности, как например FAST REFRESH.
2. Касательно FAST REFRESH: если вы подумаете и сами попробуете проанализировать как можно реализовать инкрементальные обновления, то поймете, что список ограничений абсолютно адекватен текущей сложности SQL.
3. «вас будет ждать еще один неприятный сюрприз: материализованное представление обновляется только в самом конце транзакции.»
Сюрприз?! При создании мвью вы сами выбираете «on commit», так что должны знать, что это происходит при коммите.
4. «абсолютно непонятно, как в принципе получить актуальные данные для материализованного представления внутри транзакции» — вы прямо напрашиваетесь на холивар об актуальности «грязных чтений». Не путайте функциональность и целевое назначение view и mview. MView, в первую очередь, это таблицы со всеми сопутствующими свойствами.
5. «один из достаточно авторитетных экспертов Oracle Donald Burleson в одной из своих книг.» — Что?! На таком серьезном ресурсе как хабр и упоминать бурлесоновщину?!
6. «View: В параметризованные представления во FROM можно передавать только константы»
В Oracle можно использовать контексты, функции, можно создать FGAC и тд и тп.
7. «В MS SQL для решения таких задач есть так называемые table inlined функции, в них можно объявить параметры и использовать их внутри запроса» — в оракле тоже есть и, кроме того, туда спокойно можно передавать параметры из других таблиц
8. «JPPD: Не работает с оконными функциями и рекурсивными CTE»
Работает, просто оконные функции вы не умеете готовить:
вы указываете «row_number() OVER (PARTITION BY shipment ORDER BY id)» — то есть сами инструктируете СУБД сначала посчитать ROW_NUMBER для всего дата сета из таблицы, а фильтруете на другом уровне, после этого подсчета. Если СУБД сначала отфильтровала по вашему предикату, то результат ROW_NUMBER был бы неверным.
9. «JPPD: Низкая эффективность при работе с денормализованными данными»
Ничего не понятно… Касательно выбранных планов оптимизатора, надо приводить точные данные и, например для Оракла, трассировку 10053. Тут важно даже умение собирать статистику.
10. «Так, например, переписанный запрос оконными функциями будет выглядеть следующим образом» — что вы тут хотели и почему запросы не эквивалентны? Вообще во всех указанных СУБД есть LATERALS/CROSS APPLY, которые легко решают проблемы с JPPD
11. «Разделение логики условий на типы JOIN и WHERE» — кстати, советую прочитать про Oracle vs ANSI синтаксис, и пре- и пост- предикаты в отличной книге «The Power of Oracle SQL» habr.com/en/post/461971
12. «Плохая оптимизация при работе с последними значениями»
Оракл может построить хороший план:
SQL> explain plan for
2 SELECT SUM(cc.ls)
3 FROM Product pr
4 LEFT JOIN (SELECT MAX(shipment) AS ls, s.product
5 FROM shipmentDetail s
6 GROUP BY s.product) cc ON cc.product=pr.id
7 WHERE pr.name LIKE 'Product 86%';
Explained.
Plan hash value: 2212025625
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 78 | 69 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 78 | | |
| 2 | NESTED LOOPS | | 1 | 78 | 69 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | PRODUCT | 1 | 65 | 68 (0)| 00:00:01 |
| 4 | VIEW PUSHED PREDICATE | | 1 | 13 | 1 (0)| 00:00:01 |
|* 5 | FILTER | | | | | |
| 6 | SORT AGGREGATE | | 1 | 26 | | |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED| SHIPMENTDETAIL | 1 | 26 | 1 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | SHIPMENTDETAIL_PRODUCT_FK | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$639F1A6F" "S"@"SEL$2")
INDEX_RS_ASC(@"SEL$639F1A6F" "S"@"SEL$2" ("SHIPMENTDETAIL"."PRODUCT"))
USE_NL(@"SEL$B9D46A48" "CC"@"SEL$1")
LEADING(@"SEL$B9D46A48" "PR"@"SEL$1" "CC"@"SEL$1")
NO_ACCESS(@"SEL$B9D46A48" "CC"@"SEL$1")
FULL(@"SEL$B9D46A48" "PR"@"SEL$1")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$3")
ANSI_REARCH(@"SEL$1")
OUTLINE(@"SEL$8812AA4E")
ANSI_REARCH(@"SEL$3")
OUTLINE(@"SEL$E8571221")
MERGE(@"SEL$8812AA4E" >"SEL$E8571221")
OUTLINE(@"SEL$776AA54E")
OUTLINE(@"SEL$2")
OUTER_JOIN_TO_INNER(@"SEL$776AA54E" "CC"@"SEL$1")
OUTLINE_LEAF(@"SEL$B9D46A48")
PUSH_PRED(@"SEL$B9D46A48" "CC"@"SEL$1" 2)
OUTLINE_LEAF(@"SEL$639F1A6F")
ALL_ROWS
DB_VERSION('18.1.0')
OPTIMIZER_FEATURES_ENABLE('18.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("PR"."NAME" LIKE 'Product 86%')
5 - filter(COUNT(*)>0)
8 - access("S"."PRODUCT"="PR"."ID")
Проверьте свои актуальность и свойства статистик по этим таблицам.
… /// to be continued…
Это очевидная неточность. Материализация представление поддерживается всегда, но не всегда поддерживаются те или иные опциональные возможности, как например FAST REFRESH.
Ну под материализацией я имел ввиду естественно обновляемую материализацию. Не обновляемая нужна только для задач аналитики. Но тут вопрос чья терминология первична (вон в MS SQL они вообще индексированными называются), если считать SQL(а точнее Oracle) первичной то да, может написать online или что-то типа того.
если вы подумаете и сами попробуете проанализировать как можно реализовать инкрементальные обновления, то поймете, что список ограничений абсолютно адекватен текущей сложности SQL.
Я не то что подумал, я учавствовал в реализации этого функционала, причем работающего на OLTPых террабайтных базах систем класса ERP.
Сюрприз?! При создании мвью вы сами выбираете «on commit», так что должны знать, что это происходит при коммите.
Я заметил, и почему это не удобно написал в статье.
Что?! На таком серьезном ресурсе как хабр и упоминать бурлесоновщину?!
Тут согласен, надо тщательнее изучить список экспертов. Не поделитесь ссылкой?
В Oracle можно использовать контексты, функции, можно создать FGAC и тд и тп.
Если вы про контексты, о которых я думаю, то это адский ад. То что в Oracle есть аналог table inlined функций возможно, я и не утверждал обратное. Причем тут FGAC не совсем понял.
в оракле тоже есть и, кроме того, туда спокойно можно передавать параметры из других таблиц
То есть запрос который я привел в Oracle заработает? А можно пример? Тогда дополню статью.
Работает, просто оконные функции вы не умеете готовить:
вы указываете «row_number() OVER (PARTITION BY shipment ORDER BY id)» — то есть сами инструктируете СУБД сначала посчитать ROW_NUMBER для всего дата сета из таблицы, а фильтруете на другом уровне, после этого подсчета. Если СУБД сначала отфильтровала по вашему предикату, то результат ROW_NUMBER был бы неверным.
Я там внизу привел пример как надо было делать JPPD (и как мы его делаем).
что вы тут хотели и почему запросы не эквивалентны? Вообще во всех указанных СУБД есть LATERALS/CROSS APPLY, которые легко решают проблемы с JPPD
Не уверен что CROSS APPLY сработают с оконными функциями, но это и не важно. Вопрос, что Oracle сам этого не делает. По такой логике зачем вообще оптимизатор нужен, если можно все самому сделать. Собственно я для этого и начал с представлений, так как именно с ними задача оптимизаций запросов особенно актуальна.
кстати, советую прочитать про Oracle vs ANSI синтаксис, и пре- и пост- предикаты в отличной книге «The Power of Oracle SQL» habr.com/en/post/461971
И какое отношение это имеет к тому что я написал?
Оракл может построить хороший план:
Я там 100 раз все пересчитывал. Но в любом случае там вроде COUNT STOPKEY должен быть, а не FILTER(COUNT(*)>0). Но тут не уверен. Вы можете скриптом который вначале статьи сгенерить базу (он минут 10 выполняется максимум) и выполнить запрос.
Разделение логики условий на типы JOIN и WHEREПо-моему, как раз наоборот. ВСЕ замечают, что логика разделена на две части, но не все сразу умеют правильно её готовить. Я поначалу тоже писал всё в WHERE:
Немногие это замечают, но логика, влияющая на то, какие записи окажутся в результирующей таблице в SQL, разделена на 2 части
select ...
from a, b, c, d, e
where a.column1 = b.column1
and b.column2 = c.column2
and c.column3 = d.column3
and d.column4 = e.column4
and a.column123 = 456
and b.column987 = 'ququ'
and ... -- и еще 100500 условийА потом оказалось, что сложные запросы, написанные кем-то другим, а то и самим собой, но очень давно, (ВНЕЗАПНО!) нужно отлаживать и рефакторить. И вот в длинной трехстраничной портянке начинаешь разбираться — где условия соединения, где логика, где чё. Потом условия соединения сами собой кучкуются вместе, а потом уползают в секцию FROM, чтобы не мешались.
select ...
from a
join b on a.column1 = b.column1
join c on b.column2 = c.column2
join d on c.column3 = d.column3
join e on d.column4 = e.column4
where a.column123 = 456
and b.column987 = 'ququ'
and ... -- и еще 100500 условийПотом появляется вишенка на торте. Оказывается, что записей какое-то не такое количество, поэтому нужно закомментить все, кроме одной таблицы, а потом приджойнивать остальные по одной. И оказывается, что если все условия соединения описаны во FROM, то решается эта проблема исключительно просто:
select ...
from a
-- join b on a.column1 = b.column1
-- join c on b.column2 = c.column2
-- join d on c.column3 = d.column3
-- join e on d.column4 = e.column4
where a.column123 = 456
and b.column987 = 'ququ'
and ... -- и еще 100500 условийКороче, я лично транклюкирую ту гадину, которая такую возможность у меня отнимет [сатанинский хохот за кадром]
SET @ProductName = LTRIM(RTRIM(@ProductName));Начиная с MSSQL 2017 наконец-то присутствует функция TRIM
2.
В Oracle и PostgreSQL в них нельзя обращаться к новым и предыдущим значениям, то есть такие триггеры бесполезны чуть меньше чем полностью.
В PostgreSQL можно начиная с версии 10 (клауза REFERENCING)
Единственное ограничение — триггер не должен быть мультисобытийным
Мне не хватает еще возможность передачи параметра во FROM
Например:
SELECT id
FROM :tableNameХорошо бы добавить возможность получить N строк просто для запроса.
SELECT n
FROM someSystemTable
WHERE someSystemTable.start=42 and someSystemTable.end=142Добавить бы ограничения на группу. Скажем запрос «Топ 3 самых дорогих товара для всех категорий» это тот еще квест.
Нарастающий итого (running total) еще один вызов. Сделать то можно, но всегда непросто. Сильно не хватате обращений к предыдущей и следующей строке.
передачи параметра во FROM
dynamic sql
Хорошо бы добавить возможность получить N строк просто для запроса.
fetch...offset
Топ 3 самых дорогих товара для всех категорий
select c.name, tt.name from category c cross apply (select top 3 name from product p where p.category_id = c.id order by price desc) ttrunning total
sum(x) over(order by ...)
Сильно не хватате обращений к предыдущей и следующей строке.PRECEDING / FOLLOWING
Проблема N+1
А теперь нам нужно выполнить эту процедуру для 1000 записей.
Это не проблема N+1. Она ведь не просто так так называется. Попробуйте определить, где тут N, а где 1.
Другое дело, это самая близкая аналогия из всех существующих, и конечные последствия у нее такие же.
Из области ORM, и ORM же ее и решает. Она связана с чтением. Насколько я знаю, вставку 1000 записей не называют N+1. При работе с базой из приложения, в тех местах, где она может возникнуть, она легко оптимизируется до массовых вставок.
В примере все параметры вводятся извне и добавляются в таблицу при вызове процедуры, поэтому непонятно, откуда там может что-то выбираться до вызова.
Из области ORM, и ORM же ее и решает.
Ну как решает, пытается решить. Точнее разработчик должен это делать. Но это в следующих статьях про ORM/ERP фреймворки.
При работе с базой из приложения, в тех местах, где она может возникнуть, она легко оптимизируется до массовых вставок.
Что значит легко? Переписыванием процедуры по сути. Или дублированием что еще хуже.
В примере все параметры вводятся извне и добавляются в таблицу при вызове процедуры, поэтому непонятно, откуда там может что-то выбираться до вызова.
Там имелось ввиду что нужно вызвать эту процедуру для всех записей какой-то таблицы.
Ну как решает, пытается решить. Точнее разработчик должен это делать.
Все нормально решается, автоматически делается второй запрос с WHERE foreign_key IN (...) по ключам из основного запроса. Разработчик только указывает что-то типа Entity::find()->where(...)->with('relationship').
Что значит легко? Переписыванием процедуры по сути.
При работе с базой из приложения таких процедур вообще не будет. И аналогов тоже не будет. Изменение параметров сущностей отдельно, сохранение отдельно. Вызываем логику для каждой сущности, обработанные сущности накапливаем в массив, по достижении N записей сохраняем и массив сбрасываем. Потребность в массовых вставках обычно возникает при каких-нибудь выгрузках/загрузках, которые делаются консольными приложениями и к работе пользователей не имеют отношения, а значит на соответствующий код не влияют.
Разработчик только указывает что-то типа Entity::find()->where(...)->with('relationship').
Ну так я и говорю разработчик делает, а не ORM.
Вызываем логику для каждой сущности
Ну так вы когда вызываете логику каждой сущности у вас N+1 и будет.
Или еще хуже вам эту логику дублировать придется. Сначала чтобы избежать проблемы N+1 докинуть в первый запрос все нужные данные, а потом еще раз повторить эту логику в ORM с обработкой этих данных (дублирование конечно не полное будет, но процентов на 80). Ну и вариантов выстрелить в ногу, если забыли, не те данные считали и т.п.
Но вообще конкретно эта статья не про ORM, тут как раз про то когда бизнес-логику на SQL делают — в банках и других финансах, ритейле это очень распространено.
Ну так я и говорю разработчик делает, а не ORM.
Так она сама и не может знать, какие части из всего графа сущностей надо загрузить из базы. Написать вызов одной функции это не какая-то большая сложность.
Ну так вы когда вызываете логику каждой сущности у вас N+1 и будет.
Ага, только не запросов к базе, а вызовов функций внутри приложения. Это будет примерно то же самое, что будет делать база для каждой строки.
Или еще хуже вам эту логику дублировать придется. Сначала чтобы избежать проблемы N+1 докинуть в первый запрос все нужные данные
Ничего не надо дублировать, и первый запрос менять не надо. Зачем? Логика находится в одном месте, ее и вызываем.
Но вообще конкретно эта статья не про ORM, тут как раз про то когда бизнес-логику на SQL делают
Вот и надо говорить о проблемах и альтернативах. Возможно для кого-то некоторое снижение производительности будет приемлемо для уменьшения вероятности ошибки и увеличения удобности разработки.
Так она сама и не может знать, какие части из всего графа сущностей надо загрузить из базы. Написать вызов одной функции это не какая-то большая сложность.
Ну как бы из кода обработки могла бы догадаться. Ну и там может быть много данных использоваться, так что функция не одна будет.
Ага, только не запросов к базе, а вызовов функций внутри приложения. Это будет примерно то же самое, что будет делать база для каждой строки.
Подразумевалось, если вы предварительно не укажите какие данные считать (то что вы писали сверху).
Ничего не надо дублировать, и первый запрос менять не надо. Зачем? Логика находится в одном месте, ее и вызываем.
Имелось ввиду, допустим вам надо в строку записать текущую цену по товару, ну и надбавку в зависимости от классификатора. Вам надо сначала orm'у сказать что их надо предварительно загрузить, а потом в логике обработки опять к ним же обратиться и выполнить непосредственно обработку. Хотя ORM'у что-то предварительно говорить это monkey job (а точнее ручная оптимизация, то есть accidental complexity).
Ну как бы из кода обработки могла бы догадаться.
Нет. Данные из одной и той же таблицы могут использоваться в разных частях приложения, в коде одной обработки надо загружать связанные сущности, в коде другой нет.
Ну и там может быть много данных использоваться, так что функция не одна будет.
Ага, ->with('relationship1')->with('relationship2')->with('relationship3'), очень сложный код)
Это я имел ввиду
Ну тогда и ваше решение от N+1 не избавляет, и вообще от нее избавиться нельзя. База же для всех N строк делает обработку.
Я имел ввиду, допустим вам надо в строку записать текущую цену по товару, ну и надбавку в зависимости от классификатора. Вам надо сначала orm'у сказать что их надо предварительно загрузить, а потом в логике обработки опять к ним же обратиться и выполнить непосредственно обработку.
Все равно никакого дублирования логики нет.
$skus = Sku::find()->where(...)->limit($offset, $limit)->with('category');
foreach ($skus as $sku) {
$sku->calculateNewPrice();
}
$this->bulkInsert($skus);
class Sku
{
public function calculateNewPrice()
{
if ($this->category->param1 == 'value1') {
$this->price = 100;
} elseif ($this->category->param2 == 'value2') {
$this->price = 200;
}
}
}
Логика может находиться и не в Sku, но все равно в одном месте.
Хотя ORM'у что-то предварительно говорить это monkey job
В таком общем виде это утверждение неверно. Вы о каких-то своих представлениях говорите, и скорее всего неправильных.
Как это все будет выглядеть?
Код обновления тогда тоже напишите.
Если я правильно понял связи, можно вот так сделать. Не уверен, что из коробки заработает в Yii (я в примерах его подразумеваю), но в целом это решаемо.
$products = Product::find()
->joinWith('documentRow')
->joinWith('documentRow.document')
->joinWith('lastPrice')
->where(...)->limit($offset, $limit);
foreach ($products as $product) {
$product->price = $product->lastPrice->price;
}
$this->bulkInsert($products);
...
class Product
{
public function getDocumentRow()
{
$this->hasOne(DocumentRow::class, ['id', 'document_row_id']);
}
public function getLastPrice()
{
$this->hasOne(LastPrice::class, [['product_id', 'store_id'], ['id', 'store_id']]);
}
}
class DocumentRow
{
public function getDocument()
{
$this->hasOne(Document::class, ['id', 'document_id']);
}
}
class LastPrice
{
public function table()
{
return 'prices';
}
public function find()
{
$subQuery = parent::find()->select('LAST(price)')
->groupBy(['product_id', 'store_id'])
->orderBy(['date', 'id']);
return (new Query())->select('*')->from($subQuery);
}
}Но такая архитектура с обновлением из истории выглядит странно. Я бы сделал обновление цены там же, где добавляется строка в таблицу prices. Если надо строго раз в сутки цены менять, то можно добавлять в отдельную таблицу new_prices (product_id, price) и обновлять только нужные строки по id, не перелопачивая при этом всю историю изменения цен на всех складах.
lastPrice = GROUP LAST price(Price p) BY product(p), stock(p);
WHEN CHANGED(product(DocumentDetail d)) DO
price(d) <- lastPrice(product(d), stock(d)) * (100 + markup(product(d), stock(d))) / 100;
Ну или можно так:
WHEN CHANGED(product(DocumentDetail d)) AND Product pr = product(d) AND Stock s = stock(d) DO
price(d) <- (GROUP LAST price(Price p) IF product(p) = pr AND stock(p) = pr ORDER date(p),p) * (100 + markup(p, s)) / 100;
У вас я так и не понял откуда она relation lastPrice возьмет (ну и вы код с описанием этих relation'ов не привели). Хотя видимо по самой задаче мы друг друга не допоняли.
Весь код для описания связей там есть. joinWith('lastPrice') обращается к функции getLastPrice(), которая возвращает настроенный объект Query. После запроса заполняются все указанные связи, во всех $product будет заполнен $product->lastPrice.
price(d) <- lastPrice(product(d), stock(d)) * (100 + markup(product(d), stock(d))) / 100;Почему изменение цены товара не попадает в таблицу изменения цен?
Ну я там наценку не добавил, да, там будет так:
$product->price =
$priceManager->calculatePriceWithMarkup(
$product->lastPrice->price,
$product->documentRow->document->stock_id
);А можно просто $product передавать.
Почему изменение цены товара не попадает в таблицу изменения цен?
Так тут цена просто отгрузки из текущей цены заполняется. Или я чего-то не понял в вопросе.
Ну я там наценку не добавил, да, там будет так:
Так а как будет выглядеть код по его предварительному чтению?
А где привязка что product именно по product_id, а не store_id.
Код при джойне превращается в такой SQL:
// code
(LastPrice::class, [['product_id', 'store_id'], ['id', 'store_id']])
// SQL
lastPrice.product_id = id AND lastPrice.store_id = store_idstore_id берется из document, без джойнов естественно работать не будет. Возможно там надо будет указать джойн в самой связи и поля с названиями таблиц, сейчас точно не скажу, но принцип будет такой же.
А, и в LastPrice у меня только select('LAST(price)'), полей product_id и store_id нет, надо брать select('LAST(id)') и джойнить полную запись.
Так а как будет выглядеть код по его предварительному чтению?
По чтению кого? Все чтение это результат запроса в первой строчке. Читаются товары и их связи, причем так как там джойны, то вроде бы даже запросов с IN не будет, связи заполнятся из данных основного запроса.
Так тут цена просто отгрузки из текущей цены заполняется.
Разговор же был про цену товара, причем тут цена отгрузки?
В общем получается так, у вас в коде названия не соответствуют терминам предметной области, архитектура тоже выглядит слегка странно. Можем обсудить подробнее, как это делается при бизнес-логике в приложении, если напишете исходные требования к системе на русском языке, типа небольшое ТЗ на пару абзацев. Код на вашем языке для меня не настолько понятный.
store_id берется из document, без джойнов естественно работать не будет. Возможно там надо будет указать джойн в самой связи и поля с названиями таблиц, сейчас точно не скажу, но принцип будет такой же.
А, и в LastPrice у меня только select('LAST(price)'), полей product_id и store_id нет, надо брать select('LAST(id)') и джойнить полную запись.
Так я так и не понял. То что сверху это весь код или нет?
По чтению кого? Все чтение это результат запроса в первой строчке. Читаются товары и их связи, причем так как там джойны, то вроде бы даже запросов с IN не будет, связи заполнятся из данных основного запроса.
Я про calculatePriceWithMarkup, ему же тоже нужны данные из базы. Если написать так как вы сделали, и в
$products = Product::find()
->joinWith('documentRow')
…
ничего не добавить, то она же N запросов делает.
Разговор же был про цену товара, причем тут цена отгрузки?
надо в строку записать текущую цену по товару, ну и надбавку в зависимости от классификатора
В строку документа (отгрузки) имелось ввиду записать цену товара с надбавкой.
Код на вашем языке для меня не настолько понятный.
Так если его буквально с английского переводить будет:
Когда изменится товар в строке документа:
Записать в цену строки документа, текущую цену товара этой строки документа, склада этого документа с добавленной надбавкой для товара, склада.
Этот код и есть по сути ТЗ.
То что сверху это весь код или нет?
Это весь код, но я его не запускал. В реальном приложении могут быть незначительные отличия, не влияющие на общую концепцию, но возможно вызывающие непонимание сейчас, поэтому я пишу уточнения.
Я про calculatePriceWithMarkup, ему же тоже нужны данные из базы.
она же N запросов делает
У вас в примере туда передается только товар и склад. В моем коде все они достаются одним запросом. Откуда там N запросов, если данные уже загружены из базы?
Так если его буквально с английского переводить будет:
Когда изменится товар в строке документа:
Записать в цену строки документа, текущую цену товара этой строки документа, склада этого документа с добавленной надбавкой для товара, склада.
В данном тексте нет термина "отгрузка". Поэтому это никакое не ТЗ, это просто описание работы вашего кода. Что за документы, какие документы, при каких условиях изменение появляется, откуда тут 1000 запросов возьмется.
Это весь код, но я его не запускал. В реальном приложении могут быть незначительные отличия, не влияющие на общую концепцию, но возможно вызывающие непонимание сейчас, поэтому я пишу уточнения.
Так в том то и вопрос, что непонятно насколько они небольшие будут. У вас и так уже мягко говоря не мало кода. А вы еще говорите будет что-то.
У вас в примере туда передается только товар и склад. В моем коде все они достаются одним запросом. Откуда там N запросов, если данные уже загружены из базы?
Так в одном документе много товаров. А у вас код считает наценку для одного товара.
В данном тексте нет термина «отгрузка». Поэтому это никакое не ТЗ, это просто описание работы вашего кода. Что за документы, какие документы, при каких условиях изменение появляется, откуда тут 1000 запросов возьмется.
Ну в данном случае я имел ввиду, что документ — это документ отгрузки. В любом случае можно считать что это просто документ. Давайте упростим задачу и забудем про изменение — то есть просто пересчитать для всех строк документа:
lastPrice = GROUP LAST price(Price p) ORDER date(p),p BY product(p), stock(p);
FOR document(DocumentDetail d) = myDocument DO
price(d) <- lastPrice(product(d), stock(d)) * (100 + markup(product(d), stock(d))) / 100;
Когда изменится товар в строке документа:
Записать в цену строки документа, текущую цену товара этой строки документа
Это кстати уже UPDATE, а не INSERT, который был в примере про N+1.
если старый — то UPDATE
И как вы предлагаете делать массовый UPDATE? Мы же про N+1 говорим.
У вас и так уже мягко говоря не мало кода.
Код, относящийся к обсуждаемой логике находится до многоточия. Остальное это аналог объявления внешних ключей в CREATE TABLE, и этот код общий для всего приложения. Вы вообще код функций не привели, только запрос.
Так в том то и вопрос, что непонятно насколько они небольшие будут.
Нет такого вопроса. Я показываю принцип, как это делается из приложения и почему это удобнее. Незначительные синтаксические отличия типа table.field вместо field значения не имеют, принцип от них не изменится.
Так в одном документе много товаров. А у вас код считает наценку для одного товара.
Ну так и у вас код считает наценку только для одного товара. Наценка зависит от одного товара товара и одного склада, а не от всех товаров в документе.
price(d) <- lastPrice(product(d), stock(d)) * (100 + markup(product(d), stock(d))) / 100;
У меня товары уже загружены из базы в количестве $limit штук, для каждого также загружены связи "строка документа -> документ -> склад". Для всех делаем расчет, потом все сохраняем в базу. Во время расчетов никаких запросов в базу не идет.
Давайте упростим задачу и забудем про изменение — то есть просто пересчитать для всех строк документа:
Я говорю о том, что делать так не надо. Не надо ничего пересчитывать и перезаписывать. Это требуется крайне редко и обычно запускается по расписанию, а не по событию. Чтобы разобраться, как правильно, я предложил обсудить исходные требования.
И как вы предлагаете делать массовый UPDATE? Мы же про N+1 говорим.
А в чем проблема? UPDATE с запросами тоже работает. То есть мой пример можно скомпилировать в один запрос.
Ну так и у вас код считает наценку только для одного товара.
Нет сверху FOR идет. И lsFusion скомпилирует его в
price(DocumentDetail d) <- lastPrice(product(d), stock(d)) * (100 + markup(product(d), stock(d))) / 100 WHERE document(d) = myDocument
Который в свою очередь скомпилируется в один SQL MERGE запрос.
У меня товары уже загружены из базы в количестве $limit штук, для каждого также загружены связи «строка документа -> документ -> склад».
Так а с prices и markup что будет? По сути вы будете эту логику на SQL (а точнее на функциональном псевдо-SQL) писать, а обработку в ORM?
Я уж молчу про то, что данные зачем то гоняются туда- сюда, с сервера БД на сервер приложений, а потом обратно.
Это требуется крайне редко и обычно запускается по расписанию, а не по событию
В ERP/CRM/BPM, банковских системах и вообще любых сложных системах это вообще-то стандартный кейс, их тысячи там. И там не по расписанию, а в процессе ввода это все запускается.
UPDATE с запросами тоже работает.
А, ну да, я про значения извне думал.
В моем коде все они достаются одним запросом. Откуда там N запросов, если данные уже загружены из базы?
Так в одном документе много товаров. А у вас код считает наценку для одного товара.
Ну так и у вас код считает наценку только для одного товара.
Нет сверху FOR идет. И lsFusion скомпилирует его в… Который в свою очередь скомпилируется в один SQL MERGE запрос.
У меня код "считает наценку только для одного товара" ровно в той же мере, в какой движок базы "считает эту наценку для одной строки" при выполнении вашего запроса.
У меня тоже один запрос, а не N. Ну вернее 2, выборка и сохранение.
Так а с prices и markup что будет? По сути вы будете эту логику на SQL (а точнее на функциональном псевдо-SQL) писать, а обработку в ORM?
А что с ними не так? Последняя цена тоже загружена через связи, через JOIN с подзапросом. markup, как я понимаю, содержит алгоритм наценки, который рассчитывается по product и stock, у меня он находится в calculatePriceWithMarkup(). В SQL ничего не пишется, вся логика находится в приложении.
Я уж молчу про то, что данные зачем то гоняются туда-сюда, с сервера БД на сервер приложений, а потом обратно.
Затем, что так удобнее управлять кодом. Нет тех проблем, которые вы пытаетесь решить, используя логику в хранимых процедурах.
В ERP/CRM/BPM, банковских системах и вообще любых сложных системах это вообще-то стандартный кейс, их тысячи там.
Я вообще-то про то и говорю, что это стандартный кейс в первую очередь потому что "бизнес-логику на SQL делают". Если бы логика была в приложении, в большинстве случаев это было бы не нужно.
Затем, что так удобнее управлять кодом. Нет тех проблем, которые вы пытаетесь решить, используя логику в хранимых процедурах.
Так подождите, вы же по сути и пишете на SQL только в специфичной синтаксической обертке (с JOIN'ами, GROUP BY только в три раза длиннее) и с суррогатными классами (вроде LastPrice). И чем тогда удобнее управлять кодом? При этом данные гоняются туда-сюда.
На каком SQL? На SQL я достаю данные из базы и сохраняю обратно, это протокол общения с хранилищем, без него в принципе невозможно обойтись. А вся логика находится в приложении, написана на языке программирования, разбита на классы и методы, которые соответствуют терминам предметной области, можно использовать средства переиспользования кода (наследование, композиция, трейты), подключать любые сторонние технологии (кеши, хранилища), запускать многопоточную обработку. Ничего из этого в SQL нет, либо довольно сложно использовать.
На каком SQL?
public function getLastPrice()
{
$this->hasOne(LastPrice::class, [['product_id', 'store_id'], ['id', 'store_id']]);
}
public function find()
{
$subQuery = parent::find()->select('LAST(price)')
->groupBy(['product_id', 'store_id'])
->orderBy(['date', 'id']);
return (new Query())->select('*')->from($subQuery);
}
Вы же понимаете, что это обычные Join'ы и Group by, только в таком весьма вывернутом синтаксисе. И в нормальной жизни, если бы не SQL, так бы на PHP никто не писал.
С наследованием, вы уверены что joinWith работает работает с абстрактными методами. Ну и со всякими классами в стиле LastPrice, будут проблемы, я в статье приводил пример когда писал про наследование в оракл
Вы же понимаете, что это обычные Join'ы и Group by, только в таком весьма вывернутом синтаксисе.
Нет, это обычные foreign key в синтаксисе ORM. В join их превращает ORM, может в отдельный запрос в IN превратить, может использовать эту информацию при выводе интерфейса, или при обработке запроса к API. Это просто связи между сущностями, отражение связей предметной области.
groupBy да, это часть запроса, но без него нельзя получить данные, с которыми работает логика. И как я уже сказал, он там нужен только потому что вы сделали такую таблицу.
И в нормальной жизни, если бы не SQL, так бы на PHP никто не писал.
Еще раз, PHP это не хранилище данных, и без обращений к хранилищу данных работать с данными принципиально нельзя. Если бы не SQL, писали бы на каком-нибудь другом синтаксисе, начиная с банального fread/fwrite по нужным смещениям.
С наследованием, вы уверены что joinWith работает работает с абстрактными методами.
joinWith и with просто вызывают функцию по имени, добавляя 'get' к аргументу. Если метод не будет реализован, будет соответствующая ошибка в рантайме.
Ну и со всякими классами в стиле LastPrice, будут проблемы, я в статье приводил пример
Вы не приводили пример, вы сказали, что это почему-то суррогатные классы, хотя эта связь многие-ко-многим явно присутствует в бизнес-логике, что их почему-то надо наследовать, и что будут какие-то проблемы. Тем более что LastPrice не является такой связью, соответственно и описанных там проблем с ней не будет. Тем более что LastPrice есть у вас в системе в виде представления, и непонятно, почему в приложении где никаких представлений нет оно не может быть в виде класса, как и любая другая таблица. Тем более что я бы сделал без нее, но по другим причинам.
Наследование бизнес-сущностей это вообще отдельный вопрос. Я согласен, что это было бы неплохо, но для контроля типов в этом случае нужна особая поддержка на уровне языка, которая выходит за рамки данного обсуждения. В пределах текущих возможностей языков это крайне спорное решение, обычно можно сделать по-другому.
foreign key
Foreign key логически и есть join, то есть таблица, ключевое поле и поле связывания.
Еще раз, PHP это не хранилище данных, и без обращений к хранилищу данных работать с данными принципиально нельзя. Если бы не SQL, писали бы на каком-нибудь другом синтаксисе, начиная с банального fread/fwrite по нужным смещениям.
Это SQL замаскированный под PHP. А тощая корова — не газель.
хотя эта связь многие-ко-многим явно присутствует в бизнес-логике
Я вообще не понимаю этих суррогатов многие-ко-многим. А если четыре ключа в таблице, то это многие-ко-многим-ко-многим-ко-многим?
где никаких представлений нет оно не может быть в виде класса, как и любая другая таблица
Потому что логически такого объекта как последняя цена нет. Есть функция последняя цена от двух объектов (склада и товара).
обычно можно сделать по-другому
Это всегда можно было делать по другому. С тех пор как появились If'ы. Но если все делать If'ами получается адский лапше-код.
Foreign key логически и есть join, то есть таблица, ключевое поле и поле связывания.
Нет. Логически foreign key это связь между сущностями. Это даже по последнему слову в вашей фразе заметно. Оно может использоваться для присоединения, а может не использоваться. Для контроля целостности ключей например используется.
Это SQL замаскированный под PHP.
Нет. Никто ничего не маскирует, запрос это явное обращение к хранилищу. Ну невозможно обрабатывать данные, не имея данных. Значит их надо как-то получить. Может через SQL, может через протокол MongoDB, может через URL-запрос из внешнего API. Логика их обработки останется та же самая.
Я вообще не понимаю этих суррогатов многие-ко-многим. А если четыре ключа в таблице, то это многие-ко-многим-ко-многим-ко-многим?
Эм, связь многие-ко-многим это вообще-то один из стандартных терминов теории баз данных.
Потому что логически такого объекта как последняя цена нет. Есть функция последняя цена от двух объектов (склада и товара).
Ну как это нет, если вы его используете. Оно даже в вашей терминологии существительное. И это не техническая терминология, я терминология предметной области.
На всякий случай еще раз повторю — в приложении я сделал такой класс исключительно потому что у вас в базе есть такое представление. Мне он не нужен, я бы сделал по-другому. ORM это mapping, вот ваше представление мапится на этот класс, а строки из этого представления на объекты этого класса, так же как таблица products мапится на класс Product. В базе есть встроенные типы "таблица" и "представление", а в языке программирования для создания пользовательских типов данных нет ничего кроме классов. Если вы считаете, что это лишняя сущность, уберите ее из вашей архитектуры, тогда и в приложении ее не будет.
Но если все делать If'ами получается адский лапше-код.
Так и не надо все делать if-ами, есть другие способы. Композиция называется. Связь один-к-одному вполне нормально работает.
Нет. Логически foreign key это связь между сущностями.
Так и join это логически связь между таблицами (сущностями в вашей терминологии).
может через протокол MongoDB
А вот тут можно поподробнее. Как это join'ы и group by в MongoDB, это вроде как «документная» база.
Эм, связь многие-ко-многим это вообще-то один из стандартных терминов теории баз данных.
Да я знаю, и она очень дырявая на мой взгляд.
Ну как это нет, если вы его используете. Оно даже в вашей терминологии существительное. И это не техническая терминология, я терминология предметной области.
Под объектом я имею ввиду бизнес-объект. То, что явно создается при помощи new, имеет идентификатор (один) и т.п.
Мне он не нужен, я бы сделал по-другому
И как бы вы сделали?
Композиция называется
Вообще основная и практически единственная функция наследования это использование в механизмах полиморфизма (именно полиморфизм это замена if, такой «модульный» if). И композиция вам тут никак не поможет.
Так и join это логически связь между таблицами (сущностями в вашей терминологии).
Нет. Джойн это соединение двух множеств по некоторому условию. Он может быть по foreign key, а может по другому условию. И наоборот, связь между сущностями есть, а никаких джойнов может не быть.
Как это join'ы и group by в MongoDB, это вроде как «документная» база.
А причем тут именно join'ы и group by? Они мне сами по себе не нужны, мне нужны данные из хранилища. Я уже несколько раз повторил — это просто способ получения данных. Будет другое хранилище, будет другой протокол их получения.
Да я знаю, и она очень дырявая на мой взгляд.
Какая разница, дырявая она или нет. Термин "многие-ко-многим" обозначает определенный тип взаимосвязей между сущностями. Как правило при хранении данных в базе для такой связи требуется промежуточная таблица. Когда говорят этот термин, подразумевают эти особенности. Всё, причем тут какие-то суррогаты?
То, что явно создается при помощи new, имеет идентификатор (один)
Вот есть у вас тип "Склад", и есть более конкретный тип "Магазин". Вы когда "Склад" создаете при помощи new, у вас создается экземпляр сущности "Магазин"? Вот так и тут, вы создаете объект, обозначающий изменение цены, и он же становится экземпляром более конкретного типа "Текущая цена". Это кстати частично связано с наследованием, я там даже хотел унаследовать от класса Price, но потом решил попроще сделать.
Один идентификатор есть только у самостоятельных сущностей, у связей между ними идентификаторов как минимум 2, потому что у связи между чем-то и чем-то как минимум 2 конца. Связи это тоже полноценные понятия предметной области. Если вы будете заметать их под ковер, у вас могут быть проблемы с архитектурой. Возможно вы с ними не сталкивались, потому что в движке базы данных уже есть особые типы данных "таблица", "представление", и остальные, поэтому вам свои классы для них создавать не надо.
И композиция вам тут никак не поможет.
Класс Client, таблица client. Класс PhysicalClient с дополнительными полями и специфичным методами, таблица physical_client, в классе Client свойство physicalClient, которое может быть равно null. Там может будет пара if где-нибудь в начале обработки на сравнение с null, ну и всё.
И как бы вы сделали?
Описывал тут, последний абзац.
Вы когда «Склад» создаете при помощи new, у вас создается экземпляр сущности «Магазин»?
Нет. Не создается. Это объект класса Магазин является объектом класса Склад. Но не наоборот.
Один идентификатор есть только у самостоятельных сущностей,
Вот я про эти самостоятельные сущности и говорю. Это объекты и классы. А «несамостоятельные» сущности это — костыли ORM.
Там может будет пара if где-нибудь в начале обработки на сравнение с null, ну и всё.
Их пара, когда у вас два класса. Будет больше, будет винегрет if'ов, как собственно и было до появления ООП.
Описывал тут, последний абзац.
То есть ручной материализацией (триггерами по сути). Кстати, а куда бы вы это поле последней цены добавили и как таблицу назвали?
Нет. Не создается. Это объект класса Магазин является объектом класса Склад. Но не наоборот.
Откуда же он тогда взялся? Мы же объект "Склад" создавали.
Вот я про эти самостоятельные сущности и говорю. Это объекты и классы. А «несамостоятельные» сущности это — костыли ORM.
Причем тут ORM, если связи есть и в вашем коде, и вообще в предметной области?
Их пара, когда у вас два класса. Будет больше, будет винегрет if'ов
Не будет. Приводите примеры пожалуйста для своих утверждений.
То есть ручной материализацией (триггерами по сути).
Причем тут триггеры? Триггеры там ровно в той же мере, что и в вашем коде. Вы же откуда-то данные берете для вставки, и добавляете по какому-то событию. Таблица изменения цен никуда не девается, просто не используется для расчета текущей цены. Состояние отдельно, логи отдельно.
Кстати, а куда бы вы это поле последней цены добавили и как таблицу назвали?
Это цена товара на складе? Вот в параметры этой сущности бы и добавил. Есть Product с характеристиками, есть Sku(product_id) на конкретном складе, вот у этого Sku есть текущая цена на этом складе. Связь со складом у него даже в названии отражена. Никакого перелопачивания миллионов данных по всем складам не требуется.
и с суррогатными классами (вроде LastPrice)
Какой же он суррогатный, если у вас специальное представление для него написано? Он там присутствует только потому что у вас в требованиях крайне спорное решение с доставанием последней записи из истории изменений. Как я уже сказал, обычно это делается по-другому.
Какой же он суррогатный
Суррогатный класс потому что последняя цена — это функция (от товара и склада), а не объект (объекты это товар и склад). А вы создаете по сути суррогатные объекты и суррогатные классы под них.
Функция, которая возвращает что? Запись в таблице prices это тоже объект.
Да, но это объект изменения цены — PriceChange, но никак не последняя цена — LastPrice.
Цитата:
"текущая цена это представление — SELECT LAST (price ORDER BY date,id) FROM prices GROUP BY product, store"
Ну не LastPrice, а CurrentPrice, это ничего не меняет. У вас есть конкретный бизнес-термин, который обозначает конкретную бизнес-сущность. От того, что вы будете делать вид, что ее нет, она никуда не денется. А при разработке могут быть проблемы из-за недостаточной модели предметной области.
который обозначает конкретную бизнес-сущность
Тут вопрос что такое сущность. Если вы имеете ввиду под этим класс (для которого создаются объекты), то последняя цена — не класс.
Тут вопрос что такое сущность.
Инфологическая модель данных «Сущность-связь»
Сущность – любой различимый объект (объект, который мы можем отличить от другого), информацию о котором необходимо хранить в базе данных. Сущностями могут быть люди, места, самолеты, рейсы, вкус, цвет и т.д. Необходимо различать такие понятия, как тип сущности и экземпляр сущности. Понятие тип сущности относится к набору однородных личностей, предметов, событий или идей, выступающих как целое. Экземпляр сущности относится к конкретной вещи в наборе.
Entity–relationship model
An entity may be defined as a thing capable of an independent existence that can be uniquely identified. An entity is an abstraction from the complexities of a domain. When we speak of an entity, we normally speak of some aspect of the real world that can be distinguished from other aspects of the real world.
An entity is a thing that exists either physically or logically.
An entity, strictly speaking, is an instance of a given entity-type.
У меня более конкретное определение объекта — то, что явно создается при помощи new, имеет идентификатор (один) и т.п.
Количество в документе тоже тогда сущность.
"independent existence" означает состояние и поведение, что означает необходимость определять объект как один и тот же в разные моменты изменения поведения. Если мы хотим выделять количество как один и тот же объект в разные моменты времени, то да, это будет экземпляр сущности, а его тип сущностью. В соответствующих предметных областях выделяют такие классы. Например в каких-нибудь анализаторах кода будет объект, соответствующий интовой переменной в анализируемом коде.
У количества в документе мы состояние и поведение не выделяем, поэтому оно не сущность. А у документа выделяем, поэтому оно сущность.
У меня более конкретное определение объекта — то, что явно создается при помощи new, имеет идентификатор (один) и т.п.
Ну вы же уточняли, что я подразумеваю, я подразумеваю общепринятое определение. То, что вы используете другое определение, не делает мои рассуждения неправильными.
Если общепринятое определение вам не подходит, вам надо создать полностью свою непротиворечивую систему терминов и предупреждать об этом, чтобы не вводить в заблуждение собеседников. Например вам нужно дать название тому, что создается при помощи new, но имеет 2 поля для идентификатора, пояснить, откуда в логической предметной области взялся какой-то оператор new, либо не использовать термины "сущность" и "объект" вне контекста языка программирования, и т.д.
У количества в документе мы состояние и поведение не выделяем, поэтому оно не сущность
Не понимаю в чем разница количества в документе и цены товара на складе. Кроме того что первый показатель от одного объекта, а второй от двух.
Если общепринятое определение вам не подходит, вам надо создать полностью свою непротиворечивую систему терминов и предупреждать об этом, чтобы не вводить в заблуждение собеседников
Мое соответствует классическому ООП. Там есть классы и их можно делать new. Все остальное методы / процедуры.
Например вам нужно дать название тому, что создается при помощи new, но имеет 2 поля для идентификатора
То что создается при помощи new имеет идентификатор — в C++ это адрес в памяти, в java внутренний id и т.п… А 2 поля идентификатора — это костыли ORM.
А вообще надо про это статью написать. В смысле Сущность vs Класс.
Не понимаю в чем разница количества в документе и цены товара на складе.
Не знаю, это у вас надо спросить, зачем вы сделали отдельную таблицу с изменением этой цены, да еще и отдельное представление по этой таблице. И почему для количества не сделали.
Мое соответствует классическому ООП. Там есть классы и их можно делать new.
И-и? Вот у нас в приложении не было строки из представления last_prices в базе данных, а вот появилась. Какой операцией она создается в оперативной памяти?
То что создается при помощи new имеет идентификатор — в C++ это адрес в памяти, в java внутренний id и т.п… А 2 поля идентификатора — это костыли ORM.
Да причем тут ORM-то?) У вас в коде нет ORM, а таблицы с двумя идентификаторами есть.
То что создается при помощи new имеет идентификатор — в C++ это адрес в памяти, в java внутренний id и т.п… А 2 поля идентификатора — это костыли ORM.
Сущность с двумя идентификаторами это связь, ее существование автоматически вытекает из существования нескольких сущностей. Если у нас есть 2 сущности, мы можем создать между ними связь. Будет ли она отражать реальную связь из предметной области, это другой вопрос.
Более того. Обычные связи один-ко-многим типа product.category_id, это частный случай связи с отдельной таблицей. И их точно так же можно моделировать отдельными таблицами с двумя столбцами, вообще все связи можно так моделировать, задавая соответствущие ограничения на столбцы. Просто так никто не делает по техническим причинам, просто помещают в ту таблицу, которая со стороны "много", и на другом конце связи получается всегда "один" объект. А для связи многие-ко-многим так упростить не получится.
По поводу C++. Покажите, как будет выглядеть структура классов, где есть авторы, книги, и их взаимосвязь, без работы с БД. Естественно с возможностью получить все книги конкретного автора и всех авторов конкретной книги.
Вот и надо говорить о проблемах и альтернативах. Возможно для кого-то некоторое снижение производительности будет приемлемо для уменьшения вероятности ошибки и увеличения удобности разработки.
Проблемы и альтернативы это разные вещи. Хотя в статье как раз есть много и про альтернативы (и как это сделано в lsFusion).
Пока писал уже ответили на остальное.
SELECT SUM(cc.ls)
FROM Product pr
LEFT JOIN (SELECT MAX(shipment) AS ls, s.product
FROM shipmentDetail s
GROUP BY s.product) cc ON cc.product=pr.id
WHERE pr.id = 1; -- name LIKE 'Product 86%';то есть, оно в принципе есть.
А как оно должно в вашем запросе работать, какой предикат протолкнется в подзапрос, если условие по имени продукта, а подзапрос про имена продуктов не знает?
Вот для сравнения запрос с lateral и план, работает только по отфильтрованным строкам.
SELECT SUM(cc.ls)
FROM Product pr
LEFT JOIN lateral ( SELECT MAX(shipment) AS ls
FROM shipmentDetail s
where s.product = pr.id
) cc on true
WHERE pr.name LIKE 'Product 86%';
то есть, оно в принципе есть.
Так она не join протолкнула, а значение. То есть PPD там есть, JPPD нет.
А как оно должно в вашем запросе работать, какой предикат протолкнется в подзапрос, если условие по имени продукта, а подзапрос про имена продуктов не знает?
Я в общем то в статье описал как это делают другие субд. Но по сути да, они вставляют что-то вроде lateral, как вы и написали.
Что кстати весьма забавно, что lateral PostgreSQL поддерживает, а JPPD нет.
То есть вот такой запрос:
SELECT SUM(cc.ls)
FROM Product pr
LEFT JOIN lateral ( SELECT SUM(shipment/1000) AS ls
FROM shipmentDetail s
where s.product = pr.id
) cc on true
;
Дает вот такой план:
"Aggregate (cost=40238877.72..40238877.73 rows=1 width=32) (actual time=18344.109..18344.109 rows=1 loops=1)" " -> Nested Loop Left Join (cost=804.71..40238752.71 rows=50001 width=8) (actual time=0.615..18335.300 rows=50001 loops=1)" " -> Seq Scan on product pr (cost=0.00..819.01 rows=50001 width=4) (actual time=0.014..12.254 rows=50001 loops=1)" " -> Aggregate (cost=804.71..804.72 rows=1 width=8) (actual time=0.365..0.366 rows=1 loops=50001)" " -> Bitmap Heap Scan on shipmentdetail s (cost=6.04..803.68 rows=207 width=4) (actual time=0.042..0.340 rows=200 loops=50001)" " Recheck Cond: (product = pr.id)" " Heap Blocks: exact=9986478" " -> Bitmap Index Scan on shipmentdetail_p_s (cost=0.00..5.99 rows=207 width=0) (actual time=0.020..0.020 rows=200 loops=50001)" " Index Cond: (product = pr.id)" "Planning Time: 0.194 ms" "Execution Time: 18344.192 ms"
А его то как раз эффективнее вот так выполнять:
SELECT SUM(cc.ls)
FROM Product pr
LEFT JOIN ( SELECT product, SUM(shipment/1000) AS ls
FROM shipmentDetail s
GROUP BY product
) cc on cc.product = pr.id
;
"Aggregate (cost=160706.41..160706.42 rows=1 width=32) (actual time=3355.234..3355.235 rows=1 loops=1)" " -> Hash Left Join (cost=159631.13..160581.41 rows=50001 width=8) (actual time=3324.236..3351.385 rows=50001 loops=1)" " Hash Cond: (pr.id = cc.product)" " -> Seq Scan on product pr (cost=0.00..819.01 rows=50001 width=4) (actual time=0.025..5.641 rows=50001 loops=1)" " -> Hash (cost=159027.97..159027.97 rows=48253 width=12) (actual time=3323.202..3323.202 rows=50000 loops=1)" " Buckets: 65536 Batches: 1 Memory Usage: 2661kB" " -> Subquery Scan on cc (cost=158062.91..159027.97 rows=48253 width=12) (actual time=3285.821..3308.588 rows=50000 loops=1)" " -> Finalize HashAggregate (cost=158062.91..158545.44 rows=48253 width=12) (actual time=3285.819..3301.673 rows=50000 loops=1)" " Group Key: s.product" " -> Gather (cost=147447.25..157580.38 rows=96506 width=12) (actual time=3168.262..3214.404 rows=150000 loops=1)" " Workers Planned: 2" " Workers Launched: 2" " -> Partial HashAggregate (cost=146447.25..146929.78 rows=48253 width=12) (actual time=3153.383..3168.983 rows=50000 loops=3)" " Group Key: s.product" " -> Parallel Seq Scan on shipmentdetail s (cost=0.00..115197.00 rows=4166700 width=8) (actual time=0.024..801.467 rows=3333334 loops=3)" "Planning Time: 0.383 ms" "Execution Time: 3358.020 ms"
То есть PostgreSQL не принимает никакого решения, а что сказали то и делаю.
В PostgreSQL формально наследование таблиц есть, но не более того. А учитывая, что смысла в наследовании без полиморфизма нет практически никакого, зачем в PostgreSQL наследование вообще добавляли — неясно.
Да есть пример в документации, где описано зачем
www.postgresql.org/docs/10/tutorial-inheritance.html
Вообще, если провести аналогию со структурным программированием, полиморфизм в SQL, по идее, должен был выглядеть как возможность создания абстрактного представления, в который можно добавлять различные UNION'ы в качестве реализации, то есть что-то вроде:
CREATE ABSTRACT VIEW detail (document LONG, quantity NUMBER);
EXTEND VIEW Detail
SELECT receipt AS document, quantity FROM receiptDetail;
…
EXTEND VIEW X
SELECT shipment AS document, quantity FROM shipmentDetail;
Храните базовую таблицу document. С полем amount например. От неё наследуйте receipt и shipment. В них будут уникальные поля для этих типов документов.
Так что всё делается, только «наоборот». И делается правильнее. т.к. БД это про хранение, и нечего делать «интерфейсы» выдавая одни поля за другие.
Плюс так можно делать при индуктивном задании логики. При дедуктивном, когда создаются таблицы со своими именами полей, а потом функционал надо обобщить в абстрактный так тоже не сработает.
Но вообще надо было конечно в примере GROUP BY написать или что-то по сложнее, чтобы вопросов не было.
Запросы (то есть VIEW) в реализацию добавлять нельзя (в смысле что VIEW наследовать от таблиц / друг от друга).
Ну может это для кого-то недостаток. Но вы пишите " зачем в PostgreSQL наследование вообще добавляли — неясно."
Хотя тут как раз наследование — отличная и незаменимая фича.
Есть люди, которые БД используют не так хардкорно. Может даже используют ORM. И их большинство.
View им в этом не нужны. А эта фича — как раз возможность хранить данные с наследованием. И именно структурированное хранение данных первичная фича базы. View же для тех, кто пытается и бизнеслогику реализовать на SQL, что часто тупиковый путь.
кто пытается и бизнеслогику реализовать на SQL, что часто тупиковый путь.
Ну об этом в общем-то и статья.
Вот с ORM как раз очень часто view нужны для статистики.
С тезисами статьи про особенности SQL я более или менее согласен. Действительно есть такие операции с данными, которые не ложатся на синтаксис SQL достаточно гладко или красиво. Как правило, в таких случаях применяются заметно более общие механизмы, чем оптимальные. И потому СУБД предоставляют некоторые расширения синтаксиса, чтобы решать частные задачи более подходящими алгоритмами.
Но я категорически не согласен с выводами статьи про то, что коммерческие системы не соответствуют чему-то там. Нельзя обобщать два неудачных случайных примера до всех коммерческих систем.
Но я категорически не согласен с выводами статьи про то, что коммерческие системы не соответствуют чему-то там. Нельзя обобщать два неудачных случайных примера до всех коммерческих систем.
Там в том то и дело что их больше чем 2. Я специально все собрал в одну статью, чтобы было видно что их гораздо больше, и что производители СУБД везде смогли накосячить.
Ну и я не говорил, что они чему-то не соответствуют. Тут вот какая ситуация. Смотрите мы когда делали платформу над SQL и компилировали запросы, к нам постоянно приходили разработчики и говорили вот мы тут такую логику нахерачили и у нас что-то тормозит. Ты им отвечаешь, смотрите вот видите запрос, видите план, видите как СУБД косячит. Знаете что они отвечали, а нам какая разница, чьи это проблемы, формируйте такие запросы, чтобы они выполнялись быстро. Понятно что это вопрос бренда. Будь у нас бренд Oracle или MS SQL мы бы всех посылали на три буквы и говорили бы, что «просто вы не так держите», крутитесь сами как хотите. Но нет, нам приходилось решать проблемы СУБД, мы конечно в конце концов все их решили, но сам факт почему это приходилось делать нам, хотя мы могли бы сфокусироваться именно на верхнем уровне, конечно дико раздражал. Собственно так и родилась эта статья.
Просто ради интереса, вы не оставили разработчикам возможности "нахерачить" тормозящую логику? Или оставили, но теперь у вас есть возможность предлагать оптимизации для этих случаев, потому что внутри больше нет "чужих" систем?
PS. Где бы почитать про систему менее маркетинговые материалы, а более технические? Пошарился на сайте и что-то не нашёл, где прочитать спеку вашего внутреннего аналога SQL.
Пошарился на сайте и что-то не нашёл, где прочитать спеку вашего внутреннего аналога SQL.Если речь про язык lsfusion, то вот documentation.lsfusion.org/pages/viewpage.action?pageId=1573050.
Спасибо, не сразу понял, что это и есть документация. Больше похоже на форум с сообщением от 2012 года. Еще приметил статейку с примерами в блоге компании. Почитаю 8)
Больше похоже на форум с сообщением от 2012 года.Хм, да, в мобильной версии эта страница сейчас выглядит не слишком понятно. В полной версии (desktop version) она больше похожа на стартовую страницу описания языка, с содержанием в виде дерева слева.
Еще приметил статейку с примерами в блоге компанииДа, статьи с названиями «Не очередной язык программирования. Часть N...» предполагались, как своеобразный tutorial, в них есть описание языка и примеры.
1) Большой кэш для предвычисленных затратных вещей на Redis. Обслуживание кэша не на SQL, а на прикладном языке.
ACID'а не будет, то есть подойдет только для аналитики по большому счету, во всяком случае в бизнес-приложениях.
2) Запуск длительных запросов в виде отдельных скриптов на других языках. Добавление прогрессбаров в конце концов.
Тоже самое что и в пункте 1). Хотя во многих случаях действительно помогает (в lsFusion это асинхронные события).
3) Решение проблемы наследования на уровне ORM — в PHP в Doctrine, точно можно делать красивое наследование. Затем же из PHP работать с сущностями и построитель запросов ORM сам соберет данные из нужных таблиц.
Там в ORM те же и проблемы что в Oracle будут (в статье есть), то есть N+1 и непонятно что делать с таблицами скажем с 3 ключами.
SQL не идеален, но он уже давно под капотом у других языков. И в целом системы, где используется деревянный SQL, сгенерированный ORM или неискушенными в ORM программистами легче поддерживать, чем базу со вьюшками, хранимками и прочими радостями.
Он все же обычно не под капотом, а сбоку. То есть у большинства других языков есть трансляторы в SQL, типа как в LINQ, Hibernate(HQL) или 1С.
Но и на голом SQL где очень важна производительность (банки, финансы, ритейл), тоже много чего пишут. Откуда по вашему у Oracle оборот 10млрд, не как у подложки же под SQL.
Собственно в свое время было много холиваров на тему SQL vs noSQL, и, соответственно, много историй, когда людям с noSQL на SQL приходилось переходить из-за невозможности сделать простейший JOIN.
Взять хоть задачу по пересчету остатков. Да ее можно решить и так. Но так делать можно только на очень низконагруженых системах. Даже на системе со средней нагрузкой это может породить проблемы. При это умалчивается, что в принципе в задаче пересчета остатков заложен конфликт, который приходится разрешать в любой системе. Или мы строго учитываем остатки, или быстро, при этом в быстро еще заложено или быстро пишем, или быстро читаем. Если нам нужно и строго и быстро, то необходимо вводить отдельную «подсистему», которая денормализует данные и будет работать именно с остатками. Но и тут можно придумать кейс при котором возникнут проблемы, которые на SQL не имеют качественного решения, это распределенное управление остатками, когда у вас множество источников остатков, часть с которых вообще могут быть вне вашего контроля( например системы поставщиков ), т.е. одну проблему, мы поменяли на другую, которая тоже рано или поздно потребует решения.
Но так делать можно только на очень низконагруженых системах. Даже на системе со средней нагрузкой это может породить проблемы.
А что в вашем понятии низконагруженные системы? Системы класса ERP с террабайтными базами и почти тысячей одновременных пользователей это какие системы? Вот у меня сейчас перед глазами с пяток таких систем, использующих описанный в статье подход.
Но тут вопрос то в другом. Остаток вам все равно надо хранить и обновлять инкрементально. Иначе система просто ляжет из-за нарушения баланса чтения и записи. Весь вопрос, как это делать. Задать декларативно представление и сказать серверу — теперь это твои проблемы, крутись как хочешь. Или самому разработчику императивно руками обновлять соответствующую таблицу. Не говоря о том что поддержать все случаи изменений очень тяжело, а значит нужно прибегать к разным хакам вроде запретов и перепроведений, о чем я и писал в статье.
При это умалчивается, что в принципе в задаче пересчета остатков заложен конфликт, который приходится разрешать в любой системе
Вы про update conflict'ы? Ну так с ними то как раз SQL сервера более менее сами справляются. От разработчика нужно только перестарт транзакции поддержать (хотя это тоже не такая тривиальная задача). Но это перпендикулярно задаче материализации представлений.
Или мы строго учитываем остатки, или быстро, при этом в быстро еще заложено или быстро пишем, или быстро читаем.
Проблема в том что пользователю надо и то и другое. А дальше вопрос балансов. Грубо говоря пользователь обычно согласен лишние 500мс подождать на проведении документа, но чтобы все остальное летало.
т.е. одну проблему, мы поменяли на другую
Почему поменяли? Решили одну. Ну а вторую значительно упростили. Так, достаточно обновлять поле «остаток у поставщика» как первичные данные в своей таблице, а дальше уже внутри сервер включает свою магию с материализациями, событиями и т.п.
У меня есть практический вопрос. Предположим, у меня есть проект с бизнес логикой на MS SQL. Возможно ли 'вместо MS SQL' использовать lsFusion + PostgreSQL (только как бек енд сервер)?
У меня есть практический вопрос. Предположим, у меня есть проект с бизнес логикой на MS SQL. Возможно ли 'вместо MS SQL' использовать lsFusion + PostgreSQL (только как бек енд сервер)?
Тут нюанс в том, что lsFusion работает на более высоком уровне абстрагирования — «функциональном». Теоретически его конечно можно было «опустить» вниз на реляционный, именно с целью подменять MS SQL и Oracle, но тогда растеряется куча других преимуществ, вроде логики представлений, и например явной типизации. Да и насильно «деградировать» это странновато (хотя с точки зрения market fit может и имеет смысл).
Вообще, как мне видится, у lsFusion есть два способа облегчить вход на рынок:
1. Подключить в качестве фронтенда популярные фреймворки вроде React и выйти на рынок чистой классической веб-разработки (как более мобильный рынок и восприимчивый к инновациям)
2. Сделать возможно подключаться к существующим БД: генерить код на lsFusion на основе структуры БД (тем самым давая логику представлений для существующей бд) + добавлять триггеры в БД для обеспечения инфраструктуры событий, материализаций, ограничений, агрегаций и т.п. То есть по сути заходить как как вирус постепенно пожирая старую логику на SQL и переводя ее на lsFusion. Эта задача как и первая тоже техническая и не такая уж и сложная.
Другое дело, что эти две задачи надо доделывать, в то же время текущий продукт уже достаточно цельный и мы пока попробуем прощупать рынок с ним. Сначала русскоязычный, но что важнее англоязычный. Второй возможно более мобильный и восприимчивый к инновациям (но туда непонятно точно как заходить), и только после того как оценим feedback на нем, будем решать что делать дальше и в каком направлении из верхних двух развиваться. Ну или вообще сфокусироваться на решениях (вроде как 1С выходил на рынок через решения для бухгалтерии, заходить через рынок FMCG розницы где у нас достаточно большой опыт и экспертиза и там на самом деле очень слабая конкуренция)
генерить код на lsFusion на основе структуры БД
Это очень правильная идея. Люди смогут взять существующий проект и сравнить реализацию одних и тех же задач. Возможно они не будут переделывать старый, но задумаются о том, чтобы на этой архитектуре сделать новый.
Технически это будет выглядеть как реализация FAST REFRESH в Oracle. Там на самом деле это как реализовано. Создаются таблицы Таблица$mlog (причем они именно что обычные таблицы, в частности видны в каталогах, в sql developer), и триггеры записывают туда старые значения при изменении. А затем в конце транзакции, генерятся запросы по обновлению таблиц представлений.
Соответственно здесь можно сделать также, lsFusion генерит триггеры для всех используемых таблиц и сохраняет изменения в Таблица$llog. Затем в конце каждой транзакции (тут надо смотреть есть ли такая точка входа у SQL серверов) вызывает цикл обработки событий lsFusion с «виртуальной» сессией изменений из этих $llog таблиц (тут тоже вопрос можно ли из sql сервера синхронно обратиться к внешнему серверу и по какому протоколу, но почти уверен что такая возможность есть). Соответственно lsFusion выполняет всю работу по обновлению материализаций, проверке ограничений (если ограничение нарушится то вся транзакция откатится с сообщением), обработке событий и т.п. При этом даже в старой логике можно спокойно обращаться к таблицам из lsFusion как родным (правда будет проблема что материализации обновляются в конце транзакции, и это нужно будет учитывать). Плюс lsFusion может создавать поля / обновлять данные в существующих таблицах. То есть интеграцию реально можно сделать практически бесшовной. И технически все выглядит достаточно просто.
Но это позволит обойти legacy только при «SQL-разработке». На рынке «ERP-разработки» (SAP/Axapta/1С) такой фокус не прокатит. И там придется идти напролом.
Статья понравилась. Но...
Корень основных проблем скорее во фразах типа "разработчик не знает что там выдумает бизнес"... Ну естественно, если у вас "во главе угла" аджайл с девизом "быстро счас наклепаем то что кажется поняли, а потом разберемся" - никакая БД вам не поможет сделать то что сами вы не знаете... :-)
И нужно четко понимать что "чудес не будет". Быстро, надежно, много и дешево одновременно не может быть "по определению", как бы вам этого не хотелось. Нужно искать балансы и определять приоритеты. И не нужно "пихать" в хранилище данных сырые xml и потом удивляться полученному результату (по производительности и/или ресурсам).
Если вы используете в своих sql запросах связки из десятков таблиц (особенно с изменяемыми периодически в них объемами данных и множестве индексов) не удивляйтесь что стоимостные оптимизаторы БД периодически "сходят с ума"... Лучше подумайте как лично вы (как разработчик) можете "облегчить им жизнь" чтобы "переваривать" терабайты данных.
Объекты это конечно хорошо (для разработчика), но "размазывать" их по множеству таблиц в БД - плохо, как и плохо делать выборку "объекта из базы" для получения в итоге одного хранимого в одной таблице поля.
Почему не SQL?