DaveBoltman (10/28/2014)
CROSS APPLY has the advantage that the function is only called once for each possible value of the function parameters, rather than once for each row. Convert it to a table like this:
declare @pol_cd char(18), @val_date datetime
set @pol_cd = 'xx000000'
set @val_date = '2010/06/30'
selectp.currency
frompolicies p
cross apply (select dbo.fnxrate(p.currency, 'eur', @val_date) as x) as q
where1 = 1
and p.pol_id = @pol_cd
(q is the alias for the "table", x is the field name)
The simplest test I can think of suggests that this is not the case.
/*
WITH E1 AS (SELECT * FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) e1 (n)),
E2 AS (SELECT b.n FROM E1 a, E1 b),
E4 AS (SELECT c.n FROM E2 a, E2 b, E2 c)
SELECT ID = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1, n
INTO #Temp
FROM E4
CREATE UNIQUE CLUSTERED INDEX ucx_ID ON #Temp (ID)
*/
DECLARE @ID INT, @n INT, @nMOD INT, @StartDate DATETIME
---------------------------------------------------------------------
-- no user function
SELECT @StartDate = GETDATE()
SELECT
@ID = ID,
@n = n,
@nMOD = n%3
FROM #Temp
ORDER BY ID
SELECT DATEDIFF(MILLISECOND,@StartDate,GETDATE())
----------------------------------------------------------------------
-- standard call to iTVF
SELECT @StartDate = GETDATE()
SELECT
@ID = ID,
@n = n,
@nMOD = x.[mod]
FROM #Temp
CROSS APPLY dbo.IF_MOD (n) x
ORDER BY ID
SELECT DATEDIFF(MILLISECOND,@StartDate,GETDATE())
----------------------------------------------------------------------
-- standard call to UDF in output list
SELECT @StartDate = GETDATE()
SELECT
@ID = ID,
@n = n,
@nMOD = dbo.UDF_MOD(n)
FROM #Temp
ORDER BY ID
SELECT DATEDIFF(MILLISECOND,@StartDate,GETDATE())
----------------------------------------------------------------------
-- cross apply UDF
SELECT @StartDate = GETDATE()
SELECT
@ID = ID,
@n = n,
@nMOD = x.[MOD]
FROM #Temp
CROSS APPLY (SELECT [MOD] = dbo.UDF_MOD(n)) x
ORDER BY ID
SELECT DATEDIFF(MILLISECOND,@StartDate,GETDATE())
----------------------------------------------------------------------
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden