Информация об изменениях

Сообщение Re[16]: Бизнес логика в ХП от 26.06.2016 17:39

Изменено 26.06.2016 17:43 Gattaka

Здравствуйте, gandjustas, Вы писали:



G>Здравствуйте, Gattaka, Вы писали:

G>>Вот запрос:
G>
  Запрос
G>>

G>>    -- 1. Простовляем списку узлов новую роль
G>>        update Network_Node
G>>    set Property = true -- Некий признак, флаг, роль и т.п. не вдаемся в подробности. Нам пока не важно.
G>>    where Id in (select Id from @selectedNodes) --В хранимку приходит список идентификаторов выбранных пользователем узлов - табличная переменная @selectedNodes

G>>    -- 2. В соответсвии с бизнес логикой нужно найти связать сетевые узлы, которым мы проставили роль по связям зарегестрированных на них пользователей 
G>>    --    
G>>    insert Node_Node(Node1Id, Node2Id)
G>>    select distinct n.Id, un2.NodeId
G>>    from Network_Node n
G>>    join UserOnNode un1 on un1.NodeId = n.Id     -- Берем пользователей зарегестрированных на узле
G>>        join User_User uu on uu.User1Id = un1.UserId -- Берем связи пользователя
G>>        join UserOnNode un2 on un1.UserId = uu.User2Id -- Берем узлы на котором зарегестрирован связанный пользователь
G>>    where n.Property = true 
G>>          and not exists(select * from Node_Node nn3 where nn3.Node1Id = n.Id and nn3.Node2Id = un2.NodeId) -- такая связь еще не сущетвует

G>>


G>Кстати в запросе ошибка, смотри третий джоин


G>>Итак, напомню
G>>

G>>В таблице User(Id, Name) 70000 записей. В таблице Network_Node(Id, Name, Property) 70000 записей. В Таблице UserOnNode(NodeId, UserId) — 70000 записей по одному пользователю на узле. В таблице User_User(User1Id, User2Id) — связи пользователей, все польователи связаны со всеми — это 70000*70000=4900000000 записей. На каждую из записей по 8 байт, т.е. приблизительно 36 гигабайт табличка, не учитывая индексов. Теперь в таблице Network_Node(Node1Id, Node2Id) — связи узлов. Допустим кто-то с кем-то уже был связан в случайном порядке 900000000 записей каких-то.
G>>Нам нужно сделать: У узлов (35000 каких-то) Network_Node меняется свойство Property — бизнес логика такова, что нужно найти зарегестрированные на этих узлах пользовати и если они связаны — добавить связи по узлам.


G>Если все пользователи связаны со всеми, то установка property на любом узле приведет к добавлению Node_Node всех узлов в поле Node2Id. Что для дальнейшего использования не имеет смысла, так как просто можно запрашивать n.Property вместо джоина к Node_Node.

G>А если ты приведешь реальные средние значения количества связей User_Node и User_User, то перемножив два числа получим количество считываемых\добавляемых строк. Например для User_Node примерное соотношение 10, а для User_User — 150, то надо будет получить максимум 1500 записей для каждого ID. Размер записи — 8 байт (два ID по 4 байта). То есть 12кб. Если это операция обновления, которая не выполняется 10 раз в секунду, то можно не беспокоиться о скорости.
Считайте, что все со всеми минус 1000000 каких-то случайных связей между пользователями. Вот такие объемы. Те расчеты, что вы привели — оч. мало.

G>А вообще я бы лучше сделал материализованную view вида

G>
G>create view UserNodeJunction with schemabinding as 
G>select 
G>    un1.NodeId as Node1Id, 
G>    un1.UserId as User1Id, 
G>    un2.NodeId as Node2Id, 
G>    un2.UserId as User2Id 
G>from UserOnNode un1 
G>join User_User uu on uu.User1Id = un1.UserId -- Берем связи пользователя
G>join UserOnNode un2 on un2.UserId = uu.User2Id 

Ерунда :(, будет тормозить добавление и удаление связей.

G>create clustered index PK_UserNodeJunction on UserNodeJunction (Node1Id, User1Id, Node2Id, User2Id)
G>create index IX_NodeLink on UserNodeJunction (Node1Id, Node2Id)
G>


G>Тогда твой запрос вообще можно было бы во view превратить:
G>
G>create view NodeWithProperty as 
G>select distinct n.Id, j.Node2Id
G>from Network_Node n
G>join UserNodeJunction j on n.Id = j.Node1Id
G>where n.Property = true 
G>


Так джойн и так быстро делается, только при вставке связей вы получите дополнительные расходы. Плюс размер базы разратется в 2 раза. Если сейчас это 36 ГБ табличка — будет 72 ГБ.

G>И если это view индексировать (вытащив distinct на уровень выше), то вовсе пропадет необходимость что-то куда-то вставлять.

Нет, я вам просто привел упрощенный пример. Вставлять нужно железно, от этого к сожалению никуда не уйти. Было бы супер не вставлять, но печаль... Во-первых тут не одно свойство может приводить к такому, во вторых даже если в результате бага связи попали — исчизать не должны. И масса еще другого...

G>ЗЫ. Тем не менее я понял твою идею — тяжелую логику держать на уровне SQL, а не тащит в приложение. И я с этим согласен. Но средства ты кардинально не те выбрал, как на уровне базы, так и на уровне приложения. Поэтому ничего толком обосновать не можешь.
Ну супер! На самом деле я ожидал что вы dapper предложете или linq2db, но тоже не катит, т.к. работу с блокировками в многопользовательском режиме нужно возложить на SQL, в частности READPAST и т.п. подобные штуки понадабятся...
Re[16]: Бизнес логика в ХП
Здравствуйте, gandjustas, Вы писали:



G>Здравствуйте, Gattaka, Вы писали:

G>>Вот запрос:
G>
  Запрос
G>>

G>>    -- 1. Простовляем списку узлов новую роль
G>>        update Network_Node
G>>    set Property = true -- Некий признак, флаг, роль и т.п. не вдаемся в подробности. Нам пока не важно.
G>>    where Id in (select Id from @selectedNodes) --В хранимку приходит список идентификаторов выбранных пользователем узлов - табличная переменная @selectedNodes

G>>    -- 2. В соответсвии с бизнес логикой нужно найти связать сетевые узлы, которым мы проставили роль по связям зарегестрированных на них пользователей 
G>>    --    
G>>    insert Node_Node(Node1Id, Node2Id)
G>>    select distinct n.Id, un2.NodeId
G>>    from Network_Node n
G>>    join UserOnNode un1 on un1.NodeId = n.Id     -- Берем пользователей зарегестрированных на узле
G>>        join User_User uu on uu.User1Id = un1.UserId -- Берем связи пользователя
G>>        join UserOnNode un2 on un1.UserId = uu.User2Id -- Берем узлы на котором зарегестрирован связанный пользователь
G>>    where n.Property = true 
G>>          and not exists(select * from Node_Node nn3 where nn3.Node1Id = n.Id and nn3.Node2Id = un2.NodeId) -- такая связь еще не сущетвует

G>>


G>Кстати в запросе ошибка, смотри третий джоин


G>>Итак, напомню
G>>

G>>В таблице User(Id, Name) 70000 записей. В таблице Network_Node(Id, Name, Property) 70000 записей. В Таблице UserOnNode(NodeId, UserId) — 70000 записей по одному пользователю на узле. В таблице User_User(User1Id, User2Id) — связи пользователей, все польователи связаны со всеми — это 70000*70000=4900000000 записей. На каждую из записей по 8 байт, т.е. приблизительно 36 гигабайт табличка, не учитывая индексов. Теперь в таблице Network_Node(Node1Id, Node2Id) — связи узлов. Допустим кто-то с кем-то уже был связан в случайном порядке 900000000 записей каких-то.
G>>Нам нужно сделать: У узлов (35000 каких-то) Network_Node меняется свойство Property — бизнес логика такова, что нужно найти зарегестрированные на этих узлах пользовати и если они связаны — добавить связи по узлам.


G>Если все пользователи связаны со всеми, то установка property на любом узле приведет к добавлению Node_Node всех узлов в поле Node2Id. Что для дальнейшего использования не имеет смысла, так как просто можно запрашивать n.Property вместо джоина к Node_Node.

G>А если ты приведешь реальные средние значения количества связей User_Node и User_User, то перемножив два числа получим количество считываемых\добавляемых строк. Например для User_Node примерное соотношение 10, а для User_User — 150, то надо будет получить максимум 1500 записей для каждого ID. Размер записи — 8 байт (два ID по 4 байта). То есть 12кб. Если это операция обновления, которая не выполняется 10 раз в секунду, то можно не беспокоиться о скорости.
Считайте, что все со всеми минус 1000000 каких-то случайных связей между пользователями. Вот такие объемы. Те расчеты, что вы привели — оч. мало.

G>А вообще я бы лучше сделал материализованную view вида

G>
G>create view UserNodeJunction with schemabinding as 
G>select 
G>    un1.NodeId as Node1Id, 
G>    un1.UserId as User1Id, 
G>    un2.NodeId as Node2Id, 
G>    un2.UserId as User2Id 
G>from UserOnNode un1 
G>join User_User uu on uu.User1Id = un1.UserId -- Берем связи пользователя
G>join UserOnNode un2 on un2.UserId = uu.User2Id 

Ерунда :(, будет тормозить добавление и удаление связей.

G>create clustered index PK_UserNodeJunction on UserNodeJunction (Node1Id, User1Id, Node2Id, User2Id)
G>create index IX_NodeLink on UserNodeJunction (Node1Id, Node2Id)
G>


G>Тогда твой запрос вообще можно было бы во view превратить:
G>
G>create view NodeWithProperty as 
G>select distinct n.Id, j.Node2Id
G>from Network_Node n
G>join UserNodeJunction j on n.Id = j.Node1Id
G>where n.Property = true 
G>


Так джойн и так быстро делается, только при вставке связей вы получите дополнительные расходы. Плюс размер базы разратется в 2 раза. Если сейчас это 36 ГБ табличка — будет 72 ГБ.

G>И если это view индексировать (вытащив distinct на уровень выше), то вовсе пропадет необходимость что-то куда-то вставлять.

Нет, я вам просто привел упрощенный пример. Вставлять нужно железно, от этого к сожалению никуда не уйти. Было бы супер не вставлять, но печаль... Во-первых тут не одно свойство может приводить к такому, во вторых даже если в результате бага связи попали — исчизать не должны. И масса еще другого...

G>ЗЫ. Тем не менее я понял твою идею — тяжелую логику держать на уровне SQL, а не тащит в приложение. И я с этим согласен. Но средства ты кардинально не те выбрал, как на уровне базы, так и на уровне приложения. Поэтому ничего толком обосновать не можешь.
Ну супер! На самом деле я ожидал что вы dapper предложете или linq2db, но тоже не катит, т.к. работу с блокировками в многопользовательском режиме лучше возложить на SQL, в частности READPAST и т.п. подобные штуки понадобятся...