April 22, 2016 at 7:35 am
Firstly, the SQL used is bad/antipattern and should never be coded this way and I know that, and is specifically written to cause iteration on an entire table for performance comparisons of functions.
With that out of the way,
It seems like natively compile functions perform poorer than normal scalar functions.
Is there a test or implementation that shows that it is clearly better than normal functions?
My test is below with results and so far see no compelling reason to use this feature.
Am I missing something?
This was run on my Laptop which is why the times are so high, but all things being equal, Native takes longer.
use AdventureWorks2016CTP3
GO
--Taken from a post by Lowell
--Begin Lowell's code
SELECT TOP 10000000
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM MASTER.dbo.SysColumns sc1,
MASTER.dbo.SysColumns sc2
GO
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
GO
--END Lowell's code
declare @time datetime = getdate()
select dbo.ufnLeadingZeros(N) from Tally where dbo.ufnLeadingZeros(N) = '09999999'
select datepart(second,getdate()-@time)
select @time = getdate()
select dbo.ufnLeadingZeros_Native(N) from Tally where dbo.ufnLeadingZeros_Native(N) = '09999999'
select datepart(second,getdate()-@time)
Results
ufnLeadingZeros Run1 Time(s) 35
ufnLeadingZeros Run2 Time(s) 36
ufnLeadingZeros_Native Run1 Time(s) 55
ufnLeadingZeros_Native Run2 Time(s) 46
April 23, 2016 at 9:48 am
Can you perhaps also post the code of the UDF used? Both the T-SQL version and the natively compiled version, of course...
April 23, 2016 at 9:56 am
April 23, 2016 at 1:13 pm
Sorry, was not aware you were using standard sample code.
I set up a SQL 2016 CTP3 VM on my Azure account and ran the same code you used. After changing the DATEDIFF to show milliseconds, I got results quite different from yours. Consistently, the native compiled version of the procedure was about twice as fast as the T-SQL version. The native version usually took between 250 and 500 ms; the T-SQL version between 500 and 1000 ms.
However, you are working with a beta - no, sorry, it's called CTP now - version, so it is very well possible that there is something wrong related to your specific settings or your specific hardware. I suggest filing this as a bug on Connect, providing as much information as you can about your hardware and the settings of the VM.
April 25, 2016 at 3:48 am
Thanks Hugo for giving this a review.
The test you ran with the 250ms, was it on one row or the entire table?
And was it on super powerful hardware?
I reran the tests with statistics time on, which I avoided the first time round cos I was using UDF's.
Maybe the native function is better when working in isolation but may be a bit slower when linking to pages?
The top test has a filter and the bottom one does not, but is constantly assigning between a variable and function output.
(Don't know what I am testing but seemed good in my head.)
These are my stats on my laptop
oh, and I neglected to say last time,
My version = Enterprise Evaluation Edition (64-bit) version 13.0.1400.361,EngineEdition = 3,Collation = Latin1_General_CI_AS
set statistics time,io on
declare @time datetime = getdate()
select dbo.ufnLeadingZeros(N) from Tally where dbo.ufnLeadingZeros(N) = '0000000'
--Table 'Tally'. Scan count 1, logical reads 16108, physical reads 1049, read-ahead reads 9797, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--CPU time = 37656 ms, elapsed time = 43831 ms.
select dbo.ufnLeadingZeros_Native(N) from Tally where dbo.ufnLeadingZeros_Native(N) = '0000000'
--Table 'Tally'. Scan count 1, logical reads 16108, physical reads 3, read-ahead reads 14147, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--CPU time = 46047 ms, elapsed time = 47707 ms.
GO
declare @var int
select @var=dbo.ufnLeadingZeros(N) from Tally
--Table 'Tally'. Scan count 1, logical reads 16108, physical reads 1361, read-ahead reads 16103, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- SQL Server Execution Times:
-- CPU time = 40922 ms, elapsed time = 48142 ms.
select @var=dbo.ufnLeadingZeros_Native(N) from Tally
--Table 'Tally'. Scan count 1, logical reads 16108, physical reads 1197, read-ahead reads 6741, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- SQL Server Execution Times:
-- CPU time = 53188 ms, elapsed time = 56652 ms.
April 25, 2016 at 3:41 pm
MadAdmin (4/25/2016)
Thanks Hugo for giving this a review.The test you ran with the 250ms, was it on one row or the entire table?
And was it on super powerful hardware?
It was the exact code you posted. So the UDF ran on every row in order to be ab le to select the one row that qualifies.
The hardware is a VM on Windows Azure, set up through the wizard. The size I chose is "D2_V2 Standard", which gives 2 cores, 7 GB of RAM, 4 data disks of 500 max IOPS each, 100 GB local SSD, load balancing and auto scale.
(And in case you wonder, setting up the VM, running the test, the disabling the VM to stop the charges but without deleting it so I can reuse it later cost me a whopping € 0.06 out of the monthly € 130.00 allowance I have as part of my MSDN subscription).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply