UDF Performance and why to avoid use in WHERE clauses

UDFPerf.sql

-- 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.