That's odd. I've tested CTEs vs loops on hierarchy data and ended up with slightly better performance in the CTE than the loop. Directly contradicts what you ended up with.

set statistics io on

set statistics time on

declare @TopStructureID_in int

select @topstructureid_in =

(select structureid

from dbo.campaigns

where campaignid = 560)

declare @Hierarchy table (

Lvl int not null,

StructureID int not null,

ParentID int,

primary key (lvl, structureid),

State varchar(3))

declare @Rows int

insert into @hierarchy (lvl, structureid, parentid, state)

select 1, structureid, parentstructureid, rowstate

from dbo.corporatestructure

where structureid = @topstructureid_in

and rowstate != 'del'

select @rows = @@rowcount

while @rows > 0

begin

insert into @hierarchy (lvl, structureid, parentid, state)

select 1 + h2.lvl,

c2.structureid, c2.parentstructureid, c2.RowState

from dbo.corporatestructure c2

inner join @hierarchy h2

on c2.parentstructureid = h2.structureid

where c2.rowstate != 'del'

and c2.structureid not in

(select structureid

from @hierarchy)

select @rows = @@rowcount

end

select *

from @hierarchy

==============================

Table 'Campaigns'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

Table '#131B4459'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'CorporateStructure'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

Table '#131B4459'. Scan count 2, logical reads 71, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 1, logical reads 37, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'CorporateStructure'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 15 ms, elapsed time = 1 ms.

(17 row(s) affected)

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

Table '#131B4459'. Scan count 2, logical reads 381, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 1, logical reads 174, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'CorporateStructure'. Scan count 18, logical reads 36, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 3 ms.

(86 row(s) affected)

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

Table '#131B4459'. Scan count 2, logical reads 729, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 1, logical reads 262, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'CorporateStructure'. Scan count 104, logical reads 208, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 8 ms.

(130 row(s) affected)

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

Table '#131B4459'. Scan count 2, logical reads 1099, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 1, logical reads 314, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'CorporateStructure'. Scan count 234, logical reads 472, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 16 ms, elapsed time = 21 ms.

(156 row(s) affected)

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

Table '#131B4459'. Scan count 2, logical reads 1054, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 1, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'CorporateStructure'. Scan count 390, logical reads 784, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 16 ms, elapsed time = 22 ms.

(5 row(s) affected)

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

Table '#131B4459'. Scan count 2, logical reads 1044, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'CorporateStructure'. Scan count 395, logical reads 794, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 15 ms, elapsed time = 22 ms.

(0 row(s) affected)

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

(395 row(s) affected)

Table '#131B4459'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

=====================================

set statistics io on

set statistics time on

declare @TopStructureID_in int

select @topstructureid_in =

(select structureid

from dbo.campaigns

where campaignid = 560)

declare @Hierarchy table (

Lvl int not null,

StructureID int not null,

ParentID int,

primary key (lvl, structureid),

State varchar(3))

;with Hierarchy (Lvl, StructureID, ParentID, RowState)

as

(select 0, StructureID, ParentStructureID, c1.RowState

from dbo.corporatestructure c1

where structureid = @topstructureid_in

union all

select 1 + h2.lvl,

c2.structureid, c2.parentstructureid, c2.RowState

from dbo.corporatestructure c2

inner join hierarchy h2

on c2.parentstructureid = h2.structureid

where c2.rowstate != 'del'

and h2.rowstate != 'del')

insert into @hierarchy (lvl, structureid, parentid, state)

select lvl, structureid, parentid, rowstate

from hierarchy

select *

from @hierarchy

====================================

Table 'Campaigns'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

Table '#441E6E67'. Scan count 0, logical reads 797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 2, logical reads 2371, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'CorporateStructure'. Scan count 790, logical reads 1584, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 31 ms, elapsed time = 24 ms.

(395 row(s) affected)

(395 row(s) affected)

Table '#441E6E67'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

[/code]

Both run multiple times, to make sure data is in the cache.

The loop method (which may differ from the loop you're using), took 62 milliseconds of CPU time, and doesn't improve from that in the next five runs. Took 1,142 total table scans of the CorporateStructure table (the one with the hierarchy in it).

The CTE took 31 milliseconds CPU as its longest run, and took as little as 15 milliseconds CPU on several of the runs. 790 total scans of the table.

From that, I have to say the CTE is better in all regards. Faster, and less IO bottleneck.