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?