Skip to content

Instantly share code, notes, and snippets.

@NickCraver
Last active Aug 29, 2015
Embed
What would you like to do?
UDF Performance and why to avoid use in WHERE clauses
-- Here, the function is run once, and using the result for row comparisons
Declare @RayId BIGINT = dbo.fnToRay('1D7370C08D710779-EWR');
Select *
From Log_2015_04_15
Where RayId = @RayId;
-- (1 row(s) affected)
-- Table 'Log_2015_04_15'. Scan count 41, logical reads 7808338, physical reads 0, read-ahead reads 126, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- SQL Server Execution Times:
-- CPU time = 24124 ms, elapsed time = 2752 ms.
-- Here the function is run for and compared to every row (194,238,550 times in this example)
Select *
From Log_2015_04_15
Where RayId = dbo.fnToRay('1D7370C08D710779-EWR');
-- (1 row(s) affected)
-- Table 'Log_2015_04_15'. Scan count 1, logical reads 7808338, physical reads 0, read-ahead reads 76633, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- SQL Server Execution Times:
-- CPU time = 500797 ms, elapsed time = 554186 ms.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment