UDF Help

Alan.B (3/1/2013)

This is something I need to play around with more. I took what Jeff said to imply that, in the code below, the iTVF function (nsq_iTVF) would be faster than the iSVF (nsq_iSVF).

CREATE FUNCTION dbo.nsq_iSVF (@int int)
RETURNS bigint WITH SCHEMABINDING
AS
BEGIN
	RETURN @int/2
END
GO

CREATE FUNCTION dbo.nsq_iTVF (@int int)
RETURNS @rs TABLE (n bigint) WITH SCHEMABINDING
AS
BEGIN
	INSERT @rs SELECT @int/2;
	RETURN;
END
GO

--Create some test data
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
	DROP TABLE #tmp;

CREATE TABLE #tmp (n bigint);

INSERT #tmp
	SELECT TOP 100000 ABS(CHECKSUM(NEWID()))
	FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2;

SET NOCOUNT ON;
GO

--Test the Functions (on my local PC)
SET STATISTICS TIME ON
	SELECT x.n n1, s.n n2
	FROM #tmp x
	CROSS APPLY dbo.nsq_iTVF(x.n) s
SET STATISTICS TIME OFF
GO

SET STATISTICS TIME ON
	SELECT n n1, dbo.nsq_iSVF(n) n2
	FROM #tmp x
SET STATISTICS TIME OFF
GO

DROP TABLE #tmp
GO

However, the iSVF makes short work of the iTVF.

 SQL Server Execution Times:
   CPU time = 4977 ms,  elapsed time = 5108 ms.

 SQL Server Execution Times:
   CPU time = 1497 ms,  elapsed time = 1599 ms.

Doing the same test on a two functions I wrote - each correctly calculate the hamming distance between two strings... Again, the SVF remains undefeated.

Query:


--iTVF
CREATE FUNCTION dbo.hd_iTVF (@s1 varchar(8000), @s2 varchar(8000))
RETURNS @hd TABLE(hd int)
AS
BEGIN
	WITH 
	nums(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM [master].dbo.spt_values Tally),
	matrix  AS (SELECT SUBSTRING(@s1,n,1) s1, SUBSTRING(@s2,n,1) s2 FROM nums WHERE n<=LEN(@s1))
	INSERT @hd 
		SELECT CASE WHEN LEN(@s1)<>LEN(@s2) THEN NULL ELSE COUNT(*) END
		FROM matrix 
		WHERE s1<>s2;

	RETURN;
END
GO

--iSVF
CREATE FUNCTION dbo.hd_SVF (@s1 varchar(8000), @s2 varchar(8000))
RETURNS int
AS
BEGIN
	DECLARE @hd int=0;

	WITH 
	nums(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM [master].dbo.spt_values Tally),
	matrix  AS (SELECT SUBSTRING(@s1,n,1) s1, SUBSTRING(@s2,n,1) s2 FROM nums WHERE n<=LEN(@s1))
	SELECT @hd=CASE WHEN LEN(@s1)<>LEN(@s2) THEN NULL ELSE COUNT(*) END
	FROM matrix 
	WHERE s1<>s2;

	RETURN @hd;
END
GO

--Create some test data
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
	DROP TABLE #tmp;

CREATE TABLE #tmp (s1 varchar(10), s2 varchar(10))

INSERT #tmp
SELECT TOP 100000 s1 = ABS(CHECKSUM(NEWID())), s2=ABS(CHECKSUM(NEWID()))
FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2;

SET NOCOUNT ON;
GO

--Test the Functions (on my local PC)
SET STATISTICS TIME ON
	SELECT s1, s2, s.hd
	FROM #tmp x
	CROSS APPLY dbo.hd_iTVF(x.s1,x.s2) s
SET STATISTICS TIME OFF
GO

SET STATISTICS TIME ON
	SELECT s1, s2, dbo.hd_SVF(s1,s2) AS hd
	FROM #tmp x
SET STATISTICS TIME OFF
GO

DROP TABLE #tmp
GO

Results:

 SQL Server Execution Times:
   CPU time = 4977 ms,  elapsed time = 5108 ms.

 SQL Server Execution Times:
   CPU time = 1497 ms,  elapsed time = 1599 ms.

Yes, I have begun reading Jeff's Spackle article about this (great article as usual) and am at the TEST, TEST, TEST part.

Based on my testing here - this is a case where a SVF (inline or otherwise) is superior. Is this because I need to do a cross join to get my scalar value from the iTVF?

CREATE FUNCTION dbo.hd_iTVF (@s1 varchar(8000), @s2 varchar(8000))
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN(
WITH
nums(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM [master].dbo.spt_values Tally),
matrix AS (SELECT SUBSTRING(@s1,n,1) s1, SUBSTRING(@s2,n,1) s2 FROM nums WHERE n<=LEN(@s1))
SELECT CASE WHEN LEN(@s1)<>LEN(@s2) THEN NULL ELSE COUNT(*) END hd
FROM matrix
WHERE s1 <> s2);
GO

RAISERROR('
======================================================================
Test the functions with SET STATISTICS TIME ON so we can see how much
it actually affects scalar and mTVF functions but not the iTVF. Each
test is in its own batch so we can run individual tests if we want.
======================================================================
',0,1) WITH NOWAIT
;
--===== Setup test environment
SET STATISTICS TIME, IO OFF; --Well turn TIME on for each test for a nice, clean output.
SET NOCOUNT ON;
GO
RAISERROR('========== nsq_iTVF (mTVF) ===========================================',0,1) WITH NOWAIT
;
--===== Create bit-bucket variable
DECLARE @BitBucket BIGINT
;
--===== Test the code
SET STATISTICS TIME ON;
SELECT @BitBucket = x.n,
@BitBucket = s.n
FROM #tmp x
CROSS APPLY dbo.nsq_iTVF(x.n) s;
SET STATISTICS TIME OFF;
GO
RAISERROR('========== nsq_iSVF (scalar) =========================================',0,1) WITH NOWAIT
;
--===== Create bit-bucket variable
DECLARE @BitBucket BIGINT
;
--===== Test the code
SET STATISTICS TIME ON;
SELECT @BitBucket = n,
@BitBucket = dbo.nsq_iSVF(n)
FROM #tmp x;
SET STATISTICS TIME OFF;
GO
RAISERROR('========== Lynn_iTVF (real iTVF) =====================================',0,1) WITH NOWAIT
;
--===== Create bit-bucket variable
DECLARE @BitBucket BIGINT;
--===== Test the code
SET STATISTICS TIME ON;
SELECT @BitBucket = x.n,
@BitBucket = s.divby2
FROM #tmp x
CROSS APPLY dbo.Lynn_iTVF(x.n) s;
SET STATISTICS TIME OFF;
GO
RAISERROR('
======================================================================
Now, do the exact same tests with a simple DURATION calculation
instead of using SET STATISTICS. Again, each test is in its own
batch so we can run individual tests if we want.
======================================================================
',0,1) WITH NOWAIT
;
GO
RAISERROR('========== nsq_iTVF (mTVF) ===========================================',0,1) WITH NOWAIT
;
--===== Create the timer and bit-bucket variables and start the timer.
DECLARE @StartTime DATETIME,
@BitBucket BIGINT;
SELECT @StartTime = GETDATE()
;
--===== Test the code
SELECT @BitBucket = x.n,
@BitBucket = s.n
FROM #tmp x
CROSS APPLY dbo.nsq_iTVF(x.n) s
;
--===== Display the duration
PRINT 'Duration (ms): ' + CAST(DATEDIFF(ms,@StartTime,GETDATE()) AS VARCHAR(10))
;
GO
RAISERROR('========== nsq_iSVF (scalar) =========================================',0,1) WITH NOWAIT
;
--===== Create the timer and bit-bucket variables and start the timer.
DECLARE @StartTime DATETIME,
@BitBucket BIGINT;
SELECT @StartTime = GETDATE()
;
--===== Test the code
SELECT @BitBucket = n,
@BitBucket = dbo.nsq_iSVF(n)
FROM #tmp x
;
--===== Display the duration
PRINT 'Duration (ms): ' + CAST(DATEDIFF(ms,@StartTime,GETDATE()) AS VARCHAR(10))
;
GO
RAISERROR('========== Lynn_iTVF (real iTVF) =====================================',0,1) WITH NOWAIT
;
--===== Create the timer and bit-bucket variables and start the timer.
DECLARE @StartTime DATETIME,
@BitBucket BIGINT;
SELECT @StartTime = GETDATE()
;
--===== Test the code
SELECT @BitBucket = x.n,
@BitBucket = s.divby2
FROM #tmp x
CROSS APPLY dbo.Lynn_iTVF(x.n) s
;
--===== Display the duration
PRINT 'Duration (ms): ' + CAST(DATEDIFF(ms,@StartTime,GETDATE()) AS VARCHAR(10))
;
GO

======================================================================
Test the functions with SET STATISTICS TIME ON so we can see how much
it actually affects scalar and mTVF functions but not the iTVF. Each
test is in its own batch so we can run individual tests if we want.
======================================================================
========== nsq_iTVF (mTVF) ===========================================

SQL Server Execution Times:
CPU time = 58750 ms, elapsed time = 103897 ms.
========== nsq_iSVF (scalar) =========================================

SQL Server Execution Times:
CPU time = 10828 ms, elapsed time = 34761 ms.
========== Lynn_iTVF (real iTVF) =====================================

SQL Server Execution Times:
CPU time = 94 ms, elapsed time = 94 ms.

======================================================================
Now, do the exact same tests with a simple DURATION calculation
instead of using SET STATISTICS. Again, each test is in its own
batch so we can run individual tests if we want.
======================================================================
========== nsq_iTVF (mTVF) ===========================================
Duration (ms): 24640
========== nsq_iSVF (scalar) =========================================
Duration (ms): 733
========== Lynn_iTVF (real iTVF) =====================================
Duration (ms): 90

======================================================================
Test the functions with SET STATISTICS TIME ON so we can see how much
it actually affects scalar and mTVF functions but not the iTVF. Each
test is in its own batch so we can run individual tests if we want.
======================================================================
========== nsq_iTVF (mTVF) ===========================================
SQL Server Execution Times: CPU time = 4664 ms, elapsed time = 4733 ms.
========== nsq_iSVF (scalar) =========================================
SQL Server Execution Times: CPU time = 406 ms, elapsed time = 430 ms.
========== Lynn_iTVF (real iTVF) =====================================
SQL Server Execution Times: CPU time = 15 ms, elapsed time = 20 ms.
======================================================================
Now, do the exact same tests with a simple DURATION calculation
instead of using SET STATISTICS. Again, each test is in its own
batch so we can run individual tests if we want.
======================================================================
========== nsq_iTVF (mTVF) ===========================================
Duration (ms): 4353
========== nsq_iSVF (scalar) =========================================
Duration (ms): 286
========== Lynn_iTVF (real iTVF) =====================================
Duration (ms): 20

--I changed the order of these functions to the order in which
--they appear in the output.

--===== Alan's orignal code.
-- This actually is NOT an "iTVF". It's an "mTVF"
-- which is as bad or worse than a scalar UDR>
CREATE FUNCTION dbo.nsq_iTVF (@int int)
RETURNS @rs TABLE (n bigint) WITH SCHEMABINDING
AS
BEGIN
INSERT @rs SELECT @int/2;
RETURN;
END
GO
--===== Alan's orignal code
-- This is a scalar UDF
CREATE FUNCTION dbo.nsq_iSVF (@int int)
RETURNS bigint WITH SCHEMABINDING
AS
BEGIN
RETURN @int/2
END
GO
--===== Lynn's true iTVF code being used
-- as if a scalar UDF. (changed the name for testing)
CREATE FUNCTION dbo.Lynn_iTVF (@int bigint)
RETURNS TABLE WITH SCHEMABINDING
AS
return (SELECT cast(@int/2 as bigint) divby2);
GO
--===== Create some test data (Alan's original code)
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp;

CREATE TABLE #tmp (n bigint);

INSERT #tmp
SELECT TOP 100000 ABS(CHECKSUM(NEWID()))
FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2;
GO

RAISERROR('
======================================================================
Now, do the exact same tests adding hard-coding of the calculation
being done with the various functions with a simple DURATION calculation
instead of using SET STATISTICS, plus calculations from sys.db_exec_requests.
Also clearing proccache and systemcache.
======================================================================
',0,1) WITH NOWAIT
;
GO
RAISERROR('========== nsq_iTVF (mTVF) ===========================================',0,1) WITH NOWAIT
;
--===== Create the timer and bit-bucket variables and start the timer.
DECLARE @StartTime DATETIME,
@BitBucket BIGINT;

Declare @cpu_ int,
@lreads_ int,
@eMsec_ int;
declare @CpuMs int,
@LogRds int,
@Elapsed int;

dbcc freeproccache with no_infomsgs;
dbcc freesystemcache('ALL') with no_infomsgs;

Select
@StartTime = GETDATE(),
@cpu_ = cpu_time
, @lreads_ = logical_reads
, @eMsec_ = total_elapsed_time
From
sys.dm_exec_requests
Where
session_id = @@spid;

---
--===== Test the code
SELECT @BitBucket = x.n,
@BitBucket = s.n
FROM #tmp x
CROSS APPLY dbo.nsq_iTVF(x.n) s
;

---

Select
@CpuMs = cpu_time - @cpu_
, @LogRds = logical_reads - @lreads_
, @Elapsed = total_elapsed_time - @eMsec_
From
sys.dm_exec_requests
Where
session_id = @@spid;
--===== Display the duration
PRINT 'Duration (ms): ' + CAST(DATEDIFF(ms,@StartTime,GETDATE()) AS VARCHAR(10)) +
' CPU(ms): ' + cast(@CpuMs as varchar(10)) + ' Logical Reads: ' + cast(@LogRds as varchar(10)) + ' Elapsed(ms): ' + cast(@Elapsed as varchar(10))
;
GO
RAISERROR('========== nsq_iSVF (scalar) =========================================',0,1) WITH NOWAIT
;
--===== Create the timer and bit-bucket variables and start the timer.
DECLARE @StartTime DATETIME,
@BitBucket BIGINT;

Declare @cpu_ int,
@lreads_ int,
@eMsec_ int;
declare @CpuMs int,
@LogRds int,
@Elapsed int;

dbcc freeproccache with no_infomsgs;
dbcc freesystemcache('ALL') with no_infomsgs;

Select
@StartTime = GETDATE(),
@cpu_ = cpu_time
, @lreads_ = logical_reads
, @eMsec_ = total_elapsed_time
From
sys.dm_exec_requests
Where
session_id = @@spid;

---
--===== Test the code
SELECT @BitBucket = n,
@BitBucket = dbo.nsq_iSVF(n)
FROM #tmp x
;

--===== Test the code
SELECT @BitBucket = x.n,
@BitBucket = s.n
FROM #tmp x
CROSS APPLY dbo.nsq_iTVF(x.n) s
;

---

Select
@CpuMs = cpu_time - @cpu_
, @LogRds = logical_reads - @lreads_
, @Elapsed = total_elapsed_time - @eMsec_
From
sys.dm_exec_requests
Where
session_id = @@spid;
--===== Display the duration
PRINT 'Duration (ms): ' + CAST(DATEDIFF(ms,@StartTime,GETDATE()) AS VARCHAR(10)) +
' CPU(ms): ' + cast(@CpuMs as varchar(10)) + ' Logical Reads: ' + cast(@LogRds as varchar(10)) + ' Elapsed(ms): ' + cast(@Elapsed as varchar(10))
;
GO
RAISERROR('========== Lynn_iTVF (real iTVF) =====================================',0,1) WITH NOWAIT
;
--===== Create the timer and bit-bucket variables and start the timer.
DECLARE @StartTime DATETIME,
@BitBucket BIGINT;

Declare @cpu_ int,
@lreads_ int,
@eMsec_ int;
declare @CpuMs int,
@LogRds int,
@Elapsed int;

dbcc freeproccache with no_infomsgs;
dbcc freesystemcache('ALL') with no_infomsgs;

Select
@StartTime = GETDATE(),
@cpu_ = cpu_time
, @lreads_ = logical_reads
, @eMsec_ = total_elapsed_time
From
sys.dm_exec_requests
Where
session_id = @@spid;

---
--===== Test the code
SELECT @BitBucket = x.n,
@BitBucket = s.divby2
FROM #tmp x
CROSS APPLY dbo.Lynn_iTVF(x.n) s
;

---

Select
@CpuMs = cpu_time - @cpu_
, @LogRds = logical_reads - @lreads_
, @Elapsed = total_elapsed_time - @eMsec_
From
sys.dm_exec_requests
Where
session_id = @@spid;
--===== Display the duration
PRINT 'Duration (ms): ' + CAST(DATEDIFF(ms,@StartTime,GETDATE()) AS VARCHAR(10)) +
' CPU(ms): ' + cast(@CpuMs as varchar(10)) + ' Logical Reads: ' + cast(@LogRds as varchar(10)) + ' Elapsed(ms): ' + cast(@Elapsed as varchar(10))
;
GO

RAISERROR('========== Hard-coded computation =====================================',0,1) WITH NOWAIT
;
--===== Create the timer and bit-bucket variables and start the timer.
DECLARE @StartTime DATETIME,
@BitBucket BIGINT;

Declare @cpu_ int,
@lreads_ int,
@eMsec_ int;
declare @CpuMs int,
@LogRds int,
@Elapsed int;

dbcc freeproccache with no_infomsgs;
dbcc freesystemcache('ALL') with no_infomsgs;

Select
@StartTime = GETDATE(),
@cpu_ = cpu_time
, @lreads_ = logical_reads
, @eMsec_ = total_elapsed_time
From
sys.dm_exec_requests
Where
session_id = @@spid;

---
--===== Test the code
SELECT @BitBucket = x.n,
@BitBucket = x.n/2
FROM #tmp x
;

---

Select
@CpuMs = cpu_time - @cpu_
, @LogRds = logical_reads - @lreads_
, @Elapsed = total_elapsed_time - @eMsec_
From
sys.dm_exec_requests
Where
session_id = @@spid;
--===== Display the duration
PRINT 'Duration (ms): ' + CAST(DATEDIFF(ms,@StartTime,GETDATE()) AS VARCHAR(10)) +
' CPU(ms): ' + cast(@CpuMs as varchar(10)) + ' Logical Reads: ' + cast(@LogRds as varchar(10)) + ' Elapsed(ms): ' + cast(@Elapsed as varchar(10))
;
GO

DROP FUNCTION dbo.nsq_iTVF;
DROP FUNCTION dbo.nsq_iSVF;
DROP FUNCTION dbo.Lynn_iTVF;
GO

(100000 row(s) affected)

======================================================================
Now, do the exact same tests adding hard-coding of the calculation
being done with the various functions with a simple DURATION calculation
instead of using SET STATISTICS, plus calculations from sys.db_exec_requests.
Also clearing proccache and systemcache.
======================================================================
========== nsq_iTVF (mTVF) ===========================================
Duration (ms): 14710 CPU(ms): 13984 Logical Reads: 1000393 Elapsed(ms): 14710
========== nsq_iSVF (scalar) =========================================
Duration (ms): 14356 CPU(ms): 13766 Logical Reads: 1000631 Elapsed(ms): 14354
========== Lynn_iTVF (real iTVF) =====================================
Duration (ms): 63 CPU(ms): 62 Logical Reads: 289 Elapsed(ms): 65
========== Hard-coded computation =====================================
Duration (ms): 63 CPU(ms): 63 Logical Reads: 253 Elapsed(ms): 64

(1000000 row(s) affected)

======================================================================
Now, do the exact same tests adding hard-coding of the calculation
being done with the various functions with a simple DURATION calculation
instead of using SET STATISTICS, plus calculations from sys.db_exec_requests.
Also clearing proccache and systemcache.
======================================================================
========== nsq_iTVF (mTVF) ===========================================
Duration (ms): 149236 CPU(ms): 138281 Logical Reads: 10002279 Elapsed(ms): 149234
========== nsq_iSVF (scalar) =========================================
Duration (ms): 141856 CPU(ms): 136047 Logical Reads: 10004408 Elapsed(ms): 141855
========== Lynn_iTVF (real iTVF) =====================================
Duration (ms): 660 CPU(ms): 656 Logical Reads: 2179 Elapsed(ms): 660
========== Hard-coded computation =====================================
Duration (ms): 670 CPU(ms): 656 Logical Reads: 2143 Elapsed(ms): 668