function

Question

Post reply

function

ReginaR1975

SSC Eights!

Points: 967
More actions
May 27, 2012 at 6:36 pm

#269541

I need to create a function for multiple stored procedures so it's easy to manage changes. It's slow. How do you optimize this? :unsure:
CREATE FUNCTION [dbo].[Calc](@Val1 INT, @Val2 INT)
RETURNS INT
AS
BEGIN
DECLARE @VAL3 INT = (SELECT @Val1 * @Val2)
RETURN @VAL3
END
SELECT A.Name,
A.Procedure,
A.Age,
dbo.Calc(A.height, A.weight),
B.Address,
B.City,
B.State,
B.Zip
From Table1 A
INNER JOIN Tanle2 B ON A.ID = B.ID
Create a New Project is BIDS

1.

2. File -> New -> Project

3. Select SQL CLR project (Database Project -> Microsoft SQL Server -> SQL SLR)

4. Click on SQL Server Project

5. Enter a name for your project, I entered SSCfunctions

6. Select the target database to deploy your project; if your database is not on the list, just add it.

7. Answer NO to the prompt if you wish to enable SQL/CLR debugging.

8. Now add an User-Defined Function ... to your project by selecting Project -> Add User-Defined Function at the BIDS IDE (as shown below)

or, you may add it via the Solution Explored; right-click on your project name -> select Add -> select User-Defined Function ...

9. On the Add New Item select the User-Defined Function template and give it a name, like Calc (be sure it ends with .cs if you are using C# or .vb if you are using Vb.Net); then click the Add button.

10. BIDS added the function Calc to the project, into the class UserDefinedFunctions (do not worry about this class name); the default function type is SqlString and it returns a SqlString "Hello"; We will change this function to return an integer and accept two integer parameters.

11. Replace the function type to SqlInt32; add the parameters as shown in the picture; and replace the function's body with the code shown.

12. Now, let's deploy the function, right-click at the project on the Solution Explorer, and select deploy; the image below shows these steps (a) and (b) on the BIDS IDE and the SSMS' database SSIS_PDS_LAB showing its Assemblies node, the new function binary file lands at this node 🙂 Depending on your environment (and permissions) the deployment should take about 30 seconds.

13. Once you deploy your project, SSMS Object explored will show the assembly (named SqlClassLibrary at its Assemblies node) and the function dbo.Calc on the Scalar-valued Functions node in the Programmability-Functions leafs

14. The assembly name is SqlClassLibrary because we did not change it at BIDS, you can change it by selecting the Properties option on the right-click menu of the solution located on the solutions explorer of BIDS

15. It is time to test the new function at SSMS, just open a new query, select the target database, USE SSIS_PDS_LAB (I am sure you will be using a different database); then type the code below:

use SSIS_PDS_LAB

go

select dbo.Calc(4,5) as test

It will fail with an error as shown below; it happens because we haven't enabled CLR, which is the next step.

16. You enable CLR with the following code:

EXEC sp_configure 'show advanced options', 1

RECONFIGURE

EXEC sp_configure 'clr_enabled', 1

RECONFIGURE

EXEC sp_configure 'show advanced options', 0

RECONFIGURE

This is a one-off requirement and your function is ready to rock-n-roll

17. Executing the function 🙂 Now the code shown on the Step 15 should work, the first time it will take time (less than 5 seconds, or probably 10), as SQL is initializing the CLR environment, once it is initilized, future references to the functions will go as very good speed.

18. You can reference the function from other databases as shown in the next query

use SSIS_PDS

go

SELECT ssis_pds_lab.dbo.Calc(4,5) as testing

We prefixed the function with the name of the database containing it.

Hope this helps,
Rock from VbCity

Dwain Camps SSC Guru Points: 86978 More actions · Answer 1

You might try the hint from this article:

http://www.mssqltips.com/sqlservertip/1692/using-schema-binding-to-improve-sql-server-udf-performance

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

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

Hi ReginaR1975

You may follow the suggestions found at the link posted by dwain.c or enable the CLR (Common Language Runtime); the CLR allows you to code functions in Vb.Net or C# (or any other language supported by the Net Framework) and reference them in your T-SQLs.

The steps required to implement your function are:

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Well, not that all that isn't fun, but here's another way to go:

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

RETURNS TABLE As

RETURN SELECT @Val1 * @Val2 As Val3

SELECT A.Name,

A.[Procedure],

A.Age,

(SELECT Val3 FROM dbo.itvfCalc(A.height, A.weight)),

B.Address,

B.City,

B.State,

B.Zip

From Table1 A

INNER JOIN Table2 B ON A.ID = B.ID

I cannot test it of course without your DDL or any test data, but this should address much of your performance issues.

[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]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

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.

[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]

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

Rock from VbCity (5/27/2012)
Hi ReginaR1975
You may follow the suggestions found at the link posted by dwain.c or enable the CLR (Common Language Runtime); the CLR allows you to code functions in Vb.Net or C# (or any other language supported by the Net Framework) and reference them in your T-SQLs.

Let's see if all of that is actually worth it. Please post the performance for a simple million row test. Of course, you should post the code for the million row test so that others can confirm your findings.

--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: 1004406 More actions · Answer 6

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

--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: 1004406 More actions · Answer 7

ReginaR1975 (5/27/2012)
I need to create a function for multiple stored procedures so it's easy to manage changes. It's slow. How do you optimize this? :unsure:
CREATE FUNCTION [dbo].[Calc](@Val1 INT, @Val2 INT)
RETURNS INT
AS
BEGIN
DECLARE @VAL3 INT = (SELECT @Val1 * @Val2)
RETURN @VAL3
END
SELECT A.Name,
A.Procedure,
A.Age,
dbo.Calc(A.height, A.weight),
B.Address,
B.City,
B.State,
B.Zip
From Table1 A
INNER JOIN Tanle2 B ON A.ID = B.ID

Gosh... I'm thinking that even though that's a scalar UDF, it IS a memory only scalar UDF and there's a pretty good chance that function isn't the main problem.

Take a look at the second link in my signature line below. If you can provide the information outlined in that article the way it's outlined, we stand a much better chance of helping you lick this problem.

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

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

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

Hope this helps,
Rock from VbCity

Paul White SSC Guru Points: 150468 More actions · Answer 9

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.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Paul White SSC Guru Points: 150468 More actions · Answer 10

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.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

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.

[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]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 12

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

Rock: By the way, that was an awesome post above. I'd strongly encourage you to submit it as an article to Steve Jones (the editor here). Don't be dissuaded by these results, as Paul says, it's hard to beat in-lining for something this simple. There are many calculations only moderately more complex where SQLCLR beats everything else hands-down.

[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]

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

Thanks Barry,

I will find out the structure or format of the site's articles and approach Steve Jones, I am not discourage, actually I went into comparing the four solutions discussed in this topic and the CLR approach got a very good second place, I will write down my findings for future members visiting this topic.

ISSUE

The function listed below was performing very slow:

CREATE FUNCTION [dbo].[Calc](@Val1 INT, @Val2 INT)

RETURNS INT

AS

BEGIN

DECLARE @VAL3 INT = (SELECT @Val1 * @Val2)

RETURN @VAL3

END

when used in a query like this:

SELECT

A.Name

, A.Procedure

, A.Age

, dbo.Calc(A.height, A.weight)

, B.Address

, B.City

, B.State

, B.Zip

From Table1 A

INNER JOIN Tanle2 B ON A.ID = B.ID

We did not have the structure, sample data of the two tables involved: Table1 and Tanle2; We overcome this constraint by using system_columns table.

OPTIONS

We discussed 4 options, e.g.

1. The initial function given by the Original Posted (ReginaR1975)

2. Implement SCHEMABINDING as suggested by dwain.c

3. Implement a CLR as suggested by Rock.

4. Implement an in-line table valued function as suggested by RBarryYoung

SOLUTION

We used the following query to find out the performance of these four options

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

Replacing the function as required.

The result found is shown in the following summary

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!

Cheers,

Hope this helps,
Rock from VbCity

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

I have about 1.5 million records in Table2. When should I use in-line table valued function as suggested by RBarryYoung vs CLR as suggested by Rock?