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

Откуда: Гималай
Сообщений: 2101
Приветствую всех.
Есть таблица пользователей:

TblUsers
ID
ParentUser_ID
UserName


данные
ID ParentUser_ID UserName
1 -1 User1
2 -1 User2
3 2 User3
4 3 User4
5 3 User5
6 3 User6
7 6 User7


инструкция для получения пути:
DECLARE
	@ParentUser_ID bigint,
	@User_ID bigint,
	@U_ID bigint,
	@TreePath nvarchar(MAX)

DECLARE
	@TblUsers TABLE (
		ID bigint,
		ParentUser_ID bigint,
		UserName nvarchar(200)
	)

INSERT INTO @TblUsers (ID, ParentUser_ID, UserName)
SELECT 1, -1, N'User1'
UNION ALL
SELECT 2, -1, N'User2'
UNION ALL
SELECT 3, 2, N'User3'
UNION ALL
SELECT 4, 3, N'User4'
UNION ALL
SELECT 5, 3, N'User5'
UNION ALL
SELECT 6, 3, N'User6'
UNION ALL
SELECT 7, 6, N'User7'

SET @ParentUser_ID=2
SET @User_ID=7
SET @TreePath=N''
SELECT
	@TreePath=UserName,
	@U_ID=ParentUser_ID
FROM
	@TblUsers
WHERE
	ID=@User_ID
IF (@U_ID<>-1) AND (@U_ID<>@ParentUser_ID)
	WHILE 1=1 BEGIN
		SELECT
			@U_ID=ParentUser_ID,
			@TreePath=UserName+N' / '+@TreePath
		FROM
			@TblUsers
		WHERE
			ID=@U_ID
		
		IF (@U_ID=-1) OR (@U_ID=@ParentUser_ID)
			BREAK
	END

SELECT @TreePath

Может быть кто-нибудь предложит более оптимальный способ, так как эта функция должна вызываться для каждой записи из recordset'а из 5-10 тысяч записей
При этом заранее строить путь нельзя, так как отношение пользователей может быть разным, т.е. нужно будет построить путь от пользователя User2 до User7, от User3 до User7 и т.д.

Спасибо за внимание и помощь
4 июл 11, 20:56    [10920783]     Ответить | Цитировать Сообщить модератору
 Re: Путь к элементу в дереве  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
orunbek
инструкция для получения пути:


ну и говнокод

чем вам СТЕ не нравится!?

DECLARE
	@ParentUser_ID bigint,
	@User_ID bigint

DECLARE
	@TblUsers TABLE (
		ID bigint,
		ParentUser_ID bigint,
		UserName nvarchar(200)
	)

INSERT INTO @TblUsers (ID, ParentUser_ID, UserName)
SELECT 1, -1, N'User1'
UNION ALL
SELECT 2, -1, N'User2'
UNION ALL
SELECT 3, 2, N'User3'
UNION ALL
SELECT 4, 3, N'User4'
UNION ALL
SELECT 5, 3, N'User5'
UNION ALL
SELECT 6, 3, N'User6'
UNION ALL
SELECT 7, 6, N'User7'

SET @ParentUser_ID=2
SET @User_ID=7;

with cte
as
(
select id, cast( UserName as varchar(100) ) UserName from @TblUsers
where ParentUser_ID = @ParentUser_ID
union all
select t1.id, cast( t2.UserName + '/' + t1.UserName as varchar(100) )
 from @TblUsers t1 inner join cte t2 on t1.ParentUser_ID = t2.ID 
)
select UserName from cte where id = @User_ID
4 июл 11, 21:13    [10920812]     Ответить | Цитировать Сообщить модератору
 Re: Путь к элементу в дереве  [new]
orunbek
Member

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

ндэээ, ну и "манеры" у вас...
при CTE строятся для всех пользователей
выполните:
select UserName from cte --where id = @User_ID

увидите:
User3
User3/User4
User3/User5
User3/User6
User3/User6/User7

а при while:
обрабатывается только три записи User7, User6, User3
для тестовых данных CTE проходит нормально, у меня изначально и был CTE, но я не хотел как раз обрабатывать каждую запись
а для реальных данных, когда количество записей в таблице TblUsers несколько сотен тысяч?
который метод быстрее будет?
4 июл 11, 21:37    [10920874]     Ответить | Цитировать Сообщить модератору
 Re: Путь к элементу в дереве  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
orunbek
который метод быстрее будет?

конечно мой
4 июл 11, 21:40    [10920880]     Ответить | Цитировать Сообщить модератору
 Re: Путь к элементу в дереве  [new]
orunbek
Member

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

правильный тест?

cte
set nocount on;

DECLARE
	@ParentUser_ID bigint,
	@User_ID bigint,
	@i bigint

DECLARE
	@TblUsers TABLE (
		ID bigint,
		ParentUser_ID bigint,
		UserName nvarchar(200)
	)

INSERT INTO @TblUsers (ID, ParentUser_ID, UserName)
SELECT 1, -1, N'User1'
UNION ALL
SELECT 2, -1, N'User2'
UNION ALL
SELECT 3, 2, N'User3'
UNION ALL
SELECT 4, 3, N'User4'
UNION ALL
SELECT 5, 3, N'User5'
UNION ALL
SELECT 6, 3, N'User6'
UNION ALL
SELECT 7, 6, N'User7'

set @i=8
while @i<10000 begin
	insert into @TblUsers (ID, ParentUser_ID, UserName)
	select @i, 3, N'User'+CAST(@i AS nvarchar)
	set @i=@i+1
end

SET @ParentUser_ID=2
SET @User_ID=7

select convert(varchar,GETDATE(),121) as start_time;

with cte
as
(
select id, cast( UserName as varchar(100) ) UserName from @TblUsers
where ParentUser_ID = @ParentUser_ID
union all
select t1.id, cast( t2.UserName + '/' + t1.UserName as varchar(100) )
 from @TblUsers t1 inner join cte t2 on t1.ParentUser_ID = t2.ID 
)

select UserName from cte where id = @User_ID

select convert(varchar,GETDATE(),121) as end_time

while..do
set nocount on;

DECLARE
	@ParentUser_ID bigint,
	@User_ID bigint,
	@U_ID bigint,
	@TreePath nvarchar(MAX),
	@i bigint

DECLARE
	@TblUsers TABLE (
		ID bigint,
		ParentUser_ID bigint,
		UserName nvarchar(200)
	)

INSERT INTO @TblUsers (ID, ParentUser_ID, UserName)
SELECT 1, -1, N'User1'
UNION ALL
SELECT 2, -1, N'User2'
UNION ALL
SELECT 3, 2, N'User3'
UNION ALL
SELECT 4, 3, N'User4'
UNION ALL
SELECT 5, 3, N'User5'
UNION ALL
SELECT 6, 3, N'User6'
UNION ALL
SELECT 7, 6, N'User7'

set @i=8
while @i<10000 begin
	insert into @TblUsers (ID, ParentUser_ID, UserName)
	select @i, 3, N'User'+CAST(@i AS nvarchar)
	set @i=@i+1
end

SET @ParentUser_ID=2
SET @User_ID=7
SET @TreePath=N''

print convert(varchar,GETDATE(),121)

SELECT
	@TreePath=UserName,
	@U_ID=ParentUser_ID
FROM
	@TblUsers
WHERE
	ID=@User_ID
IF (@U_ID<>-1) AND (@U_ID<>@ParentUser_ID)
	WHILE 1=1 BEGIN
		SELECT
			@U_ID=ParentUser_ID,
			@TreePath=UserName+N' / '+@TreePath
		FROM
			@TblUsers
		WHERE
			ID=@U_ID
		
		IF (@U_ID=-1) OR (@U_ID=@ParentUser_ID)
			BREAK
	END

print @TreePath
print convert(varchar,GETDATE(),121)
4 июл 11, 22:01    [10920948]     Ответить | Цитировать Сообщить модератору
 Re: Путь к элементу в дереве  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
при while...do это не больше секунды
при cte больше секунды
у меня по крайней мере так выполняется
4 июл 11, 22:19    [10921008]     Ответить | Цитировать Сообщить модератору
 Re: Путь к элементу в дереве  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
orunbek
при while...do это не больше секунды
при cte больше секунды
у меня по крайней мере так выполняется


дык вы тест под свой код подгоняете...попробуйте глубину дерева сделать несколько десятков...а так под ваши данные, через тот же СТЕ:

with cte
as
(
select id, ParentUser_ID p_id, cast( UserName as varchar(100) ) UserName from @TblUsers
where id = @User_ID
union all
select t1.id, t1.ParentUser_ID, cast( t1.UserName + '/' + t2.UserName as varchar(100) )
 from @TblUsers t1 inner join cte t2 on t1.id = t2.p_id and t1.ParentUser_ID != -1 
)
select UserName from cte where p_id = @ParentUser_ID

и опять ваш вариант с циклом в разы хуже по времени
5 июл 11, 06:27    [10921795]     Ответить | Цитировать Сообщить модератору
 Re: Путь к элементу в дереве  [new]
orunbek
Member

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

я не подгоняю
этим кодом хочу продемонстрировать случаи, когда на одном достаточно большое количество записей будет
и это только на уровне User6, а если до этого уровня такое же количество записей будет?
5 июл 11, 07:41    [10921842]     Ответить | Цитировать Сообщить модератору
 Re: Путь к элементу в дереве  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
т.е. на одном уровне
5 июл 11, 09:14    [10922046]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить