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

Откуда: Київ
Сообщений: 10428
Есть старые добрые примеры Ицыка

-- 1 
SET NOCOUNT ON;
USE tempdb;
GO
IF OBJECT_ID('dbo.Employees', 'U') IS NOT NULL
  DROP TABLE dbo.Employees;
GO
CREATE TABLE dbo.Employees
(
  empid   INT         NOT NULL,
  hid     HIERARCHYID NOT NULL,
  lvl AS hid.GetLevel() PERSISTED,
  empname VARCHAR(25) NOT NULL,
  salary  MONEY       NOT NULL,
  CONSTRAINT PK_Employees PRIMARY KEY NONCLUSTERED(empid)
);

CREATE UNIQUE CLUSTERED INDEX idx_depth_first ON dbo.Employees(hid);
CREATE UNIQUE INDEX idx_breadth_first ON dbo.Employees(lvl, hid);


-- Web Listing 2: Script to Create Stored Procedure usp_AddEmp
IF OBJECT_ID('dbo.usp_AddEmp', 'P') IS NOT NULL
  DROP PROC dbo.usp_AddEmp;
GO
CREATE PROC dbo.usp_AddEmp
  @empid   AS INT,
  @mgrid   AS INT = NULL,
  @empname AS VARCHAR(25),
  @salary  AS MONEY
AS

DECLARE
  @hid            AS HIERARCHYID,
  @mgr_hid        AS HIERARCHYID,
  @last_child_hid AS HIERARCHYID;

IF @mgrid IS NULL
  SET @hid = HIERARCHYID::GetRoot();
ELSE
BEGIN
  SET @mgr_hid = (SELECT hid FROM dbo.Employees WHERE empid = @mgrid);
  SET @last_child_hid =
    (SELECT MAX(hid) FROM dbo.Employees
     WHERE hid.GetAncestor(1) = @mgr_hid);
  SET @hid = @mgr_hid.GetDescendant(@last_child_hid, NULL);
END

INSERT INTO dbo.Employees(empid, hid, empname, salary)
  VALUES(@empid, @hid, @empname, @salary);
GO

--------------------
EXEC dbo.usp_AddEmp
  @empid =  1, @mgrid = NULL, @empname = 'David'  , @salary = $10000.00;
EXEC dbo.usp_AddEmp
  @empid =  2, @mgrid =    1, @empname = 'Eitan'  , @salary = $7000.00;
EXEC dbo.usp_AddEmp
  @empid =  3, @mgrid =    1, @empname = 'Ina'    , @salary = $7500.00;
EXEC dbo.usp_AddEmp
  @empid =  4, @mgrid =    2, @empname = 'Seraph' , @salary = $5000.00;
EXEC dbo.usp_AddEmp
  @empid =  5, @mgrid =    2, @empname = 'Jiru'   , @salary = $5500.00;
EXEC dbo.usp_AddEmp
  @empid =  6, @mgrid =    2, @empname = 'Steve'  , @salary = $4500.00;
EXEC dbo.usp_AddEmp
  @empid =  7, @mgrid =    3, @empname = 'Aaron'  , @salary = $5000.00;
EXEC dbo.usp_AddEmp
  @empid =  8, @mgrid =    5, @empname = 'Lilach' , @salary = $3500.00;
EXEC dbo.usp_AddEmp
  @empid =  9, @mgrid =    7, @empname = 'Rita'   , @salary = $3000.00;
EXEC dbo.usp_AddEmp
  @empid = 10, @mgrid =    5, @empname = 'Sean'   , @salary = $3000.00;
EXEC dbo.usp_AddEmp
  @empid = 11, @mgrid =    7, @empname = 'Gabriel', @salary = $3000.00;
EXEC dbo.usp_AddEmp
  @empid = 12, @mgrid =    9, @empname = 'Emilia' , @salary = $2000.00;
EXEC dbo.usp_AddEmp
  @empid = 13, @mgrid =    9, @empname = 'Michael', @salary = $2000.00;
EXEC dbo.usp_AddEmp
  @empid = 14, @mgrid =    9, @empname = 'Didi'   , @salary = $1500.00;



То есть добавляем по одному чилду за раз.

Вопрос: есть N чилдов скажем в таблице переменной, надо их добавить к указанному паренту, это все братья на одном уровне.

 declare @toadd table
 (
	empid   INT,
	mgrid   INT,
	empname VARCHAR(25),
	salary  MONEY
)

insert into @toadd(empid, mgrid, empname, salary)
select 15, 14, 'vasya', 0.01
union all
select 16, 14, 'petya', 0.02
union all
select 17, 14, 'manya', 0.03
union all
select 18, 14, 'kolya', 0.04
union all
select 19, 14, 'grish', 0.05


Можно сделать курсор по таблице, на каждую запись вызывать процедуру добавления.

Ho rак сделать оптимальнее, быстрее, без курсора?
13 окт 14, 17:12    [16698215]     Ответить | Цитировать Сообщить модератору
 Re: HierarchyID: как оптимально добавить множество чилдов?  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1873
можно без процедуры и курсора рекурсивным запросом попробовать
;with childlist(empid, mgrid, empname, salary,parenthid,hid) AS
(select t.empid, t.mgrid, t.empname, t.salary,e.hid as parenthid,e.hid.GetDescendant(lasthid, NULL)  as hid
from @toadd t
join dbo.Employees e on e.empid = t.mgrid
cross apply (SELECT MAX(hid) as lasthid FROM dbo.Employees WHERE hid.GetAncestor(1) = e.hid ) c
where not exists(select * from @toadd where empid < t.empid)
union all 
select t.empid, t.mgrid, t.empname, t.salary,c.parenthid,c.parenthid.GetDescendant(c.hid, NULL)  as hid
from @toadd t
join childlist  c on t.empid = c.empid+1
) 
insert into dbo.Employees(empid, hid, empname, salary)
select empid, hid, empname, salary from childlist 
14 окт 14, 07:34    [16700044]     Ответить | Цитировать Сообщить модератору
 Re: HierarchyID: как оптимально добавить множество чилдов?  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
2 LexusR

спасибо, так работает.

Но здесь испольузется то, что упорядочен список

а если так - не рабоатет.

 declare @toadd table
 (
	empid   INT,
	mgrid   INT,
	empname VARCHAR(25),
	salary  MONEY
)

insert into @toadd(empid, mgrid, empname, salary)
select 415, 5, 'vasya', 0.01
union all
select 316, 5, 'petya', 0.02
union all
select 117, 5, 'manya', 0.03
union all
select 218, 5, 'kolya', 0.04
union all
select 119, 5, 'grish', 0.05
14 окт 14, 19:16    [16703901]     Ответить | Цитировать Сообщить модератору
 Re: HierarchyID: как оптимально добавить множество чилдов?  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1873
поправить условие джойна не судьба ?
;with childlist(empid, mgrid, empname, salary,parenthid,hid) AS
(select t.empid, t.mgrid, t.empname, t.salary,e.hid as parenthid,e.hid.GetDescendant(lasthid, NULL)  as hid
from @toadd t
join dbo.Employees e on e.empid = t.mgrid
cross apply (SELECT MAX(hid) as lasthid FROM dbo.Employees WHERE hid.GetAncestor(1) = e.hid ) c
where not exists(select * from @toadd where empid < t.empid)
union all 
select t.empid, t.mgrid, t.empname, t.salary,c.parenthid,c.parenthid.GetDescendant(c.hid, NULL)  as hid
from @toadd t
join childlist  c on t.empid > c.empid
and not exists(select * from @toadd where empid > c.empid and empid < t.empid)
) 
insert into dbo.Employees(empid, hid, empname, salary)
select empid, hid, empname, salary from childlist 
15 окт 14, 07:09    [16705368]     Ответить | Цитировать Сообщить модератору
 Re: HierarchyID: как оптимально добавить множество чилдов?  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1873
а вообще можно без рекурсии и вообще для вставки узлов от разных парентов
SET NOCOUNT ON;
USE tempdb;
GO
IF OBJECT_ID('dbo.Employees', 'U') IS NOT NULL
  DROP TABLE dbo.Employees;
GO
CREATE TABLE dbo.Employees
(
  empid   INT         NOT NULL,
  hid     HIERARCHYID NOT NULL,
  lvl AS hid.GetLevel() PERSISTED,
  empname VARCHAR(25) NOT NULL,
  salary  MONEY       NOT NULL,
  CONSTRAINT PK_Employees PRIMARY KEY NONCLUSTERED(empid)
);

CREATE UNIQUE CLUSTERED INDEX idx_depth_first ON dbo.Employees(hid);
CREATE UNIQUE INDEX idx_breadth_first ON dbo.Employees(lvl, hid);


-- Web Listing 2: Script to Create Stored Procedure usp_AddEmp
IF OBJECT_ID('dbo.usp_AddEmp', 'P') IS NOT NULL
  DROP PROC dbo.usp_AddEmp;
GO
CREATE PROC dbo.usp_AddEmp
  @empid   AS INT,
  @mgrid   AS INT = NULL,
  @empname AS VARCHAR(25),
  @salary  AS MONEY
AS

DECLARE
  @hid            AS HIERARCHYID,
  @mgr_hid        AS HIERARCHYID,
  @last_child_hid AS HIERARCHYID;

IF @mgrid IS NULL
  SET @hid = HIERARCHYID::GetRoot();
ELSE
BEGIN
  SET @mgr_hid = (SELECT hid FROM dbo.Employees WHERE empid = @mgrid);
  SET @last_child_hid =
    (SELECT MAX(hid) FROM dbo.Employees
     WHERE hid.GetAncestor(1) = @mgr_hid);
  SET @hid = @mgr_hid.GetDescendant(@last_child_hid, NULL);
END

INSERT INTO dbo.Employees(empid, hid, empname, salary)
  VALUES(@empid, @hid, @empname, @salary);
GO

--------------------
EXEC dbo.usp_AddEmp
  @empid =  1, @mgrid = NULL, @empname = 'David'  , @salary = $10000.00;
EXEC dbo.usp_AddEmp
  @empid =  2, @mgrid =    1, @empname = 'Eitan'  , @salary = $7000.00;
EXEC dbo.usp_AddEmp
  @empid =  3, @mgrid =    1, @empname = 'Ina'    , @salary = $7500.00;
EXEC dbo.usp_AddEmp
  @empid =  4, @mgrid =    2, @empname = 'Seraph' , @salary = $5000.00;
EXEC dbo.usp_AddEmp
  @empid =  5, @mgrid =    2, @empname = 'Jiru'   , @salary = $5500.00;
EXEC dbo.usp_AddEmp
  @empid =  6, @mgrid =    2, @empname = 'Steve'  , @salary = $4500.00;
EXEC dbo.usp_AddEmp
  @empid =  7, @mgrid =    3, @empname = 'Aaron'  , @salary = $5000.00;
EXEC dbo.usp_AddEmp
  @empid =  8, @mgrid =    5, @empname = 'Lilach' , @salary = $3500.00;
EXEC dbo.usp_AddEmp
  @empid =  9, @mgrid =    7, @empname = 'Rita'   , @salary = $3000.00;
EXEC dbo.usp_AddEmp
  @empid = 10, @mgrid =    5, @empname = 'Sean'   , @salary = $3000.00;
EXEC dbo.usp_AddEmp
  @empid = 11, @mgrid =    7, @empname = 'Gabriel', @salary = $3000.00;
EXEC dbo.usp_AddEmp
  @empid = 12, @mgrid =    9, @empname = 'Emilia' , @salary = $2000.00;
EXEC dbo.usp_AddEmp
  @empid = 13, @mgrid =    9, @empname = 'Michael', @salary = $2000.00;
EXEC dbo.usp_AddEmp
  @empid = 14, @mgrid =    9, @empname = 'Didi'   , @salary = $1500.00;



  declare @toadd table
 (
	empid   INT,
	mgrid   INT,
	empname VARCHAR(25),
	salary  MONEY
)


insert into @toadd(empid, mgrid, empname, salary)
select 415, 5, 'vasya', 0.01
union all
select 316, 7, 'petya', 0.02
union all
select 117, 12, 'manya', 0.03
union all
select 216, 5, 'petrovich', 0.05


insert into dbo.Employees(empid, empname, salary, hid )
select empid,empname, salary
,p.hid.ToString()+cast(s.NC+row_number()over(partition by mgrid order by empid) as varchar(10))+'/' as hid
from @toadd t
cross apply (SELECT hid FROM dbo.Employees WHERE empid = mgrid) p
outer apply (SELECT count(hid) as NC  FROM dbo.Employees WHERE hid.GetAncestor(1) = p.hid) s


select *,hid.ToString()  as t from dbo.Employees
order by t
15 окт 14, 08:05    [16705432]     Ответить | Цитировать Сообщить модератору
 Re: HierarchyID: как оптимально добавить множество чилдов?  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
LexusR
поправить условие джойна не судьба ?
;with childlist(empid, mgrid, empname, salary,parenthid,hid) AS
(select t.empid, t.mgrid, t.empname, t.salary,e.hid as parenthid,e.hid.GetDescendant(lasthid, NULL)  as hid
from @toadd t
join dbo.Employees e on e.empid = t.mgrid
cross apply (SELECT MAX(hid) as lasthid FROM dbo.Employees WHERE hid.GetAncestor(1) = e.hid ) c
where not exists(select * from @toadd where empid < t.empid)
union all 
select t.empid, t.mgrid, t.empname, t.salary,c.parenthid,c.parenthid.GetDescendant(c.hid, NULL)  as hid
from @toadd t
join childlist  c on t.empid > c.empid
and not exists(select * from @toadd where empid > c.empid and empid < t.empid)
) 
insert into dbo.Employees(empid, hid, empname, salary)
select empid, hid, empname, salary from childlist 


просто рука не поднимается править чужой код
15 окт 14, 12:28    [16706718]     Ответить | Цитировать Сообщить модератору
 Re: HierarchyID: как оптимально добавить множество чилдов?  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
"а вообще можно без рекурсии и вообще для вставки узлов от разных парентов "

А вот это то, что нужно, когда нужно добавить несколько поддеревьев, спасибо.
Существенно, что правильный hid генерируется.
15 окт 14, 12:34    [16706752]     Ответить | Цитировать Сообщить модератору
 Re: HierarchyID: как оптимально добавить множество чилдов?  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
Но кстати, в последнем примере (без рекурсии) если изменить так, то не сработает

insert into @toadd(empid, mgrid, empname, salary)
select 415, 5, 'vasya', 0.01
union all
select 316, 7, 'petya', 0.02
union all
select 117, 12, 'manya', 0.03
union all
select 216, 5, 'petrovich', 0.05
union all
[b]select 501, 316, 'petin drug1', 0.02
union all
select 51, 316, 'petin drug2', 0.02
[/b]


То есть не добавляются двое петиных друзей к пете, который не существует в базовой таблице и сам добавляется
15 окт 14, 12:50    [16706839]     Ответить | Цитировать Сообщить модератору
 Re: HierarchyID: как оптимально добавить множество чилдов?  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
Наверное можно так сделать:

для первого уровня узлов в промежуточной таблице, которые будут цепляться к узлам в базовой - определить их реальные хиды, затем для остальных сгенерировать хиды и уже после этого все эти поддеревья вкинуть в базовую таблицу.
15 окт 14, 13:01    [16706908]     Ответить | Цитировать Сообщить модератору
 Re: HierarchyID: как оптимально добавить множество чилдов?  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1873
а как Петины дети получат данные о Петиной иерархии если его еще нет в таблице ?
15 окт 14, 13:02    [16706913]     Ответить | Цитировать Сообщить модератору
 Re: HierarchyID: как оптимально добавить множество чилдов?  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1873
Winnipuh
Наверное можно так сделать:

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


в принципе наверно можно
15 окт 14, 13:03    [16706928]     Ответить | Цитировать Сообщить модератору
 Re: HierarchyID: как оптимально добавить множество чилдов?  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1873
;with nodes (empid,mgrid, empname, salary, hid ) as
(select t.empid,t.mgrid, t.empname, t.salary
,p.hid.ToString()+cast(s.NC+row_number()over(partition by t.mgrid order by t.empid) as varchar(10))+'/' as hid
from @toadd t
outer apply (SELECT hid FROM dbo.Employees WHERE empid = t.mgrid) p
outer apply (SELECT count(hid) as NC  FROM dbo.Employees WHERE hid.GetAncestor(1) = p.hid) s
) 
insert into dbo.Employees(empid, empname, salary, hid )
select n.empid, n.empname, n.salary
,isnull(n.hid,p.hid+cast(row_number()over(partition by n.mgrid order by n.empid) as varchar(10))+'/') as hid
from nodes n
left join nodes p on p.empid = n.mgrid and n.hid is null
15 окт 14, 13:35    [16707159]     Ответить | Цитировать Сообщить модератору
 Re: HierarchyID: как оптимально добавить множество чилдов?  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
LexusR
;with nodes (empid,mgrid, empname, salary, hid ) as
(select t.empid,t.mgrid, t.empname, t.salary
,p.hid.ToString()+cast(s.NC+row_number()over(partition by t.mgrid order by t.empid) as varchar(10))+'/' as hid
from @toadd t
outer apply (SELECT hid FROM dbo.Employees WHERE empid = t.mgrid) p
outer apply (SELECT count(hid) as NC  FROM dbo.Employees WHERE hid.GetAncestor(1) = p.hid) s
) 
insert into dbo.Employees(empid, empname, salary, hid )
select n.empid, n.empname, n.salary
,isnull(n.hid,p.hid+cast(row_number()over(partition by n.mgrid order by n.empid) as varchar(10))+'/') as hid
from nodes n
left join nodes p on p.empid = n.mgrid and n.hid is null


йез!
15 окт 14, 14:05    [16707388]     Ответить | Цитировать Сообщить модератору
 Re: HierarchyID: как оптимально добавить множество чилдов?  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
LexusR
;with nodes (empid,mgrid, empname, salary, hid ) as
(select t.empid,t.mgrid, t.empname, t.salary
,p.hid.ToString()+cast(s.NC+row_number()over(partition by t.mgrid order by t.empid) as varchar(10))+'/' as hid
from @toadd t
outer apply (SELECT hid FROM dbo.Employees WHERE empid = t.mgrid) p
outer apply (SELECT count(hid) as NC  FROM dbo.Employees WHERE hid.GetAncestor(1) = p.hid) s
) 
insert into dbo.Employees(empid, empname, salary, hid )
select n.empid, n.empname, n.salary
,isnull(n.hid,p.hid+cast(row_number()over(partition by n.mgrid order by n.empid) as varchar(10))+'/') as hid
from nodes n
left join nodes p on p.empid = n.mgrid and n.hid is null


Для статического дерева - это ок.

Я рядом другую тему открыл, но добавлю здесь вопросы тоже, на что нарвался на таблице в 10 млн записей:

1. Запрос типа такого - это писец какой-то, работает несколько минут, в профайлере идуте несколько миллинов(!) заисей с таким запросом от объекта 'hierarchyid', такое впечатление, что идет в лоб перебор записей.

SELECT count(hid) as NC  FROM dbo.Employees WHERE hid.GetAncestor(1) = p.hid


2. Такой метод быстрого формирования пути хорош
select t.empid,t.mgrid, t.empname, t.salary
,p.hid.ToString()+cast(s.NC+row_number()over(partition by t.mgrid order by t.empid) as varchar(10))+'/' as hid


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

Как можно решить эти (1,2) проблемы?
18 окт 14, 12:07    [16724657]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить