Home Forums Article Discussions Article Discussions by Author Discuss content posted by Jim Youmans Find UDF useage RE: Find UDF useage

  • March 23, 2017 at 1:36 pm

    #1934646

    Jim Youmans-439383 - Thursday, March 23, 2017 6:13 AM

    While I agree with you in general, in my case the Scalar UDF was the issue.  It was a simple convert INT to DATE (20170101 to 01/01/2017) in the select part of the query.  The UDF was being called for every row of the result set (in this case it was about 250K).  By replacing the UDF with TSQL code, I was able to improve the performance tremendously.  I wanted to be able to see what other stored procedures were calling UDFs.

    Thanks!

    Oh yes... I absolutely agree and even said similar in my writeup.  IF a UDF is actually the cause of a performance problem, it should be fixed. 

    And, to be clear, none of what I said was a slam on your code or article.  I just had this vision that someone that may not know better may take your good code and make it an urgent mission to replace all Scalar UDFs in all their databases instead of correctly identifying possibly much more serious problems and fixing those first.

    To be sure, thank you for taking the time to submit the code, explain why you wrote it, and participate in this discussion.  Well done and thank you for making a difference.

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