FSMk45U4su2F 0 Newbie Poster

Allright.

I have two queries, which provide the exact same result, yet the simpler one is roughly 100 times slower than the more complicated one.

I have absolutely no idea what is going on here.

And let me add, there is no recursion so this is no idiocyncracy of the query execution plan.

These are the queries:

select dub.*
from
    (
        select top 1 path
        from tblFamily
        where 0 = 0
            and lvl = 3
    ) as sub
    cross apply
    (      
        select path
        from tblFamily
        where 0 = 0          
            and id in
            (
                select number
                from dbo.util_list2tbl (sub.path)              
            )      
    ) as dub  
   
select lub.*
from
    (
        select top 1 path
        from tblFamily
        where 0 = 0
            and lvl = 3
    ) as sub
    cross apply
    (
        select number
        from dbo.util_list2tbl (sub.path)      
    ) as dub
    cross apply
    (
        select path
        from tblFamily
        where 0 = 0          
            and id = dub.number
    ) as lub

1. tblFamily has a primary key on path, which is a condensed path of [(ancestor_id,)*this_id,]
There are no problems with that whatsoever in the various procedures accessing it.
2. util_list2tbl is simply Erland Sommarskog's table-valued function which splits a comma-delimited string og numbers into a table of (number, position_in_list)*.
No problems with that either.

As already mentioned, the first query shows up in the execution plan with 99% of the load, and the latter with 1%.
It takes 1:45 minutes on my home computer.

This is the output from io and time:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.

(3 row(s) affected)
Table '#1367E606'. Scan count 24422, logical reads 24422, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblFamily'. Scan count 2, logical reads 183, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
   CPU time = 82781 ms,  elapsed time = 105673 ms.

(3 row(s) affected)
Table 'tblFamily'. Scan count 1, logical reads 13, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#1367E606'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 139 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

So I am wondering whether the whole implementation of cross apply is, erm, not-so-good.
Anyone?