Is there a better way to query a hierarchy table?

Question

Post reply

Is there a better way to query a hierarchy table?

johnsql-193053

SSCommitted

Points: 1657
More actions
January 21, 2015 at 8:23 am

#293995

Hi,
I have a table named 'DepartmentItem' which is designed with hierarchy structure. The column 'ParentId' from table DepartmentItem indicates parent-child relationship and department root among records. I have written and run a user-defined function I use recursive approach, but the function runs slowly.
My question: is there a better way to query that hierarchy table instead of using recursive?
** The current user-defined function that is written using recursive:
CREATE FUNCTION dbo.fnGetDepartmentTree
(
@departmentItemId int
)
RETURNS TABLE
AS
RETURN
with DepartmentItemTree(DepartmentItemId , DepartmentItemTypeId , ParentId, ItemOrder, Level)
AS
(
select DepartmentItemId , DepartmentItemTypeId , ParentId, ItemOrder, 0 as Level
from DepartmentItem with(nolock)
where DepartmentItemId = @departmentItemId and IsActive = 1
union all
select si.DepartmentItemId , si.DepartmentItemTypeId , si.ParentId, si.ItemOrder, Level + 1
from DepartmentItem siwith(nolock)
inner join DepartmentItemTree t on si.ParentId = t.DepartmentItemId
where IsActive = 1
)
select * from DepartmentItemTree
** And definition of table 'DepartmentItem' :
DepartmentItemId int IDENTITY(1,1) NOT NULL,
ParentId int NULL, -- Each department root starts when this column is NULL or the current row is department root. If it is not NULL then the current row has ParentId whose record has DepartmentItemId = ParentId of the current row (see more below)
IsActive bit NOT NULL DEFAULT ((1)),
ItemOrder int NOT NULL,
DepartmentItemTypeId int NOT NULL
ALTER TABLE dbo.DepartmentItem WITH NOCHECK ADD CONSTRAINT FK_DepartmentItem_ParentId_DepartmentItem FOREIGN KEY(ParentId)
REFERENCES dbo.DepartmentItem (DepartmentItemId )
GO
ALTER TABLE dbo.DepartmentItem CHECK CONSTRAINT FK_DepartmentItem_ParentId_DepartmentItem
GO

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

There are always better ways...

You should read this:

http://www.sqlservercentral.com/articles/Hierarchy/94040/

and

http://www.sqlservercentral.com/articles/T-SQL/94570/

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Dwain Camps SSC Guru Points: 86978 More actions · Answer 2

Indeed there are many ways. Jeff's articles are great. There is also this one:

https://www.simple-talk.com/sql/performance/the-performance-of-traversing-a-sql-hierarchy-/

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St