function

Question

function

Viewing 15 posts - 16 through 30 (of 40 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

It has to do with what kinds of things the function is doing, not so much how many rows are in the table.

For the example that you posted, a simple multiplication, an inline table-valued function is fastest and simpler too, so that's definitely the way to go. For some other things, like say moderately complex parsing and/or reformatting strings, SQLCLR usually blows the doors off anything else (there are exceptions).

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

ReginaR1975 SSC Eights! Points: 967 More actions · Answer 2

ReginaR1975

SSC Eights!

Points: 967

May 28, 2012 at 11:08 am

#1493886

Okay thank you Barry, Rock, Dwain... 😀

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

SQL Kiwi (5/28/2012)
Rock from VbCity (5/27/2012)
I tried my code with Barry's function getting a result 1 versus 6 seconds in favour of Barry's.
Which just goes to show how awesome SQLCLR functions are compared with T-SQL scalar functions. However, Barry's example uses a parameterized view (an in-line table-valued function) which is fully in-lined into the calling query before optimization. The overhead of calling a SQLCLR function is very small, but it is not zero. There are cases where SQLCLR functions beat native in-lined T-SQL, but not on such a simple example as this one.

?Ummm... he said "in favor of Barry's". That sounds like Barry's code won the race. Or is that what you're saying, as well? Just can't tell from here.

I also don't trust the way people test things. I'd like to see the actual test code so I can make up my own mind.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

Rock from VbCity (5/27/2012)
Thanks Jeff
I tried my code with Barry's function getting a result 1 versus 6 seconds in favour of Barry's 🙂 I am in the process of fine-tuning my T-Sql skills so, these threads are helping me to achieve my goals!
Cheers

That's nice. Can you post the test code please so that we can have someone verify?

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

RBarryYoung (5/28/2012)
Jeff Moden (5/27/2012)
RBarryYoung (5/27/2012)
Just by way of comparision, this query:
SELECT
SUM( dbo.calc(c1.column_id, c2.column_id) / 2 )
FROM sys.system_columns c1
cross join sys.system_columns c2
took 104 seconds to execute on my system.
Whereas this query (another way of doing the same thing as my previous post):
SELECT
SUM( cc.Val3 / 2 )
FROM sys.system_columns c1
cross join sys.system_columns c2
cross apply dbo.itvfCalc(c1.column_id, c2.column_id) cc
took only 3 seconds.
How many rows do you currently have in sys.system_columns???
4,666 (SQL Server 2008 R2). Squared, that's 21,771,556.

Thanks, Barry.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ReginaR1975 SSC Eights! Points: 967 More actions · Answer 6

I'm getting more records when I use a function. I just want the function to perform a calculation on certain fields. :crazy:

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

Rock from VbCity (5/28/2012)
The in-line table valued function handled the 1.6 billions records in around 1 second, the CLR function took a bit more than 6 seconds while the remaining two approaches took more than 100 seconds!

This is why I always want to see the test code and method of meaurement, etc, etc. If your test bed is simply using a Cross Join on sys.system_columns and it only has 4,611 rows in it, then it is NOT possible for the table valued function to have handled 1.6 billion rows. In fact, using that number, there should only be 21,261,321 rows.

I missed it in my first read. I see that the iTVF won here which answers my previous question but will still test it myself. I'd still like to do my own testing but I'm no C# (or other flavor) programmer. Paul, any chance of you posting the T-SQL script to make the SQLCLR function? Thanks.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

SQL Kiwi (5/28/2012)
Jeff Moden (5/27/2012)
How many rows do you currently have in sys.system_columns???
There are 6532 rows in that table in my test database. The scalar T-SQL function :sick: ran for 4 minutes 8 seconds; Barry's in-line TVF version ran for 8 seconds.
Scalar functions (even schema-bound and not accessing data) create a complete new T-SQL context for every execution (every row) and therefore performance sucks horribly. One day, Microsoft will improve the implementation (in-lining simple scalar functions like this) but that is not what we have today. Beware the scalar UDF, whether it accesses data or not.

I agree that you should be aware of potential problems but, as always, "It Depends". For example, write your fastest iTVF to solve the 'Initial Caps' problem and see how slow it is compared to the scalar UDF version even though the scalar version has a While Loop in it.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

ReginaR1975 (5/28/2012)
I'm getting more records when I use a function. I just want the function to perform a calculation on certain fields. :crazy:

Can you post the actual code that's causing you this problem? The function that Barry posted should be working fine for this depending on how you called it.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ReginaR1975 SSC Eights! Points: 967 More actions · Answer 10

CREATE FUNCTION dbo.itvfCalc(@Val1 INT, @Val2 INT)

RETURNS TABLE As

RETURN SELECT @Val1 * @Val2 As Val3

--this returns 5 records

SELECT P.[ListPrice]*PH.StandardCost

FROM [AdventureWorks].[Production].[Product] P

INNER JOIN [AdventureWorks].[Production].[ProductCostHistory] PH ON P.ProductID = PH.ProductID

WHERE PH.[StandardCost] > 2000

--this returns 2520 records

SELECT FX.Val3

FROM [AdventureWorks].[Production].[Product] P

CROSS JOIN [AdventureWorks].[Production].[ProductCostHistory] PH

CROSS APPLY dbo.itvfCalc(P.ProductID,PH.ProductID) FX

WHERE PH.[StandardCost] > 2000

-------------------------------------------------------------------------------------------

CREATE FUNCTION dbo.itvfCalc(@Val1 INT, @Val2 INT)

RETURNS TABLE As

RETURN SELECT @Val1 * @Val2 As Val3

CREATE FUNCTION dbo.itvfCalc2(@Val1 INT, @Val2 INT)

RETURNS TABLE As

RETURN SELECT @Val1 + @Val2 As Val3

--this returns 5 records

SELECT P.[ListPrice]*PH.StandardCost,

P.[ListPrice] + PH.StandardCost

FROM [AdventureWorks].[Production].[Product] P

INNER JOIN [AdventureWorks].[Production].[ProductCostHistory] PH ON P.ProductID = PH.ProductID

WHERE PH.[StandardCost] > 2000

--this returns 995,400 records

SELECT FX.Val3,

FX2.Val3

FROM [AdventureWorks].[Production].[Product] P

CROSS JOIN [AdventureWorks].[Production].[ProductCostHistory] PH

CROSS APPLY dbo.itvfCalc(P.ProductID,PH.ProductID) FX

CROSS JOIN [AdventureWorks].[Production].[ProductCostHistory] PH2

CROSS APPLY dbo.itvfCalc(P.ProductID,PH.ProductID) FX2

WHERE PH.[StandardCost] > 2000

Rock from VbCity Hall of Fame Points: 3991 More actions · Answer 11

Hi ReginaR1975

It seems your testing query on the in-line table valued is missing a join column

--this returns 5 records

SELECT P.[ListPrice]*PH.StandardCost

FROM [AdventureWorks].[Production].[Product] P

INNER JOIN [AdventureWorks].[Production].[ProductCostHistory] PH ON P.ProductID = PH.ProductID

WHERE PH.[StandardCost] > 2000

--this returns 2520 records

SELECT FX.Val3

FROM [AdventureWorks].[Production].[Product] P

CROSS JOIN [AdventureWorks].[Production].[ProductCostHistory] PH

CROSS APPLY dbo.itvfCalc(P.ProductID,PH.ProductID) FX

WHERE PH.[StandardCost] > 2000

I will try

SELECT FX.Val3

FROM [AdventureWorks].[Production].[Product] P

INNER JOIN [AdventureWorks].[Production].[ProductCostHistory] PH ON P.ProductID = PH.ProductID

CROSS APPLY dbo.itvfCalc(P.ProductID,PH.ProductID) FX

WHERE PH.[StandardCost] > 2000

Cheers,

Hope this helps,
Rock from VbCity

ReginaR1975 SSC Eights! Points: 967 More actions · Answer 12

ReginaR1975

SSC Eights!

Points: 967

May 28, 2012 at 4:13 pm

#1493920

It works now. Thank you!

Paul White SSC Guru Points: 150467 More actions · Answer 13

Jeff Moden (5/28/2012)
?Ummm... he said "in favor of Barry's". That sounds like Barry's code won the race. Or is that what you're saying, as well?

Of course. I thought I was clear, but let me restate it:

In-line T-SQL: 1 second.

SQLCLR scalar function: 6 seconds.

T-SQL scalar function: 107 seconds.

The comparison between T-SQL scalar function and SQLCLR scalar function is stark.

Paul, any chance of you posting the T-SQL script to make the SQLCLR function?

See following post.

For example, write your fastest iTVF to solve the 'Initial Caps' problem and see how slow it is compared to the scalar UDF version even though the scalar version has a While Loop in it.

There are a few edge cases where implementation specifics of T-SQL scalar and multi-statement functions happen to provide a performance benefit, but this is by accident not design. It is normally possible to use temporary tables to provide the accidental benefit in a more robust way. As you know (http://www.sqlservercentral.com/Forums/FindPost1299037.aspx) the fastest solution for the 'Initial Caps' problem is the one-line SQLCLR function.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 14

OK, here's a SQLCLR implementation (with integer overflow checking):

CREATE ASSEMBLY SSC

FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300BB06C44F0000000000000000E00002210B010800000800000006000000000000EE2600000020000000400000000040000020000000020000040000000000000004000000000000000080000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000009C2600004F000000004000005003000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000F4060000002000000008000000020000000000000000000000000000200000602E72737263000000500300000040000000040000000A0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000000E00000000000000000000000000004000004200000000000000000000000000000000D0260000000000004800000002000500602000003C0600000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000120203D82A1E02280E00000A2A00000042534A4201000100000000000C00000076322E302E35303732370000000005006C000000B4010000237E0000200200003002000023537472696E6773000000005004000008000000235553005804000010000000234755494400000068040000D401000023426C6F620000000000000002000001471500000900000000FA253300160000010000000E0000000200000002000000020000000E0000000B000000010000000200000000000A000100000000000600370030000600720060000600890060000600A60060000600C50060000600DE0060000600F700600006001201600006002D0160000600650146010600790160000600B20192010600D20192010A001B0200020000000001000000000001000100010010001200000005000100010050200000000096003E000A00010055200000000086184E0010000300000001005400000002005A0011004E00140019004E00140021004E00140029004E00140031004E00140039004E00140041004E00140049004E00140051004E00190059004E00140061004E001E0069004E00100071004E00100009004E00100020006B0023002E002B006B012E00130074012E001B0074012E0023007A012E000B006B012E00330089012E003B0074012E004B0074012E005B00AA012E006300B3010480000001000000B4110556000000000000F001000002000000000000000000000001002700000000000200000000000000000000000100F401000000000000003C4D6F64756C653E005353432E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A65637400496E74656765724D756C7469706C79002E63746F720045787072310045787072320053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465005353430053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E41747472696275746500000320000000000036BDA4767499F34D951EA5E9070ADE140008B77A5C561934E08905000208080803200001042001010E0420010102042001010881460100040054020F497344657465726D696E697374696301540209497350726563697365015455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D44617461416363657373000000000801000353534300000501000000000E0100094D6963726F736F667400002001001B436F7079726967687420C2A9204D6963726F736F6674203230313200000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010000C42600000000000000000000DE260000002000000000000000000000000000000000000000000000D0260000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002040000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000F80200000000000000000000F80234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000556B411000001000556B4113F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00458020000010053007400720069006E006700460069006C00650049006E0066006F00000034020000010030003000300030003000340062003000000034000A00010043006F006D00700061006E0079004E0061006D006500000000004D006900630072006F0073006F00660074000000300004000100460069006C0065004400650073006300720069007000740069006F006E0000000000530053004300000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0034003500330032002E00320032003000320031000000000030000800010049006E007400650072006E0061006C004E0061006D00650000005300530043002E0064006C006C0000005C001B0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020004D006900630072006F0073006F006600740020003200300031003200000000003800080001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000005300530043002E0064006C006C000000280004000100500072006F0064007500630074004E0061006D00650000000000530053004300000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0034003500330032002E00320032003000320031000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0034003500330032002E0032003200300032003100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000F03600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

WITH PERMISSION_SET = SAFE;

GO

CREATE FUNCTION dbo.IntegerMultiply

(

@Expr1 integer,

@Expr2 integer

)

RETURNS integer

AS EXTERNAL NAME SSC.UserDefinedFunctions.IntegerMultiply;

Source code:

using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions

{

[SqlFunction

(

IsDeterministic = true,

IsPrecise = true,

DataAccess = DataAccessKind.None,

SystemDataAccess = SystemDataAccessKind.None

)

]

public static int IntegerMultiply(int Expr1, int Expr2)

{

checked

{

return Expr1 * Expr2;

}

};

-- 3749 for me

SELECT COUNT(*) FROM sys.system_columns AS sc

-- 2 seconds

SELECT

SUM(sc.column_id * sc2.column_id / 2)

FROM sys.system_columns AS sc

CROSS JOIN sys.system_columns AS sc2;

-- 6 seconds

SELECT

SUM(dbo.IntegerMultiply(sc.column_id, sc2.column_id) / 2)

FROM sys.system_columns AS sc

CROSS JOIN sys.system_columns AS sc2;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Rock from VbCity Hall of Fame Points: 3991 More actions · Answer 15

I did not try the query without referencing a function; the query below took a bit more than a second on my environment.

-- 2 seconds

SELECT

SUM(sc.column_id * sc2.column_id / 2)

FROM sys.system_columns AS sc

CROSS JOIN sys.system_columns AS sc2;

It seems the OP wants to implement a function to encapsulate a business rule that will be used in several places in her solution.

Hope this helps,
Rock from VbCity