Post reply

Use GETDATE() within a UDF

  • November 20, 2007 at 9:11 am

    #752764

    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?

  • November 20, 2007 at 10:06 pm

    #752951

    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)
    Intro to Tally Tables and Functions

  • November 21, 2007 at 4:36 am

    #753034

    Same topic (http://qa.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!

  • November 21, 2007 at 8:46 pm

    #753269

    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?

  • November 22, 2007 at 1:33 am

    #753316

    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?

  • November 23, 2007 at 4:32 pm

    #753713

    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)
    Intro to Tally Tables and Functions

  • November 27, 2007 at 3:48 am

    #754454

    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.

  • November 27, 2007 at 8:04 pm

    #754801

    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)
    Intro to Tally Tables and Functions

  • November 28, 2007 at 1:06 am

    #754844

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

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

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