Looking for some guidance on avoiding Scalar UDFs for DB lookups

  • You should get rid of the local variable as it's not needed and causes overhead.  Also, make sure that ANSI_NULLS and QUOTED_IDENTIFIER are set properly when the function is created.

    @WO_Status char(2)
    RETURNS bit
    RETURN (
    CASE WHEN @WO_Status in ('30', '40', '50', '60', '65', '70') THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • Indeed, but best is to just avoid the SVF


    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Sorry... not enough coffee yet.  Misread some code and removed the post.

    And I totally agree with Johan... avoid Scalar UDFs at all costs.  They even prevent parallelism if you do a select from a table if you have one in a computed column even if you don't use the computed column.  Brent Ozar wrote about that and I've verified his findings.


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

  • Something like this?

            WITH cteAggregated(CustomerID, ItemID, Yak)
    AS (
    SELECT CustomerID,
    FROM dbo.MyTableNameHere
    GROUP BY CustomerID,
    SELECT CustomerID,
    CAST(SUBSTRING(Yak, 1, 1) AS VARCHAR(1)) AS MyLevelHere,
    CAST(SUBSTRING(Yak, 2, LEN(Yak) - 1) AS DECIMAL(19, 4)) AS MyPriceHere
    FROM cteAggregated;

    It's hard to say exactly without knowing your DDL.


    N 56°04'39.16"
    E 12°55'05.25"

  • Thanks all. I managed to get it to do what I needed by breaking down into 3 queries and can run all 3 within 20 seconds. It it was not perfect and required additional outliers to be added so was a WIP. Then the business decided to change the pricing on certain things which kind of broke it - at least requiring the addition of a bunch of exclusions. I will review the responses in more detail as my understanding develops (just starting out using CTEs) and revisit the code when I get a chance.



Viewing 5 posts - 16 through 20 (of 20 total)

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