functions

Question

functions

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

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

Johan Bijnens SSC Guru Points: 135259 More actions · Answer 1

Oh yes, functions can be like troyan horses,....

they may serve you very well, even for a long time, until your system reaches the condition for them to burst open ....

That's why you should avoid functions that do more than just perform e.g a calculation, based on input parameters (no extra stuff ...).

All other things should be done using proper joins to tables, views, nested table expressions, @- #- ##temptables, ....

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Brookstone SSCommitted Points: 1745 More actions · Answer 2

Thanks again for all the help and insight. We are going to rewrite the process to eliminate the functions.

Much appreciated!

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 3

FWIW - I noted a reference to @today (or something) earlier in your comments. I have been bit big time by T-SQL deciding that a time variable (e.g. from GetDate) was nondeterministic, even if the date/time parameter was passed in as a variable. I think it is a bug, but can't get anyone to agree.

Sergiy SSC Guru Points: 110206 More actions · Answer 4

Jim Russell (9/16/2008)
FWIW - I noted a reference to @today (or something) earlier in your comments. I have been bit big time by T-SQL deciding that a time variable (e.g. from GetDate) was nondeterministic, even if the date/time parameter was passed in as a variable. I think it is a bug, but can't get anyone to agree.

I disagree.

I have several UDF's taking datetime parameter, and all are deterministic.

_____________
Code for TallyGenerator

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

Can you show us one, Sergiy... so we can get the idea of how to do it? 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)

Sergiy SSC Guru Points: 110206 More actions · Answer 6

Jeff Moden (9/16/2008)
Can you show us one, Sergiy... so we can get the idea of how to do it? Thanks.

I've already shown you once.

You should just avoid implicit conversions and specify style for explicit ones.

Like this:

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

IF EXISTS (SELECT * FROM sysobjects WHERE name = N'DateOnly')

DROP FUNCTION DateOnly

GO

CREATE FUNCTION dbo.DateOnly (@DT datetime)

RETURNS datetime

WITH SCHEMABINDING

AS

BEGIN

DECLARE @ZD datetime

SET @ZD = convert(datetime,0,120)

RETURN dateadd(dd,datediff(dd,@ZD,@DT),@ZD)

END

GO

SELECT dbo.DateOnly (getdate())

GO

SELECT object_id('dbo.DateOnly'), OBJECTPROPERTY ( object_id('dbo.DateOnly'), 'IsDeterministic')

_____________
Code for TallyGenerator

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

Yeah... I know, Sergiy. Really just wanted you to post it on this thread so others can learn. 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)

Sergiy SSC Guru Points: 110206 More actions · Answer 8

You always welcome.

If needed no problem to post it 3rd and 4th time.

I just wanted OP to ask. 😉

_____________
Code for TallyGenerator

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

Heh... Got it. 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)

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 10

Sergie:

Interesting that with the "WITH SCHEMABINDING" clause removed, the return is 0 = Not Deterministic. I guess I don't understand why that has anything to do with it. Is that the magic key to the whole nondeterministic performance hits when getdate() is used?

Perhaps you could shed some light on this old thread:

http://www.sqlservercentral.com/Forums/Topic424279-360-1.aspx

(Actually, in that test I was using CURRENT_TIMESTAMP instead of GETDATE(). Should that make any difference?)

Sergiy SSC Guru Points: 110206 More actions · Answer 11

Jim Russell (9/18/2008)
Sergie:
Interesting that with the "WITH SCHEMABINDING" clause removed, the return is 0 = Not Deterministic. I guess I don't understand why that has anything to do with it. Is that the magic key to the whole nondeterministic performance hits when getdate() is used?

In times of doubts open the Book. Book on Line.

It has very good explanation about functions determinism and conditions you need to satisfy to make a function deterministic.

I don't know what performance hits related to GETDATE() you're talking about, never experienced ones.

Perhaps you could shed some light on this old thread:
http://www.sqlservercentral.com/Forums/Topic424279-360-1.aspx

It's quite hard to do. You did not provide any clue about the way you functions are built.

Look in the article mentioned in Jeff's signature and follow the instructions.

And I bet your problem has noting to do with determinism.

(Actually, in that test I was using CURRENT_TIMESTAMP instead of GETDATE(). Should that make any difference?)

Again - open BOL. It clearly states that CURRENT_TIMESTAMP and GETDATE() are functionally identical.

_____________
Code for TallyGenerator

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 12

You are right Sergie, and I'm convinced that I need to create a simple sample that demonstrates the problem. (But that is going to take a while.)

I got some more replies via Connect, but it was not entirely satisfactory. There the blame was placed on UDFs, and the advice was to not use them.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2481958&SiteID=1

I'm still confused by the impact of "WITH SCHEMABINDING" where BOL makes no mention of it's impact on determinism. However, BOL clearly states that CURRENT_TIMESTAMP and GETDATE() are both nondeterministic functions.

For the time being, I'll assume that with SCHEMABINDING, T-SQL is happy to assume that the function is deterministic, and avoids repeated evaluations. (Part of my original problem, and not a factor in your test.) I'll be curious to see if that has the same impact in a case where the function includes calls to GETDATE().

Sergiy SSC Guru Points: 110206 More actions · Answer 13

Jim Russell (9/18/2008)
I got some more replies via Connect, but it was not entirely satisfactory. There the blame was placed on UDFs, and the advice was to not use them.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2481958&SiteID=1

Usual advice from SQL cowboys.

They also always suggest not to use views, triggers, etc.

Because they just don't know how to write correct SQL code.

For the time being, I'll assume that with SCHEMABINDING, T-SQL is happy to assume that the function is deterministic, and avoids repeated evaluations. (Part of my original problem, and not a factor in your test.) I'll be curious to see if that has the same impact in a case where the function includes calls to GETDATE().

Jim, you have absolute mess in you head about function determinism.

You need to slow down, take some time and study the subject.

To give you an idea how wrong your believes are I recommend you simple test:

SELECT TOP 100000 C1.*

FROM syscolumns C1, syscolumns C2, syscolumns C3

It's a big triple cross-join generation enormous recordset.

I put limit of 100k rows not to blow the server.

Note how long will it take to execute it.

Now add non-deterministic function:

SELECT TOP 100000 C1.*, GETDATE()

FROM syscolumns C1, syscolumns C2, syscolumns C3

Now see the difference.

On my local machine it's less than 5%.

Just about the time required to pass extra column from Server to QA.

So, nondeterministic function DOES NOT SLOW THE QUERY.

_____________
Code for TallyGenerator

ezlik3sundaymorning Old Hand Points: 333 More actions · Answer 14

What would cause this type of behavior could be various reasons, the most likely cause to me would be a lack of statistics, or an undocumented change, or an update to the system that adversely affected other things as well.

As stated previously, if you are talking about small lookups, I generally use CTE's to join off of for small data sets but if the data changes row by row then you definitely need something larger that would encompass the entire set and join against them. Since I would assume a scalar function. I will give you an example that I have had.

With only parsing 100K records, removing inline(Scalar) functions provided my client with a query that was reduced from 2 min to miliseconds due to the ROW BY ROW processing rather than performing a set based operation.

My suggestion would be to look up some of Jeff Modens articles on SSC. and give your developers some training on how to optimize the code before it comes to you.

Tally Tables to eliminate cursors and loops and always perform set based operations where possible. I have had clients that can not see that RBR processing is not the way to go and fight it until they see the performance gains that it gives to their application and users. Then they must check it 100 times or more to make sure the numbers match, after that you will be a genius and you really are just doing your job.

Be Kewl and hope that this helps.

Dex

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 15

Thanks Dex, I appreciate your insights.

And, I am now reminded that a promised months ago to post a sample that exhibits the problem I thought I was seeing. I'll tie the string around my finger tighter, and see if I can reproduce it, so you guys can tell me what stupid thing I am doing.

(RE: "My suggestion would be to look up some of Jeff Modens articles on SSC. and give your developers some training on how to optimize the code before it comes to you." I always believe everything Jeff says, but there are no developers here, just me. So if there is training to be done, it's me what needs it! 🙂 )