rCTE vs LIKE for Hierarchy

--Table DDL
Create Table #Hierarchy
(
[OUID] [int] Primary Key,
[OUParentID] [int] NULL,
[CustomerID] [int] NOT NULL,
[Enabled] [bit] NULL,
[Hierarchy] [varchar](256) NULL,
)
--Sample Insert of 26 rows
SELECT '364',NULL,'14','1','364' UNION ALL
SELECT '365','364','14','1','364.365' UNION ALL
SELECT '366','365','14','1','364.365.366' UNION ALL
SELECT '367','366','14','1','364.365.366.367' UNION ALL
SELECT '368','367','14','1','364.365.366.367.368' UNION ALL
SELECT '369','367','14','1','364.365.366.367.369' UNION ALL
SELECT '370','367','14','1','364.365.366.367.370' UNION ALL
SELECT '371','364','14','1','364.371' UNION ALL
SELECT '372','371','14','1','364.371.372' UNION ALL
SELECT '373','372','14','1','364.371.372.373' UNION ALL
SELECT '374','373','14','1','364.371.372.373.374' UNION ALL
SELECT '375','374','14','1','364.371.372.373.374.375' UNION ALL
SELECT '376','372','14','1','364.371.372.376' UNION ALL
SELECT '377','376','14','1','364.371.372.376.377' UNION ALL
SELECT '378','377','14','1','364.371.372.376.377.378' UNION ALL
SELECT '379','372','14','1','364.371.372.379' UNION ALL
SELECT '380','379','14','1','364.371.372.379.380' UNION ALL
SELECT '381','380','14','1','364.371.372.379.380.381' UNION ALL
SELECT '382','372','14','1','364.371.372.382' UNION ALL
SELECT '383','382','14','1','364.371.372.382.383' UNION ALL
SELECT '384','383','14','1','364.371.372.382.383.384' UNION ALL
SELECT '385','372','14','1','364.371.372.385' UNION ALL
SELECT '386','385','14','1','364.371.372.385.386' UNION ALL
SELECT '387','386','14','1','364.371.372.385.386.387' UNION ALL
SELECT '388','372','14','1','364.371.372.388' UNION ALL
SELECT '389','388','14','1','364.371.372.388.389'
--Original Query Approach
DECLARE @pCustomerID int,
@pUserID int,
@pOUIDs varchar(max),
@pLocationIds varchar(max)
SET @pCustomerID = 14
SET @pUserID = 1
SET @pOUIDs = NULL
SET @pLocationIDs = NULL
SELECT DISTINCT ou.OUID
FROM
(SELECT
ouChild.OUID,
ouChild.CustomerID,
ouChild.Enabled,
ou.OUID as OUIDAncestor
FROM #Hierarchy ou
INNER JOIN #Hierarchy ouChild ON ouChild.Hierarchy + '.' LIKE ou.Hierarchy + '.%'
)ou
WHERE
ou.CustomerID = @pCustomerID
AND ou.Enabled = 1
AND
(
@pOUIDs IS NULL
OR
@pOUIDs LIKE '%,' + CAST(OUIDAncestor AS VARCHAR(20)) + ',%'
);
-- CTE Approach
WITH LocationHierarchy As
(
SELECT
ouf.OUID,ouf.OUParentID,ouf.CustomerID,ouf.Hierarchy,ouf.Enabled
FROM
#Hierarchy ouf
Where
OUID in (select Value from dbo.SplitString(@pOUIDs,',')) or @pOUIDs is null
UNION ALL
SELECT
ouc.OUID,ouc.OUParentID,ouc.CustomerID,ouc.Hierarchy,ouc.Enabled
FROM
#Hierarchy ouc INNER JOIN
LocationHierarchy on ouc.OUParentID = LocationHierarchy.OUID
Where
ouc.Enabled = 1 and ouc.CustomerID = @pCustomerID
)
Select
DISTINCT ou.OUID
from
LocationHierarchy ou

ariel_mlk (9/4/2012)

Hi people,

I'm tunning a database and i've stumbled by some hierarchy queries, on the good old form of ID, ParentID, to work out the hierarchy the previous developers used a like logic as in

SELECT TB1.* FROM TABLE1 as TB1 ON TABLE1 as TB2 on TB2.Hierarchy + '.' LIKE TB1.Hierarchy + '.%'

where hierarchy field contains the "path" to the parent nodes for example
1.2.3.4.5 would mean, this row is 5, parent is 4, grandparent is 3 and so forth

i've rewritten that in a CTE, and the query plan shouts out at me that it takes something like 25% of the cost in batch when running the rCTE approach and the like approach ( which would lead me to think that the rCTE is by far faster than the original ) but the like clause seems to take way lesser reads, i've read somewhere that the query plan only looks at the cost of a first execution of a rCTE, but as I understand rCTE were meant for jobs like this one, so how can a nasty non-sargable like go by less reads than a rCTE ?

sry if the post is not clear enough,
I haven't posted the query plans in the belief that i'm just misunderstanding something, if it ain't the case, i'll promptly post those

;with BadAdj ( KeyVal, SomeVal, ParentKeyVal, AwfulPath)
as
(
select 1, 'Top Dog', null, '1' union all
select 2, 'Next Dog', 1, '1.2' union all
select 3, 'Grandchild', 2, '1.2.3' union all
select 4, 'Uber Grandchild', 3, '1.2.3.4' union all
select 5, 'Step Grandchild', 4, '1.2.3.4.5'
)

select * from BadAdj

SELECT
stChild.OUID,
stChild.OUParentID,
stChild.CustomerID,
stChild.Name,
stChild.NameFull,
stChild.Hierarchy,
stChild.HierarchyDepth,
stChild.Enabled,
st.OUID as OUIDAncestor
FROM SomeTable st
INNER JOIN SomeTable stChild ON stChild.Hierarchy + '.' LIKE st.Hierarchy + '.%'

ALTER FUNCTION [dbo].[SplitString]
(
@String VARCHAR(MAX),
@Delimeter Char(1)
)
RETURNS @RtnValue TABLE
(
Value VARCHAR(100)
)
AS
BEGIN
DECLARE @Cnt INT
SET @Cnt = 1

WHILE (CHARINDEX(@Delimeter, @String) > 0)
BEGIN
INSERT INTO @RtnValue (Value)
SELECT
Data = ltrim(rtrim(Substring(@String,1,Charindex(@Delimeter,@String)-1)))

SET @String = Substring(@String,Charindex(@Delimeter,@String)+1,len(@String))
SET @Cnt = @Cnt + 1
END

INSERT INTO @RtnValue (Value)
SELECT Data = ltrim(rtrim(@String))

RETURN
END