MS SQL

CROSS APPLY - drives me mad

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Apr 2008

Posts: 1

Reputation: FSMk45U4su2F is an unknown quantity at this point

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 
 MS SQL Syntax (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
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 
 MS SQL Syntax (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.
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.