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

Откуда:
Сообщений: 413
Получаем иерархию элементов "вниз" от @id и ограничиваем по уровню вложения. @Id может быть как корневым (c parentid is null), так и промежуточным.
declare  @t TABLE (Id int ,ParentId int, Code varchar(max), Name varchar(20))
Insert into @t values(1,null,'0','Книги')
Insert into @t values(2,1,'01','Программирование')
Insert into @t values(3,1,'02','Фантастика')
Insert into @t values(4,1,'03','Новеллы')
Insert into @t values(5,2,'001','Базы Данных')
Insert into @t values(6,2,'002','Java')
Insert into @t values(7,2,'003','C#')
Insert into @t values(8,5,'0001','MS SQL')
Insert into @t values(9,5,'0002','Oracle')
Insert into @t values(10,5,'0003','Interbase');

declare @id int set @id=1
;with cte (id, ParentId, Code, Name,lvl)
as
(
select id, ParentId, Code, Name, 0 as lvl 
from @t 
where Id = @id
union all
select t1.id, t1.ParentId, t2.Code + '.' +t1.code, t1.Name,t2.lvl+1
from @t t1
join cte t2 on t2.id = t1.parentId
)
select * from cte where lvl <3
Это оптимальный способ или есть другой способ для таблиц с около 100 000 записями?
10 ноя 11, 14:36    [11575274]     Ответить | Цитировать Сообщить модератору
 Re: Иерархия вниз. А можно ли иначе?  [new]
trew
Member

Откуда: Москва
Сообщений: 2646
notricky,

Для версии MS SQL 2008 для реализации древовидной иерархии есть новый тип хранения данных hierarchyid.
http://habrahabr.ru/blogs/sql/27774/
10 ноя 11, 14:54    [11575453]     Ответить | Цитировать Сообщить модератору
 Re: Иерархия вниз. А можно ли иначе?  [new]
notricky
Member

Откуда:
Сообщений: 413
trew
notricky,

Для версии MS SQL 2008 для реализации древовидной иерархии есть новый тип хранения данных hierarchyid.
http://habrahabr.ru/blogs/sql/27774/
Да интересно. А для случаев с Id и ParentId int сабжевый вопрос?
10 ноя 11, 15:00    [11575521]     Ответить | Цитировать Сообщить модератору
 Re: Иерархия вниз. А можно ли иначе?  [new]
Зайцев Фёдор
Member

Откуда: Лужки
Сообщений: 5308
notricky
Получаем иерархию элементов "вниз" от @id и ограничиваем по уровню вложения. @Id может быть как корневым (c parentid is null), так и промежуточным.
declare  @t TABLE (Id int ,ParentId int, Code varchar(max), Name varchar(20))
Insert into @t values(1,null,'0','Книги')
Insert into @t values(2,1,'01','Программирование')
Insert into @t values(3,1,'02','Фантастика')
Insert into @t values(4,1,'03','Новеллы')
Insert into @t values(5,2,'001','Базы Данных')
Insert into @t values(6,2,'002','Java')
Insert into @t values(7,2,'003','C#')
Insert into @t values(8,5,'0001','MS SQL')
Insert into @t values(9,5,'0002','Oracle')
Insert into @t values(10,5,'0003','Interbase');

declare @id int set @id=1
;with cte (id, ParentId, Code, Name,lvl)
as
(
select id, ParentId, Code, Name, 0 as lvl 
from @t 
where Id = @id
union all
select t1.id, t1.ParentId, t2.Code + '.' +t1.code, t1.Name,t2.lvl+1
from @t t1
join cte t2 on t2.id = t1.parentId
)
select * from cte where lvl <3
Это оптимальный способ или есть другой способ для таблиц с около 100 000 записями?

условие where lvl <3 можно перетащить в CTE
10 ноя 11, 16:20    [11576390]     Ответить | Цитировать Сообщить модератору
 Re: Иерархия вниз. А можно ли иначе?  [new]
notricky
Member

Откуда:
Сообщений: 413
Зайцев Фёдор
notricky
Получаем иерархию элементов "вниз" от @id и ограничиваем по уровню вложения. @Id может быть как корневым (c parentid is null), так и промежуточным.
declare  @t TABLE (Id int ,ParentId int, Code varchar(max), Name varchar(20))
Insert into @t values(1,null,'0','Книги')
Insert into @t values(2,1,'01','Программирование')
Insert into @t values(3,1,'02','Фантастика')
Insert into @t values(4,1,'03','Новеллы')
Insert into @t values(5,2,'001','Базы Данных')
Insert into @t values(6,2,'002','Java')
Insert into @t values(7,2,'003','C#')
Insert into @t values(8,5,'0001','MS SQL')
Insert into @t values(9,5,'0002','Oracle')
Insert into @t values(10,5,'0003','Interbase');

declare @id int set @id=1
;with cte (id, ParentId, Code, Name,lvl)
as
(
select id, ParentId, Code, Name, 0 as lvl 
from @t 
where Id = @id
union all
select t1.id, t1.ParentId, t2.Code + '.' +t1.code, t1.Name,t2.lvl+1
from @t t1
join cte t2 on t2.id = t1.parentId
)
select * from cte where lvl <3
Это оптимальный способ или есть другой способ для таблиц с около 100 000 записями?

условие where lvl <3 можно перетащить в CTE
в этом случае тогда наверное надо будет ставить t2.lvl < 2 вместо 3.
10 ноя 11, 17:19    [11577037]     Ответить | Цитировать Сообщить модератору
 Re: Иерархия вниз. А можно ли иначе?  [new]
Volochkova
Member

Откуда:
Сообщений: 2321
notricky
Получаем иерархию элементов "вниз" от @id и ограничиваем по уровню вложения. @Id может быть как корневым (c parentid is null), так и промежуточным.
declare  @t TABLE (Id int ,ParentId int, Code varchar(max), Name varchar(20))
Insert into @t values(1,null,'0','Книги')
Insert into @t values(2,1,'01','Программирование')
Insert into @t values(3,1,'02','Фантастика')
Insert into @t values(4,1,'03','Новеллы')
Insert into @t values(5,2,'001','Базы Данных')
Insert into @t values(6,2,'002','Java')
Insert into @t values(7,2,'003','C#')
Insert into @t values(8,5,'0001','MS SQL')
Insert into @t values(9,5,'0002','Oracle')
Insert into @t values(10,5,'0003','Interbase');

declare @id int set @id=1
;with cte (id, ParentId, Code, Name,lvl)
as
(
select id, ParentId, Code, Name, 0 as lvl 
from @t 
where Id = @id
union all
select t1.id, t1.ParentId, t2.Code + '.' +t1.code, t1.Name,t2.lvl+1
from @t t1
join cte t2 on t2.id = t1.parentId
)
select * from cte where lvl <3
Это оптимальный способ или есть другой способ для таблиц с около 100 000 записями?


На 50 000 записях свой запрос длится 500мс а через CTE 700мс.
Все бы ничего, но У CTE Read = 650 000, а у своего кода всего 6 000.
Так что вещь интересная, но производительность тоже важна.
11 ноя 11, 07:09    [11579015]     Ответить | Цитировать Сообщить модератору
 Re: Иерархия вниз. А можно ли иначе?  [new]
AlphaDog
Member

Откуда: in the middle of nowhere
Сообщений: 532
Volochkova,

количество ридов желательно проверять очистив весь кеш. Похоже у вас вся таблица сидит в памяти. А вот если каждый раз данные будут читаться с диска (самый пессиместичный вариант) - в этом случае отладка запросов дает наибольший положительный выхлоп.
11 ноя 11, 07:26    [11579020]     Ответить | Цитировать Сообщить модератору
 Re: Иерархия вниз. А можно ли иначе?  [new]
Начинающий SQL 2008
Member

Откуда:
Сообщений: 438
Для версии MS SQL 2008 для реализации древовидной иерархии есть новый тип хранения данных hierarchyid.
http://habrahabr.ru/blogs/sql/27774/
Правильно ли я понял, что ParentID и ChildID должны иметь ТОЛЬКО тип int ?
11 ноя 11, 11:22    [11580086]     Ответить | Цитировать Сообщить модератору
 Re: Иерархия вниз. А можно ли иначе?  [new]
notricky
Member

Откуда:
Сообщений: 413
Начинающий SQL 2008
Для версии MS SQL 2008 для реализации древовидной иерархии есть новый тип хранения данных hierarchyid.
http://habrahabr.ru/blogs/sql/27774/
Правильно ли я понял, что ParentID и ChildID должны иметь ТОЛЬКО тип int ?
Почитайте комментарии - там более менее подробно это разбирается. и сами разберетесь.

Volochkova, что вы подразумеваете под своим запросом? пример можете показать?


пс: а вот если поменять местами t1 и t2 в условии Join-а - будем получать всех предков текущего элемента.
11 ноя 11, 11:30    [11580163]     Ответить | Цитировать Сообщить модератору
 Re: Иерархия вниз. А можно ли иначе?  [new]
Volochkova
Member

Откуда:
Сообщений: 2321
notricky
Volochkova, что вы подразумеваете под своим запросом? пример можете показать?


Свой вариант
Create table #SP_Product_Sub([ProductID] [int] NOT NULL, [ParentID] [int] NOT NULL, [LevelNum] [tinyint] NOT NULL )
Create table #SP_Product ([ProductID] [int] NOT NULL, [ParentID] [int] NOT NULL)
INSERT INTO #SP_Product ([ProductID], [ParentID]) SELECT  [ProductID], [ParentID] FROM SP_Product 

DECLARE @LevelNum INT, @EXIT BIT, @LevelTotal INT
SELECT @LevelNum = 1, @EXIT = 0
Declare @str varchar(4000)

INSERT INTO #SP_Product_Sub ( [ProductID], [ParentID], [LevelNum])
SELECT [ProductID] ,[ParentID], 0 FROM #SP_Product WHERE ParentID = 0
WHILE @EXIT = 0
BEGIN
	INSERT INTO #SP_Product_Sub ( [ProductID], [ParentID], [LevelNum])
	SELECT p.[ProductID] , p.[ParentID], @LevelNum  FROM #SP_Product p INNER JOIN (SELECT ProductID FROM #SP_Product_Sub WHERE LevelNum = @LevelNum - 1 GROUP BY ProductID) p2 on p.ParentID = p2.ProductID
	IF @@ROWCOUNT =0 SELECT @EXIT = 1
	SELECT @LevelNum = @LevelNum +1
	IF @LevelNum = 10 SELECT @EXIT = 1

END

CTE
With SP_Product_Sub([ProductID], [ParentID], LevelNum  )
As
(
	-- Anchor member definition
	SELECT p.[ProductID] , p.[ParentID], 0  As LevelNum FROM SP_Product p  Where ParentID = 0
    UNION ALL
-- Recursive member definition
    SELECT p.[ProductID] , p.[ParentID], pr.LevelNum +1 As LevelNum  FROM SP_Product p  inner join  SP_Product_Sub pr on p.ParentID = pr.ProductID 
)
11 ноя 11, 15:02    [11582318]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить