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.

    SET ANSI_NULLS ON;
    SET QUOTED_IDENTIFIER ON;
    GO
    CREATE FUNCTION [dbo].[F_ISWOSTATUSWAITPOSSIBLE] (
    @WO_Status char(2)
    )
    RETURNS bit
    AS
    BEGIN
    RETURN (
    CASE WHEN @WO_Status in ('30', '40', '50', '60', '65', '70') THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END
    )
    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

    Johan

    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,
    ItemID,
    MIN(CAST(MyLevelHere AS VARBINARY(1)) + CAST(MyPriceHere AS VARBINARY(MAX)) AS Yak
    FROM dbo.MyTableNameHere
    GROUP BY CustomerID,
    ItemID
    )
    SELECT CustomerID,
    ItemID,
    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