Bad Scalar Functions (or other UDF)

  • If you can share a story of a real scalar or multi-statement table UDF and how you fixed it, I'd like articles here. Even if the fix is changing application code.

    The idea is to help people understand the issues with functions and performance by providing concrete examples.

  • Steve,

    See if Kevin Boles has any horror stories that aren't confidential. =)

  • Kevin doesn't like writing. I need someone that wants to document and describe an issue.

  • Steve Jones - SSC Editor (5/19/2016)


    I need someone that wants to document and describe an issue.

    I've recently fixed a monster that was necessarily (used in a Persisted Computed Column) a Scalar Function and I started to write about it.

    Don't let this hold anyone else up, though. This sounds like a very wide open topic that a whole lot of people could have fun with.

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

  • Thanks, Jeff. Hoping for a few pieces. We have the one you wrote on moving a scalar UDF to a CROSS APPLY, but I'd like to get more references for specific functions that might do strange things.

  • I recently found one or two issues at work with scalar functions. I would be happy to contribute an article with examples.

  • sqltung (7/22/2016)


    I recently found one or two issues at work with scalar functions. I would be happy to contribute an article with examples.

    That would be great. IF you can do the setup, the bad performance, and how you fixed things, showing better performance, excellent. If this is 3 pages for each, then make it two articles.

  • Steve Jones - SSC Editor - Friday, May 20, 2016 10:18 AM

    Thanks, Jeff. Hoping for a few pieces. We have the one you wrote on moving a scalar UDF to a CROSS APPLY, but I'd like to get more references for specific functions that might do strange things.

    My sincere apologies.  I'm way overdue on this and other articles (it's been several years since I put one out there).  Hopefully, things will lighten up on my end and I'll be able to crank the one I was thinking of for this out soon.

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

  • Hi Jeff & Steve,

    I wrote an article in 2016 on "Work around to scalar functions". I've mentioned 3 workarounds with example.

    This was my first article! I hope you would find it relevant on this thread!

    • This reply was modified 3 years, 10 months ago by  Brahmanand Shukla. Reason: Resolved typos
  • Thanks, but these threads are not asking for help. They are asking for authors to write articles for this site.

  • Hi Steve.  Are you still looking for articles on this?

    I've got an issue from a few years ago when someone used a lot of scalar functions in completely the wrong way for a system "upgrade".  The system ground to a halt within a few days even though it was only new data being added.  I fixed it within a couple of days with a variety of different solutions, including doing some of the hard work in instead of insert triggers to manipulate the data on the way in rather than always having to transform it at query time.

    Anyway, if you are after something I can write that up.

    Thanks,

    Chris

  • Absolutely. The more specific examples, the more people learn to avoid the pattern

Viewing 12 posts - 1 through 11 (of 11 total)

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