March 2, 2013 at 9:27 pm
surprisingly linear!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 4, 2013 at 7:07 am
Chrism@Work brought to my attention a copy/paste error in my code. Please see the code below:
SET NOCOUNT ON
GO
--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
;
/* in for the second time ##############################################
--===== 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
New results at 100,000 rows:
======================================================================
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): 13390 CPU(ms): 12032 Logical Reads: 1000389 Elapsed(ms): 13345
========== nsq_iSVF (scalar) =========================================
Duration (ms): 793 CPU(ms): 781 Logical Reads: 273 Elapsed(ms): 782
========== Lynn_iTVF (real iTVF) =====================================
Duration (ms): 63 CPU(ms): 63 Logical Reads: 289 Elapsed(ms): 63
========== Hard-coded computation =====================================
Duration (ms): 63 CPU(ms): 62 Logical Reads: 253 Elapsed(ms): 65
New results at 1,000,000 rows:
======================================================================
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): 136060 CPU(ms): 128906 Logical Reads: 10002281 Elapsed(ms): 136058
========== nsq_iSVF (scalar) =========================================
Duration (ms): 8260 CPU(ms): 8047 Logical Reads: 2163 Elapsed(ms): 8258
========== Lynn_iTVF (real iTVF) =====================================
Duration (ms): 650 CPU(ms): 640 Logical Reads: 2179 Elapsed(ms): 651
========== Hard-coded computation =====================================
Duration (ms): 633 CPU(ms): 625 Logical Reads: 2143 Elapsed(ms): 633
March 4, 2013 at 7:51 pm
So, just like I said in the article, Scalar functions aren't always as bad as they've been made out to be but a true iTVF will easily beat the tar out of it.
--Jeff Moden
March 5, 2013 at 1:47 pm
Lynn, Jeff, Kevin, ChrisM: thank you very much. This has been a particularly informative and excellent thread. It's given me a lot of new things to chew on.
Inline Table Valued Functions (iTVF for short) return a result set even if that result set is a single element. Think of it as an Inline Scalar Function. The "inline" type of function is about 7 times faster than any scalar function and also works faster for MultiLine Table Valued Fuctions.
This was the first time I heard anyone explain this and the first time I read Jeff's Scalar UDF article[/url]. Interstingly enough - there is not a lot of information about iSVFs at all (BOL mentioned them but barely). I have been writing/testing iSVFs and iTVFs for a couple days now and am amazed.
I had some functions that I thought were not inline but were not (such as what I posted). I have re-written a couple SVFs as iSVFs with good results. I also re-wrote a couple sSVFs as iTVFs and things that look ~2 seconds per 100K rows and are now taking 135ms Mind Boggling! .
I am still floored the article by the existance of iSVFs.
Great thread! Thank you again gents!
edit: minor typo.
-- Itzik Ben-Gan 2001
March 5, 2013 at 2:00 pm
Alan.B (3/5/2013)
Lynn, Jeff, Kevin, ChrisM: thank you very much. This has been a particularly informative and excellent thread. It's given me a lot of new things to chew on.Inline Table Valued Functions (iTVF for short) return a result set even if that result set is a single element. Think of it as an Inline Scalar Function. The "inline" type of function is about 7 times faster than any scalar function and also works faster for MultiLine Table Valued Fuctions.
This was the first time I heard anyone explain this and the first time I read Jeff's Scalar UDF article[/url]. Interstingly enough - there is not a lot of information about iSVFs at all (BOL mentioned them but barely). I have been writing/testing iSVFs and iTVFs for a couple days now and am amazed.
I had some functions that I thought were not inline but were not (such as what I posted). I have re-written a couple SVFs as iSVFs with good results. I also re-wrote a couple sSVFs as iTVFs and things that look ~2 seconds per 100K rows and are now taking 135ms Mind Boggling! .
I am still floored the article by the existance of iSVFs.
Great thread! Thank you again gents!
edit: minor typo.
Tis why we are here! :blush:
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply