Self-Referencing Parent Child Table SELECT Statement Possible?
-
Table: tblEmployee
ID ParentID EmpName
1 NULL The BIG BOSS
2 1 Sam (VP)
3 1 Bill (CEO)
4 2 Steve (under VP)
5 3 James (under CEO)
6 5 Jack(under James)
7 5 Jim (under James)
I need to write a Stored Procedure (SP) with:
Input: @ID (tblEmployee.ID)
Output: All employees records under @ID
Sample Execution
Input: @ID = 3 (Bill)
Output: 3 Records; ID# 5 (James), 6 (Jack), 7 (Jim)
This SP is returning all the employees that is under that given @ID (as well as any other person under them, and so on). If the @ID = 1, this means that it'll return every record except ID#1 because everyone reports to "The BIG BOSS".
Is there a way to write this using a single SELECT statement? If not, what's the most efficient and fastest way of returning all the records under the Inputted @ID? Performance is key to this SP because it's being called about a dozen time per minute on average for this application.
Any input/suggestions is appreciated. Thanks.
-
Which version of SQL Server ?
This would be an ideal candidate for a CTE under SQL 2005.
-
CREATE ....
Declare @IDS TABLE (Id int)
INSERT INTO @IDS (ID)
SELECT @ID
WHILE EXISTS (select 1 from tblEmployee E
inner join @IDS I on E.ParentId = I.Id --E.ParentId in @IDS
left join @IDS I1 on E.Id = I.Id -- E.Id not in @IDS yet
where I1.Id IS NULL )
BEGIN
INSERT INTO @IDS (ID)
SELECT E.Id
FROM tblEmployee E
inner join @IDS I on E.ParentId = I.Id
WHERE NOT EXISTS (select 1 from @IDS I where I.Id = E.Id)
END
SELECT E.*
FROM tblEmployees E
INNER JOIN @IDS I ON E.ID = I.ID
WHERE I.ID <> @ID
----------
It's a lazy option, there is a way to make it better in terms of performance.
But it gives you a direction.
_____________
Code for TallyGenerator -
I'm using SQL2K.
-
You're stuck with a completely flexible solution, as posted above, or alternatively you can trade off flexibility, remove the looping and place a hard cap on the number of levels you query for and just UNION ALL together a bunch of queries that pull various depths.
Select ID, EmpName -- get the employee (Level1)
From tblEmployee
Where ID = @ID
Union All -- and all immediately below (Level2)
Select L2.ID, L2.EmpName
From tblEmployee As L1
Inner Join tblEMployee As L2
On (L2.ParentId = L1.ID)
Where L1.ID = @ID
Union All -- and all 2 levels down (Level3)
Select L3.ID, L3.EmpName
From tblEmployee As L1
Inner Join tblEMployee As L2
On (L2.ParentId = L1.ID)
Inner Join tblEmployee As L3
On (L3.ParentId = L2.ID)
Where L1.ID = @ID
etc...
-
Hopefully your "OrgChart" table is not being updated dozen times per minute.
So the best way to improve performance is to create separate table ReportsToBoss(BossId, ReporterId) and fill it up with results from the SP.
Than create trigger on tblEmployee to update table ReportsToBoss every time tblEmployee has been updated.
Then your select from ReportsToBoss will be really fast.
_____________
Code for TallyGenerator -
I did this for a colleague the other day who had a similar problem with some help from BOL.
He had originally written a recursive function to draw out a tree menu system that was taken nearly 20 seconds to render on the website. Now it takes 1 second.
CREATE PROCEDURE [usp_asp_draw_tree]
@ID int
AS
SET nocount on
CREATE TABLE #cats (rID int identity(1,1),catid int, menu varchar(125))
DECLARE @level int
DECLARE @current char(20)
DECLARE @sql varchar(2000)
DECLARE @sql2 varchar(2000)
DECLARE @lvl int, @line char(100), @disp varchar(100)
set @current = @ID
CREATE TABLE #stack (item int, sName varchar(100), nlevel int)
INSERT INTO #stack
VALUES (@current,'', 1)
SELECT @level = 1
WHILE @level > 0
BEGIN
if EXISTS (SELECT * FROM #stack WHERE nlevel = @level)
BEGIN
SELECT
@current = item, @disp = sName
FROM
#stack
WHERE
nlevel = @level
--optional to display menu level
SELECT @line = replicate('~',@level - 1) + @disp
INSERT INTO #cats
VALUES (@current,@line)
DELETE FROM #stack
WHERE nlevel = @level AND item = @current
INSERT INTO #stack
(item, sName, nLevel)
SELECT
ID, EmpName, @level + 1
FROM
tbl_TEST
WHERE
ParentID = @current
if @@rowcount > 0
SELECT @level = @level + 1
END
else
SELECT @level = @level - 1
END
drop table #stack
select rID, * from tbl_TEST as ci join #cats as c on ci.ID = c.catid order by rID
drop table #cats
set nocount off
GO
exec usp_asp_draw_tree 1 --for everything or else pass in the ID that you wish to retrieve everything beneath.
I had to write it for 7.5 so that why I used a #table not @table
but it was deployed on 2005 and I had to add in the rID identity col into the table as for some reason 2005 was sorting it differently to 7.5.
-
A minor improvement on Sergiy's method... I don't know if it's faster than PW's algorithm, but the number of SQL statements seems to be the same (if you count each statement in the union all select as one SQL statement). It could be interesting to compare performance...
set nocount on
go
create table tblEmployee
(
Id int,
ParentId int NULL
)
go
insert into tblEmployee
select 1, NULL union all
select 2, 1 union all
select 3, 1 union all
select 4, 2 union all
select 5, 3 union all
select 6, 5 union all
select 7, 5
go
Declare @IDS TABLE (Id int primary key)
declare @ID int
select @ID = 1
INSERT INTO @IDS (ID) SELECT @ID
WHILE @@ROWCOUNT > 0
BEGIN
INSERT INTO @IDS (ID)
SELECT E.Id
FROM
@IDS I
inner join tblEmployee E
on E.ParentId = I.Id
left join @IDS I1
on I1.Id = E.Id
where I1.Id IS NULL
END
SELECT E.*
FROM tblEmployee E
INNER JOIN @IDS I ON E.ID = I.ID
WHERE I.ID <> @ID
drop table tblEmployee
go
-
I've tested the performance using each of the 4 suggestions and these are the best in order of performance:
1. Jesper (extremely fast for all test cases)
2. PW (fastest when it's only a few levels deep)
3. Sergiy
4. Rob Reid
Jesper's tweaking made it on par with PW's method. In fact, the majority of the time, it outperforms PW's method by about 5%.
However, PW's method is faster than Jesper's method when it's only a few levels deep. When it hits the 6+ levels deep into the tree-structure, Jesper's seems to be consistently faster by about 5%. I don't know why, but that's what I'm getting.
But, Jesper's and PW's method are about as optimal as it can get from what I can tell. Given that they perform just about the same (5% is not very noticeable), I like Jesper's method since it's so much easier to maintain and read. It also doesn't have the level limitation either.
Thanks everyone for your help!
-
Thanks for posting the results of your test....
-
Old hand posted what he described a a lazy option, I changed the code slightly to use a count of the levels available, therefore limiting the loops as follows
declare @idxcount as int
select @idxcount = count(distinct parentid) from tblEmployee
declare @idxPos as int
set @IdxPos = 1
declare @ID int
Declare @IDS TABLE (Id int)
set @ID = 3
INSERT INTO @IDS (ID)
SELECT @ID
WHILE ( @IdxPos <= @IdxCount)
BEGIN
Set @IdxPos = @IdxPos+1
INSERT INTO @IDS (ID)
SELECT E.Id
FROM tblEmployee E
inner join @IDS I on E.ParentId = I.Id
WHERE NOT EXISTS (select 1 from @IDS I where I.Id = E.Id)
END
SELECT E.*
FROM tblEmployee E
INNER JOIN @IDS I ON E.ID = I.ID
WHERE I.ID <> @ID
And it worked well, Just out of interest is this not a viable option as a flexible solution?
It is a procedure that I was going to use in a similar situation.
-
I don't think this will perform very well. Considering the data below. Your loop is iterated 10 times, and Sergiy's loop (and my loop) only 3 times.
set nocount on
go
create table tblEmployee
(
Id int,
ParentId int NULL
)
go
insert into tblEmployee
select 1, NULL union all
select 2, 1 union all
select 3, 1 union all
select 4, 1 union all
select 5, 1 union all
select 6, 1 union all
select 7, 1 union all
select 8, 1 union all
select 9, 1 union all
select 10, 1 union all
select 11, 1 union all
select 12, 2 union all
select 13, 3 union all
select 14, 4 union all
select 15, 5 union all
select 16, 6 union all
select 17, 7 union all
select 18, 8 union all
select 19, 9 union all
select 20, 10
go
declare @idxcount as int
select @idxcount = count(distinct parentid) from tblEmployee
declare @idxPos as int
set @IdxPos = 1
declare @ID int
Declare @IDS TABLE (Id int)
set @ID = 1
INSERT INTO @IDS (ID)
SELECT @ID
WHILE ( @IdxPos <= @IdxCount)
BEGIN
Set @IdxPos = @IdxPos+1
INSERT INTO @IDS (ID)
SELECT E.Id
FROM tblEmployee E
inner join @IDS I on E.ParentId = I.Id
WHERE NOT EXISTS (select 1 from @IDS I where I.Id = E.Id)
END
SELECT E.*
FROM tblEmployee E
INNER JOIN @IDS I ON E.ID = I.ID
WHERE I.ID <> @ID
drop table tblEmployee
go
-
My change looped just 4 times not 10 though as I used count distinct on parentid. but I do see your point.
Thanks
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply