Can someone explain this behavior?
I have a temp table variable with 15000 rows:
declare @sampleitems table
(
id nvarchar(255),
parentid nvarchar(255),
Name nvarchar(255)
)
insert into @sampleitems
values ('F76265C3-AAE1-4472-8F15-C866B33A6CCC', NULL, 'MASTER')
declare @count int = 15000
while @count <> 0
begin
insert into @sampleitems
values (NEWID(), 'F76265C3-AAE1-4472-8F15-C866B33A6CCC', 'sample name ' + cast(@count as nvarchar(255)))
set @count = @count -1
end
When I write a CTE query to list all fullids from above table the query takes approximately 1 second.
When I try to list fullnames query takes almost 30 seconds and maybe more.
All columns are equal data type of nvarchar(255)
but I cannot understand why listing FullNames takes so much longer than FullIDs.
Executing this query will finish in less than 2 seconds:
with CTE (ID, FullID) as
(
select
id, cast(id as nvarchar(max))
from
@sampleitems
where
parentid is null
union all
select
t.id, FullID + ' : ' + t.id
from
@sampleitems as T
inner join
CTE as c on c.ID = t.parentid
)
select *
from CTE
but listing fullnames will take almost 30 seconds or more
with CTE1 (ID, FullName) as
(
select
id, cast(Name as nvarchar(max))
from
@sampleitems
where
parentid is null
union all
select
t.id, c.FullName + ' : ' + cast(t.Name as nvarchar(max))
from
@sampleitems as T
inner join
CTE1 as c on c.ID = t.parentid
)
select *
from CTE1