May 28, 2012 at 11:04 am
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]
May 28, 2012 at 11:08 am
Okay thank you Barry, Rock, Dwain... 😀
May 28, 2012 at 1:09 pm
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
May 28, 2012 at 1:11 pm
Rock from VbCity (5/27/2012)
Thanks JeffI 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
May 28, 2012 at 1:22 pm
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
May 28, 2012 at 1:27 pm
I'm getting more records when I use a function. I just want the function to perform a calculation on certain fields. :crazy:
May 28, 2012 at 1:36 pm
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
May 28, 2012 at 1:57 pm
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
May 28, 2012 at 1:59 pm
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
May 28, 2012 at 3:20 pm
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
May 28, 2012 at 3:55 pm
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
May 28, 2012 at 4:13 pm
It works now. Thank you!
May 28, 2012 at 4:39 pm
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://qa.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
May 28, 2012 at 5:16 pm
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
May 28, 2012 at 5:41 pm
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
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply