Use GETDATE() within a UDF

Question

Use GETDATE() within a UDF

Viewing 9 posts - 16 through 23 (of 23 total)

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

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

From a sql-server-performance posting (edited):

Carl Federl (11/16/2005)
I may change my mind on UDFs.
I am in the process of writting an article and a set of test cases but here is an interesting post:

Did you ever finish that article?

When using a UDF in the where, run time goes from < .01 seconds to 2.14 minutes (134 seconds)
-- Comment this is 13 thousand times slower

I also see bad performance hits (which is why I wound up in this old thread), but the question is why? In any other language one would assume that function calls were badly implemented; why does T-SQL get a pass?

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

Jim,

Is this in reference to the table variable function you created that calls itself?

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

Same topic (http://www.sqlservercentral.com/Forums/FindPost424410.aspx), but I don't see anywhere the function is calling itself. You may have found the key, but I am still missing it. The table returning function being called isn't recursive; the only difference is the utility functions to get the parameters. If the utility function return values are placed in local variables, and those variables are then passed as arguments to the table returning function, it runs fast. If instead, the utility functions are in-lined in the table returning function calling sequence, it runs forever.

I appreciate your interest in this; help me see the light!

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Jim Russell (11/20/2007)
When using a UDF in the where, run time goes from < .01 seconds to 2.14 minutes (134 seconds)
-- Comment this is 13 thousand times slower
I also see bad performance hits (which is why I wound up in this old thread), but the question is why? In any other language one would assume that function calls were badly implemented; why does T-SQL get a pass?

Because often enough the Optimizer is "too stupid" to know what it doesn't need to repeat for each row in a table. The biggest and most common reason the performance goes "south" like that is that the query goes from being a seek to a scan (usually an index seek to a table scan), because the function's output is not determined to be deterministic, so it is run once for every row (often time even when the function is fully deterministic from a human standpoint, but NOT from a SQL Server standpoint).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

developer6 Right there with Babe Points: 717 More actions · Answer 5

Can I assume that it is acceptable to put parameterized sql into a stored procedure? Nobody is saying don't do that, right?

What solution would people suggest if the table returned from a stored procedure needed (was desired) to be joined to another table? Or would that simply be another indicator of flawed thinking?

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

Output from a stored procedure does not have a PK Constraint and cannot be indexed unless first materialized as a table. So, it depends...

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

developer6 Right there with Babe Points: 717 More actions · Answer 7

Jeff:

Obviously everything always depends... I've previously used UDFs where I've had some complex query and the result needs to be reused in multiple places, which has not exactly improved performance.

I guess what I'm interested in is how other people manage a similar situation. Stored procedures and temporary tables? Cut and paste? etc.

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

If it's within the same proc, yes... I'll many times use a temp table with indexes, etc. If there are several procs that require the same process, I'll build the temp table within each proc and call the common proc with the understanding that it may use the temp table. I'd rather do it that way than to use a Global Temp table.

In other instances, I'll create a "real" working table that I leave as a table just to see what the last results were.

Then, there's tools like the "Tally" or "Numbers" table that have allowed me to circumvent the need for a great number of UDF's including Split functions, sequential date or date/time bin creation, etc, etc.

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

developer6 Right there with Babe Points: 717 More actions · Answer 9

Thanks Jeff - it's always good to know what others are doing.