CROSS APPLY - drives me mad

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 374,193 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,553 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our MS SQL advertiser:

Thread Tools Display Modes

Views: 587 | Replies: 0

•

Join Date: Apr 2008

Posts: 1

Reputation: FSMk45U4su2F is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

FSMk45U4su2F FSMk45U4su2F is offline

Offline

Newbie Poster

CROSS APPLY - drives me mad

Apr 22nd, 2008

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:

Help with Code Tags

(Toggle Plain Text)

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:

Help with Code Tags

(Toggle Plain Text)

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?