Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

UDF Help Expand / Collapse
Author
Message
Posted Wednesday, February 27, 2013 7:28 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:41 PM
Points: 37,078, Visits: 31,638
Jeff Moden (2/27/2013)
TheSQLGuru (2/27/2013)
You may THINK you want to do a Scalar UDF, but you probably don't. I wrote a chapter for the SQL Server MVP Deep Dives 2 book entitled "Death by UDF". It is a very aptly named missive. Please do everything in your power to avoid the UDF and inline the code where you need it.

Oh, I haven't tested it, but I wonder if UDFs can call UDFs ...


Yes they can. if they're not iTVFs, it about as joyous as an aggregated view calling an aggregated view.


I guess that about sums up this thead.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1424531
Posted Wednesday, February 27, 2013 2:31 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:50 PM
Points: 2,087, Visits: 3,135
TheSQLGuru (2/27/2013)
. . .it about as joyous as an aggregated view calling an aggregated view.


You can do THAT TOO in SQL Server?!? Jeez, what a suck-@ss product!


No, those are "features", that add "flexibility" .


SQL DBA,SQL Server MVP('07, '08, '09)

"While in these days of quiet desperation /
As I wander through the world in which I live /
I search everywhere for some new inspiration /
But it's more than cold reality can give /
If I need a cause for celebration /
Or a comfort I can use to ease my mind /
I rely on my imagination /
And I dream of an imaginary time" : the inimitable Mr. Billy Joel
Post #1424751
Posted Wednesday, February 27, 2013 8:10 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:41 PM
Points: 37,078, Visits: 31,638
TheSQLGuru (2/27/2013)
. . .it about as joyous as an aggregated view calling an aggregated view.


You can do THAT TOO in SQL Server?!? Jeez, what a suck-@ss product!


BWAAA-HAAAA!!!


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1424850
Posted Thursday, February 28, 2013 8:08 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 581, Visits: 2,709
Jeff Moden (2/25/2013)
Alan.B (2/25/2013)

If i am understand right. Function can return only one value. am i right?


Scalar functions return one value, table valued functions return a table variable.


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.


I will confess, I have always used Scalar functions for returning a single value; I never thought to use an iTVF like that...

Unless I am misreading your comment - you are saying that scalar functions are worthless? Are there any cases where you would Scalar function over an iTVF that returns a single element?


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1425059
Posted Thursday, February 28, 2013 10:12 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:20 PM
Points: 4,356, Visits: 6,188
Alan.B (2/28/2013)
Jeff Moden (2/25/2013)
Alan.B (2/25/2013)

If i am understand right. Function can return only one value. am i right?


Scalar functions return one value, table valued functions return a table variable.


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.


I will confess, I have always used Scalar functions for returning a single value; I never thought to use an iTVF like that...

Unless I am misreading your comment - you are saying that scalar functions are worthless? Are there any cases where you would Scalar function over an iTVF that returns a single element?


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). However, if you use that scalar UDF directly in a SELECT/WHERE clause you can get totally hosed in several ways.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1425168
Posted Thursday, February 28, 2013 10:19 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 23,397, Visits: 32,237
Comes down to if the scalar function is being called once per query or once per row in the query.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1425172
Posted Thursday, February 28, 2013 10:25 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:20 PM
Points: 4,356, Visits: 6,188
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 at GMail
Post #1425175
Posted Friday, March 1, 2013 12:16 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:41 PM
Points: 37,078, Visits: 31,638
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1425376
Posted Friday, March 1, 2013 7:16 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:20 PM
Points: 4,356, Visits: 6,188
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.
http://www.sqlservercentral.com/articles/T-SQL/91724/


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 at GMail
Post #1425503
Posted Friday, March 1, 2013 1:46 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 581, Visits: 2,709
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?


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1425720
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse