Cross Apply with UDF is killing performance

Transact-SQL
1

I have UDF which execute in 0 sec

CREATE Function [bcd].[fn_Fetch_Agent_Neutral_State]
(
 @RESOURCE_KEY AS INTEGER
 ,@DATE_TIME_KEY AS INTEGER
 )
RETURNS TABLE
AS
RETURN  
(
    WITH Seconds AS
    (
        SELECT @DATE_TIME_KEY AS Sec
		UNION ALL
		SELECT Sec + 1 AS Sec
		FROM Seconds
		WHERE Sec <= @DATE_TIME_KEY + 899  
    )	

	SELECT 
		SUM(CASE WHEN SC.NeutralState = 'AfterCallWork' THEN 1 ELSE 0 END) AS Time_ACW
		, SUM(CASE WHEN SC.NeutralState = 'Busy' THEN 1 ELSE 0 END) AS Time_Busy
		, SUM(CASE WHEN SC.NeutralState = 'NotReady' THEN 1 ELSE 0 END) AS Time_NotReady
		, SUM(CASE WHEN SC.NeutralState = 'Ready' THEN 1 ELSE 0 END) AS Time_Ready
	FROM
	(
		SELECT  
			S.Sec
			, ISNULL(V.VoiceState,'N/A') AS VoiceState
			, ISNULL(E.EmailState,'N/A') AS EmailState
		FROM
			Seconds AS S
			LEFT JOIN
				(
				SELECT S.Sec, STA.STATE_NAME AS VoiceState
				FROM 
					[bcd].[Temp_SM_RES_STATE_FACT_Dual] AS STA
					INNER JOIN Seconds AS S ON S.Sec BETWEEN STA.START_TS AND (STA.END_TS - 1)
				WHERE STA.MEDIA_TYPE_KEY = 1 AND STA.RESOURCE_KEY = @RESOURCE_KEY AND STA.DATE_TIME_KEY = @DATE_TIME_KEY
				)V ON S.Sec = V.Sec
			LEFT JOIN
				(
				SELECT S.Sec, STA.STATE_NAME AS EmailState
				FROM 
					[bcd].[Temp_SM_RES_STATE_FACT_Dual] AS STA
					INNER JOIN Seconds AS S ON S.Sec BETWEEN STA.START_TS AND (STA.END_TS - 1)
				WHERE STA.MEDIA_TYPE_KEY = 2 AND STA.RESOURCE_KEY = @RESOURCE_KEY AND STA.DATE_TIME_KEY = @DATE_TIME_KEY
				)E ON S.Sec = E.Sec
	)X
	INNER JOIN bcd.vw_Neutal_States_Combination AS SC ON X.VoiceState = SC.VoiceState AND X.EmailState = SC.EmailState
)
GO

But when I am using it with Cross Apply, it keeps on running.....

SELECT *
FROM 
	(SELECT DT.DATE_TIME_KEY , A.RESOURCE_KEY
	FROM dbo.DATE_TIME AS DT
			CROSS JOIN #Agent AS A
	WHERE DT.DATE_TIME_KEY BETWEEN @SDK AND @EDK
	)P
	CROSS APPLY bcd.fn_Fetch_Agent_Neutral_State(P.RESOURCE_KEY, P.DATE_TIME_KEY)D
OPTION (MAXRECURSION 0)

Thanks in advance.

2

Hi niraj_19_dgp,

I found two related topics:

Or, if you ask me, I think, the complexity of your whole expression will be too huge totally (probably not using indexes at all or optimally) and individually also. For example using functions in joins, lots of sub-selects. And cross joins also could generate huge datasets without pre-filtering. It depends on your datasets, that you use.

In your case, it could be a good solution I hope, that if you put your 1st query to a temp table, and you make an index on the columns, that you would like to use in the join (DATE_TIME_KEY, RESOURCE_KEY).

And after you make exactly the same (putting to a temp table) with the [bcd.[Temp_SM_RES_STATE_FACT_Dual] table, but you prefilter for the (DATE_TIME_KEY, RESOURCE_KEY) values determined before. Also need an index for the new temp table also (START_TS, END_TS). And after for the columns (VoiceState, EmailState).

I hope, it will help a little bit.

Regards,
gigawatt38
MCSA: SQL 2016 Database Development
MCSE: Data Management and Analytics

3

(I edited your original post so I could read it better.)

Keep in mind that the cross-apply is running that function for each row in the query. So if each execution takes 1 second and you have 100 rows you'll get a 100 second query.

1 Like