February 28, 2013 at 10:19 am
Comes down to if the scalar function is being called once per query or once per row in the query.
February 28, 2013 at 10:25 am
Lynn Pettis (2/28/2013)
Comes down to if the scalar function is being called once per query or once per row in the query.
There really is more to it than just the "cursor under the covers" effect.
1) It can lead to bad estimates and thus horribly bad plans (which can mean not only poor performance but also poor concurrency).
2) It WILL void the use of parallelism.
3) It can force the use of table spooling for halloween protection even when it isn't really required.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 1, 2013 at 12:16 am
TheSQLGuru (2/28/2013)
I would honestly expect (but not certain here) that a scalar UDF that just puts it's output into a variable would be more efficient than iTVF that returns one row one column table simply due to less overhead related to the output format (variable vs table).
Then you've got to read the following.
http://www.sqlservercentral.com/articles/T-SQL/91724/
--Jeff Moden
Change is inevitable... Change for the better is not.
March 1, 2013 at 7:16 am
Jeff Moden (3/1/2013)
TheSQLGuru (2/28/2013)
I would honestly expect (but not certain here) that a scalar UDF that just puts it's output into a variable would be more efficient than iTVF that returns one row one column table simply due to less overhead related to the output format (variable vs table).Then you've got to read the following.
1) on my laptop (powerful, with SQL 2012 SP1 CU2) sUDF and iTVF both run in EXACTLY the same time (143ms). I suppose the difference is a) your old machine and b) perhaps parallelism? My CTFP was set high enough so that the 2.x query cost of scanning that 1M row table and doing the calculation on the column didn't cause the iTVF to parallelize.
2) HOWEVER, your example was not what I was mentioning. I was speaking of the case of a SINGLE call to a sUDF to populate a variable that is used elsewhere and a SINGLE call to an iTVF to get same value into a temp object. Not calling each to make a calculation iteratively on a single large table.
I note that the two are not semantically equivalent and cannot be used identically later in the batch - I was just making an observation. Since it is confusing at best I will withdraw it to avoid further pursuit of a non-useful point. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 1, 2013 at 1:46 pm
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?
-- Itzik Ben-Gan 2001
March 1, 2013 at 2:03 pm
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?
Your itvf isn't, it is a multistatement tvf. Use this in your tests:
CREATE FUNCTION dbo.nsq_iTVF (@int bigint)
RETURNS TABLE WITH SCHEMABINDING
AS
return (SELECT cast(@int/2 as bigint) divby2);
March 1, 2013 at 2:09 pm
Your other function would look something like this:
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
March 1, 2013 at 4:26 pm
I recommend against using statistics time on to capture timing metrics too.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 1, 2013 at 7:34 pm
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).
Careful now. What I said is correct but the code you wrote isn't. As Lynn suggested, the nsq_iTVF function is actually an "mTVF" or "Multi-Line Table Valued Function" and, especially for purposes of calculating a scalar value, is much worse than an actual iTVF (Inline Table Valued function) that is used to return a scalar value.
There's another problem here, at least in SQL Server 2005. Like I said in the article on the subject, the use of SET STATISTICS will add a huge amount of overhead and make scalar UDFs and mTVFs look painfully slow. You need to use a difference in times to fairly measure duration when either is involved.
Last but not least, the display is also known as the "Great Equalizer" because it takes more time to display a row than it does to calculate the row. When you return something like 100,000 rows, many methods will look like they take virtually the same amount of time because the amount of time to display the rows will overshadow the actual differences. To wit, you need to dump the output of such testing to a variable to take the display times out of the picture.
I've incorporated all of that in the following code. I've also added a true iTVF and used it as a scalar fuction. Last but not least, there are two sets of identical tests... one with SET STATISTICS and one with a smple duration timer.
First, here's the original 2 functions and test table that Alan was nice enough to build. It also includes Lynn's function which is the only tru iTVF of the 3.
--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
Here's the test code. Again, there are two sets of identical tests. The first set uses SET STATISTICS which makes all but Lynn's function (an iTVF) look really bad. Do notice the use of the @Bitbucket variable which keeps the display from becoming the "Great Equalizer".
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
Here are the results from my 11 year old single cpu 2005 desktop box. Notice the HUGE differences when you compare the first run to the second. Also notice that Lynn's true iTVF code (more than 7 times faster) blows the doors off the previous fastest code... just like it did in the article. 😉
======================================================================
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
Here are the results from my more modern quad core I5 laptop running 2008. As you can see, the duration for the scalar function is still drastically affected by SET STATISTICS. I'll also point out that Lynn's function absolutely smokes compared to the other functions.
======================================================================
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
This code is full of lessons on how to test code. For example, if you turn the Actual Execution Plan on and rerun the 2nd half of the test code, you'll see that the 2nd and 3rd test have identical execution plans and the % of batch is identical even though we just proved that nothing could be further from the truth. The lesson here is to never use an Execution Plan to determine the winner in a performance race. You actually have to conduct the race to find out for sure.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 1, 2013 at 7:41 pm
TheSQLGuru (3/1/2013)
Jeff Moden (3/1/2013)
TheSQLGuru (2/28/2013)
I would honestly expect (but not certain here) that a scalar UDF that just puts it's output into a variable would be more efficient than iTVF that returns one row one column table simply due to less overhead related to the output format (variable vs table).Then you've got to read the following.
1) on my laptop (powerful, with SQL 2012 SP1 CU2) sUDF and iTVF both run in EXACTLY the same time (143ms). I suppose the difference is a) your old machine and b) perhaps parallelism? My CTFP was set high enough so that the 2.x query cost of scanning that 1M row table and doing the calculation on the column didn't cause the iTVF to parallelize.
2) HOWEVER, your example was not what I was mentioning. I was speaking of the case of a SINGLE call to a sUDF to populate a variable that is used elsewhere and a SINGLE call to an iTVF to get same value into a temp object. Not calling each to make a calculation iteratively on a single large table.
I note that the two are not semantically equivalent and cannot be used identically later in the batch - I was just making an observation. Since it is confusing at best I will withdraw it to avoid further pursuit of a non-useful point. 🙂
On item 1, were you returning to the screen or dumping to a variable to take the time to display out of the picture?
On item 2, I stand corrected. I definetly mistook what you were referring to.
On the last part, I agree. They are not semantically equivalent. A conversion would definetly require some redaction of the code. As we're both of the same ilk when it comes to performance, we'd weigh out the ROI of the redaction v.s. the required retesting and possible redistribution of the code and make a decision from there.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 2, 2013 at 8:09 am
You have known me long enough Jeff to know I wouldn't run that test into SSMS output! 😉
My laptop being so powerful has actually forced me to do some things differently for a few of my SQL Saturday presentations. It truly is more powerful than many if not most of my client's production boxes. I have to run my column store index samples (7+GB, 131M row fact table) from an external USB drive for example because my pair of SSDs gets me almost 1GB/sec read IO performance!! And yes, that is gigaBYTE, not gigaBIT. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 2, 2013 at 9:40 am
Yeah, you're right, Kevin. I should'a'node. 🙂 The "symptoms" told me I should ask anyway. Didn't know the "problem" would be such an incredible machine. Thanks for the feedback. See you at SQLSaturday in Detroit, right?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 2, 2013 at 11:36 am
Jeff Moden (3/2/2013)
Yeah, you're right, Kevin. I should'a'node. 🙂 The "symptoms" told me I should ask anyway. Didn't know the "problem" would be such an incredible machine. Thanks for the feedback. See you at SQLSaturday in Detroit, right?
Well, I only upgrade my laptop every 4 years, and when I do I get something just below top-of-the-line (to avoid that extra 2X cost for another 20% or so perf). I find great long-term value with this method. This laptop was so much faster (esp with the SSDs) that I can actually run my P2V'd old laptop in VMWare and it is FASTER than the old hardware! :w00t:
I will indeed see you in Detroit for the SQL Saturday there. My 51st event if my counting is correct. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 2, 2013 at 11:37 am
Okay. Taking this just a little bit further, I have added one more test to the suite, I hard-coded the calculation being done by the functions. I have also added some code from my 5 year old blog post where I compared hard-coded functions, scalar functions, and itvf's.
Have a look:
--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
Here are my results on my 8 year old single processor hyper-threaded system:
(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
March 2, 2013 at 5:37 pm
Decided to up the test to Jeff's normal 1,000,000 row test:
(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
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply