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

Откуда: Гималай
Сообщений: 2101
Приветствую всех.
Есть задачка, ищу оптимальное решение.
Сразу скажу, База крутится на SQL Server 2008 R2
Есть вложенная структура клиентов, т.е. есть клиентов К1, у него могут быть подчиненные клиенты, к примеру К2, у этого еще могут быть другие подчиненные клиенты и так до бесконечности.
Необходимо "построить" правильную структуру учета долгов этих клиентов.
При продаже определенной услуги, определенному клиенту, чтобы изменение баланса (в зависимости от стоимости) менялась начиная от клиента, которому оказывают услугу до самого главного его родителя.
Если бы частота добавления записей в таблицу продаж не было такой частой как сейчас, я бы не искал решение а "наляпал" бы какое-нибудь решение. Но именно из-за того, что количество клиентов может превысит 10 тысяч записей, а количество одновременных записей в течение 10ти секунд может превысить более 30 записей, ищу наиболее оптимальное решение, с "минимальными" случаями deadlock'ов и т.д и самое главное чтобы долг рассчитывался правильно.
Теперь описываю структуру таблиц, параллельно логику еще. Есть таблица клиентов:
TblUsers
ID
ParentUser_ID
UserName
Balance


думаю ясно назначеение полей, ParentUser_ID указывает на родителя, если значение равно -1, то это основной клиент.
При этом, как уже сказал, у любого клиента могут быть любое количество вложенных подчиненных клиентов, уровень вложенности может превысить 50ый или 80ый уровень. Назначение остальных полей, думаю что ясно по названию.
Таблица продаж:
TblSells
ID
User_ID
DT
SellSum
Status


DT - дата/время продажи, SellSum - сумма продажи, Status - статус продажи: 0 - продажа не обработана, 1 - продажа обрабатывается, 2 - продажа обработана, 3 - продажа отменена
Если статус записи равен значениям: 0, 1 или 2 небходимо уменьшить значение Balance клиента, и всего его родителей, если же 3 - то наоборот. При этом при добавлении новых значений в таблицу TblSells Status может принимать любые значения.

Как уже сказал, уровень вложенности может быть любым, при этом при продаже одному клиенту, баланс должен меняться в обратном иреархическом порядке: меняется баланс пользователя, его родителя, если у его родителя есть еще родитель, то его родителя и т.д. и т.д. до пользователя самого верхнего уровня, баланс пользователя самого верхнего уровня не меняется.
И если представить что количество одновременных добавлений в таблицу продаж может превышать 30, то нужно будет минимизировать возникновение мертвых блокировок, при иреархическом обновлении.

Для этого, я решил применить следующий вариант, создаю таблицу TblUsersTree дерево подчинения пользователей:
TblUsersTree
User_ID
TreeUser_ID
ParentUser_ID
LevelNum


User_ID - ID пользователя
TreeUser_ID - ID пользователя
ParentUser_ID - ID родителя пользователя TreeUser_ID

к примеру если есть следующая структура (во вложении).
То данные в таблице TblUsersTree будут следующими:
User_ID TreeUser_ID ParentUser_ID LevelNum
2 2 1 0
3 3 1 0
4 2 1 1
4 4 2 0
5 2 1 2
5 4 2 1
5 5 4 0
6 2 1 2
6 4 2 1
6 6 4 0
7 3 3 1
7 7 3 0
8 2 1 3
8 4 4 2
8 6 4 1
8 8 6 0


Эти данные заполняются автоматом при добавлении нового пользователя и удаляются при удалении, делается через триггер:
CREATE TRIGGER [dbo].[TblUsers_INSERT] ON [dbo].[TblUsers]
	AFTER INSERT
AS
BEGIN
	SET NOCOUNT ON;
	WITH parents (User_ID, UserID, ParentUser_ID, LevelNum)
	AS
	(
		SELECT
			ins.ID, ins.ID, ins.Parent_ID, 0
		FROM
			inserted ins
		WHERE
			ins.Parent_ID<>-1
		UNION ALL
		SELECT
			p.User_ID, us.ID, us.Parent_ID, (LevelNum+1)
		FROM
			TblUsers us INNER JOIN
			parents p ON us.ID=p.ParentUser_ID
		WHERE
			us.Parent_ID<>-1
	)
	
	INSERT INTO TblUsersTree (User_ID, TreeUser_ID, ParentUser_ID, LevelNum)
	SELECT
		p.User_ID, p.UserID, p.ParentUser_ID, p.LevelNum
	FROM
		parents p
	ORDER BY
		UserID, LevelNum
END

и на удаление:
CREATE TRIGGER [dbo].[TblUsers_DELETE] ON [dbo].[TblUsers]
	AFTER DELETE
AS
BEGIN
	SET NOCOUNT ON;
	DELETE TblUsersTree WHERE User_ID IN (SELECT ID FROM deleted)
END

Данная таблица используется при обновлении баланса клиентов, когда добавляется новое значение в таблицу продаж. Структура таблицы продаж TblSells:
TblSells
User_ID
DT
SellSum
Status


Триггер на добавление записей в таблицу TblSells:
CREATE TRIGGER [dbo].[TblSells_INSERT] ON [dbo].[TblSells]
	AFTER INSERT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE
		@ut TABLE (
			User_ID bigint,
			SellSum money
		)
	
	INSERT INTO @ut (User_ID, SellSum)
	SELECT
		ut.TreeUser_ID, ins.SellSum
	FROM
		inserted ins INNER JOIN
		TblUsersTree ut ON ins.User_ID=ut.User_ID
	WHERE
		ins.Status IN (0,1,2)
	ORDER BY
		ut.LevelNum
	
	UPDATE
		TblUsers
	SET
		Balance=Balance+ut.SellSum
	FROM
		@ut ut INNER JOIN
		TblUsers us ON ut.User_ID=us.ID
END

CREATE TRIGGER [dbo].[TblSells_DELETE] ON [dbo].[TblSells]
	AFTER DELETE
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE
		@ut TABLE (
			User_ID bigint,
			SellSum money
		)
	
	INSERT INTO @ut (User_ID, SellSum)
	SELECT
		ut.TreeUser_ID, del.SellSum
	FROM
		deleted del INNER JOIN
		TblUsersTree ut ON del.User_ID=ut.User_ID
	WHERE
		del.Status IN (0,1,2)
	ORDER BY
		ut.LevelNum
	
	UPDATE
		TblUsers
	SET
		Balance=Balance-ut.SellSum
	FROM
		@ut ut INNER JOIN
		TblUsers us ON ut.User_ID=us.ID
END
Попробую объяснить что я хотел реализовать в этом триггере. Для минимизации возникновения deadlock'ов, я обновляю балансы клиентов начиная с самого низа, из-за этого и использую локальную табличную переменную, которую заполняю в нужно мне порядке и затем обновляю баланс клиентов, связав с этой переменной.

Давно "бьюсь" над этой проблемой, в поисках оптимального решения.
Пожалуйста, покритикуйте выбранный "путь". Заранее благодарю за любую помощь ;)


Вот скрипты для создания таблиц и заполнения тестовыми данными:
CREATE TABLE [dbo].[TblUsers](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[Parent_ID] [bigint] NOT NULL,
	[UserName] [nvarchar](200) NOT NULL,
	[Balance] [money] NOT NULL,
 CONSTRAINT [PK_TblUsers] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[TblUsersTree](
	[User_ID] [bigint] NOT NULL,
	[TreeUser_ID] [bigint] NOT NULL,
	[ParentUser_ID] [bigint] NOT NULL,
	[LevelNum] [int] NOT NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[TblSells](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[User_ID] [bigint] NOT NULL,
	[DT] [datetime] NOT NULL,
	[SellSum] [money] NOT NULL,
	[Status] [tinyint] NOT NULL,
 CONSTRAINT [PK_TblDocCharges] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

INSERT INTO TblUsers (Parent_ID, UserName, Balance)
SELECT -1, N'Администратор', 0
INSERT INTO TblUsers (Parent_ID, UserName, Balance)
SELECT 1, N'U2', 0
INSERT INTO TblUsers (Parent_ID, UserName, Balance)
SELECT 1, N'U3', 0
INSERT INTO TblUsers (Parent_ID, UserName, Balance)
SELECT 2, N'U4', 0
INSERT INTO TblUsers (Parent_ID, UserName, Balance)
SELECT 4, N'U5', 0
INSERT INTO TblUsers (Parent_ID, UserName, Balance)
SELECT 4, N'U6', 0
INSERT INTO TblUsers (Parent_ID, UserName, Balance)
SELECT 3, N'U7', 0
INSERT INTO TblUsers (Parent_ID, UserName, Balance)
SELECT 6, N'U8', 0
7 июн 11, 21:48    [10779242]     Ответить | Цитировать Сообщить модератору
 Re: Система учета продаж: Вложенная структура клиентов  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
автор
Как уже сказал, уровень вложенности может быть любым, при этом при продаже одному клиенту, баланс должен меняться в обратном иреархическом порядке: меняется баланс пользователя, его родителя, если у его родителя есть еще родитель, то его родителя и т.д. и т.д. до пользователя самого верхнего уровня, баланс пользователя самого верхнего уровня не меняется.
И если представить что количество одновременных добавлений в таблицу продаж может превышать 30, то нужно будет минимизировать возникновение мертвых блокировок, при иреархическом обновлении.


У Вас есть реальные графы дедлоков? Можно их посмотреть? Ибо указанный Вами пересчет, это одна инструкция UPDATE с использованием раскрытой с помощью CTE иерархии вверх, исключая последний уровень, с хинтом UPDLOCK.

Сообщение было отредактировано: 7 июн 11, 22:28
7 июн 11, 22:08    [10779312]     Ответить | Цитировать Сообщить модератору
 Re: Система учета продаж: Вложенная структура клиентов  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
pkarklin
автор
Как уже сказал, уровень вложенности может быть любым, при этом при продаже одному клиенту, баланс должен меняться в обратном иреархическом порядке: меняется баланс пользователя, его родителя, если у его родителя есть еще родитель, то его родителя и т.д. и т.д. до пользователя самого верхнего уровня, баланс пользователя самого верхнего уровня не меняется.
И если представить что количество одновременных добавлений в таблицу продаж может превышать 30, то нужно будет минимизировать возникновение мертвых блокировок, при иреархическом обновлении.


У Вас есть реальные графы дедлоков? Можно их посмотреть? Ибо указанный Вами пересчет, это одна инструкция UPDATE с использованием раскрытой с помощью CTE иерархии вверх, исключая последний уровень, с хинтом UPDLOCK.


реальных графов нету, т.е. дедлоков еще не было вообще, система только проектируется, в обновлении CTE же не используется, CTE используется для формирования дерева подчинения пользователей, при добавлении нового пользователя
и затем это дерево используется в обновлении баланса пользователей, вот опишу сам код обновления баланса:

1. в триггере добавления новой записи, заполняется локальная табличная переменная с порядком чтобы обновить балансы снизу вверх
	DECLARE
		@ut TABLE (
			User_ID bigint,
			SellSum money
		)
	
	INSERT INTO @ut (User_ID, SellSum)
	SELECT
		ut.TreeUser_ID, ins.SellSum
	FROM
		inserted ins INNER JOIN
		TblUsersTree ut ON ins.User_ID=ut.User_ID
	WHERE
		ins.Status IN (0,1,2)
	ORDER BY
		ut.LevelNum

2. и собственно само обновление
	UPDATE
		TblUsers
	SET
		Balance=Balance+ut.SellSum
	FROM
		@ut ut INNER JOIN
		TblUsers us ON ut.User_ID=us.ID

и я не могу решить пока что проблему обновления записей в таблице TblSells, когда обновляются значения поля Status
к примеру: продан - отменен, отменен - продан и т.д.
8 июн 11, 06:20    [10780098]     Ответить | Цитировать Сообщить модератору
 Re: Система учета продаж: Вложенная структура клиентов  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
а вот и код триггера на обновление, изменяющая баланс клиентов, если меняется статус продаж:
CREATE TRIGGER [dbo].[TblSells_UPDATE] ON [dbo].[TblSells]
	AFTER UPDATE
AS
BEGIN
	SET NOCOUNT ON;
	IF EXISTS (SELECT * FROM inserted ins INNER JOIN deleted del ON ins.ID=del.ID WHERE ins.Status <> del.Status) BEGIN
		DECLARE
			@ut TABLE (
				User_ID bigint,
				SellSum money
			)
		INSERT INTO @ut (User_ID, SellSum)
		SELECT
			ut.TreeUser_ID, (
				CASE
					WHEN ((ins.Status IN (0,1,2)) AND (del.Status IN (3))) THEN ins.SellSum
					WHEN ((ins.Status IN (3)) AND (del.Status IN (0,1,2))) THEN -ins.SellSum
					ELSE 0
				END
			)
		FROM
			inserted ins INNER JOIN
			deleted del ON ins.ID=del.ID INNER JOIN
			TblUsersTree ut ON ins.User_ID=ut.User_ID
		WHERE
			ins.Status <> del.Status
		ORDER BY
			ut.LevelNum

		UPDATE
			TblUsers
		SET
			Balance=Balance+ut.SellSum
		FROM
			@ut ut INNER JOIN
			TblUsers us ON ut.User_ID=us.ID
	END
END
8 июн 11, 07:48    [10780152]     Ответить | Цитировать Сообщить модератору
 Re: Система учета продаж: Вложенная структура клиентов  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
up
8 июн 11, 11:13    [10781166]     Ответить | Цитировать Сообщить модератору
 Re: Система учета продаж: Вложенная структура клиентов  [new]
Леша777
Guest
Я бы вам посоветовал не пересчитывать баланс у всего дерева.

Добавить таблицу - дерево (где нужно хранить всех предков (до корня) текущего клиента включая самого себя).

DECLARE @User TABLE (UserID INT PRIMARY KEY, ParentUserID INT NULL, Balance MONEY DEFAULT (0)) 

DECLARE @Tree TABLE (UserID INT NOT NULL, ParentUserID INT NOT NULL , PRIMARY KEY (UserID, ParentUserID))

INSERT INTO @User
                 (
	                UserID
	               ,ParentUserID
                 )
SELECT 1, NULL 

UNION ALL 

SELECT 2, 1

UNION ALL

SELECT 3, 1 

UNION ALL 

SELECT 4, 2

UNION ALL
 
SELECT 5, 3 

INSERT INTO @Tree(UserID, ParentUserID)
SELECT 1, 1
UNION ALL
SELECT 2, 2
UNION ALL
SELECT 2, 1
UNION ALL
SELECT 3, 3 
UNION ALL 
SELECT 3, 1 
UNION ALL
SELECT 4, 4 
UNION ALL
SELECT 4, 2 
UNION ALL
SELECT 4, 1 
UNION ALL 
SELECT 5, 5 
UNION ALL
SELECT 5, 3 
UNION ALL 
SELECT 5, 1 


При добвавлении, удалении, редактировании операции - изменять баланс только у самого клиента. Добавить индекс на дерево (ParentUserID, UserID) .

Нахождение баланса :

DECLARE @UserID INT 

SELECT SUM(u.Balance)
FROM @Tree AS t 
JOIN @User AS u 
  ON u.UserID = t.UserID
WHERE t.ParentUserID = @UserID 

Агрегация 60-80 записей (ваш макс уровень вложенности) - не столь дорогая операция.
8 июн 11, 12:15    [10781806]     Ответить | Цитировать Сообщить модератору
 Re: Система учета продаж: Вложенная структура клиентов  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
Леша777,

тоже отличная идея! надо "взвесить" за и против
8 июн 11, 20:11    [10785824]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить