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?