September 19, 2012 at 4:24 am
Based on http://technet.microsoft.com/en-us/library/ms175156%28SQL.105%29.aspx, the difference between cross apply and outer apply is
CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.
But in my example the cross apply also runs much slower than outer apply, why does that happen?
My table function:
ALTER function [dbo].[FnGetNumbers] (@from bigint, @to bigint)
returns table
as
return
with
a as (select convert(bigint,0) as n union all select 0),
b as (select 0 as n from a as a cross join a as b),
c as (select 0 as n from b as a cross join b as b),
d as (select 0 as n from c as a cross join c as b),
e as (select 0 as n from d as a cross join d as b),
f as (select 0 as n from e as a cross join e as b),
nums as (select row_number() over (order by (select 1)) as num from f as a cross join f as b)
select @from + num - 1 as Number
from nums
where num <= @to-@from+1
This works fast:
with temp as (
select 'A' C1, 'A' C2, 500 MaxCode
union
select 'A' C1, 'B' C2, 20 MaxCode
union
select 'A' C1, 'C' C2, 100 MaxCode
),
Combined as (
select *
from temp t outer apply dbo.FnGetNumbers(1, t.maxCode)
)
select *
from Combined
order by C1, C2
But the same query with cross apply runs really slow (I have to cancel it, so I don't even know how long it would run...):
with temp as (
select 'A' C1, 'A' C2, 500 MaxCode
union
select 'A' C1, 'B' C2, 20 MaxCode
union
select 'A' C1, 'C' C2, 100 MaxCode
),
Combined as (
select *
from temp t cross apply dbo.FnGetNumbers(1, t.maxCode)
)
select *
from Combined
order by C1, C2
September 19, 2012 at 9:34 am
Paul White has great 2 article series about Apply.
Understanding and Using APPLY (Part 1)[/url]
Understanding and Using APPLY (Part 2)[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply