Post reply

Bad Scalar Functions (or other UDF)

  • May 18, 2016 at 8:45 pm

    #327159

    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.

  • May 18, 2016 at 10:01 pm

    #1880080

    Steve,

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

  • May 19, 2016 at 2:27 pm

    #1880386

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

  • May 19, 2016 at 9:12 pm

    #1880417

    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)

  • May 20, 2016 at 10:18 am

    #1880537

    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.

  • July 22, 2016 at 11:22 am

    #1891646

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

  • July 25, 2016 at 2:48 pm

    #1891927

    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.

  • December 4, 2017 at 10:15 am

    #1970563

    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)

  • April 11, 2020 at 1:01 pm

    #3742202

    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!

    SQL Server Carpenter - Consultant & Trainer
    Author of Learn T-SQL from Scratch

  • April 12, 2020 at 4:54 pm

    #3742300

    Thanks, but these threads are not asking for help. They are asking for authors to write articles for this site.

  • April 27, 2020 at 4:09 pm

    #3746715

    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

  • April 27, 2020 at 5:09 pm

    #3746743

    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