0

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
Stelios
  • 330
  • 5
  • 21

0 Answers0