Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Avoiding if conditional to reduce CPU usage RE: Avoiding if conditional to reduce CPU usage

  • June 24, 2015 at 8:54 am

    #1807783

    Minaz Amin (6/23/2015)

    Hi All,

    I have a function with multiple if ( condition) which is CPU intensive. How could I avoid this.

    CREATE FUNCTION prici.[fn_pricipalamt]

    (

    -- Add the parameters for the function here

    @Tcode char(10),

    @SecTypeCode1 char(10),

    @SecTypeCode2 char(10),

    @TradeAmount float,

    @Is_Security1 Char(1)

    )

    RETURNS float

    AS

    BEGIN

    -- Declare the return variable here

    DECLARE @Amount float

    SET @Amount = @TradeAmount

    -- Add the T-SQL statements to compute the return value here

    IF @Tcode in ('li','ti','tr','so') AND @SecTypeCode1 IN ( zz, 'ec', tt, 'ca', 'fc' ,Gb) AND @SecTypeCode2 IS NULL

    IF @Tcode in ('sa','dv','dr','ir','pd','rc','in') AND @SecTypeCode2 IN ( zz, 'ec', tt, 'ca', 'fc' ,Gb)

    IF @Tcode in ('ac','sl','ss') AND @SecTypeCode1 NOT IN ( zz, 'ec', tt, 'ca', 'fc' ,Gb) AND @SecTypeCode2 IN ( zz, 'ec', tt, 'ca', 'fc' ,Gb)

    IF (@Tcode = 'wd') AND (@SecTypeCode1 in ('ep',Mb)) AND (@SecTypeCode2 IN ( zz, 'ec', tt, 'ca', 'fc' ,Gb))

    --IF @Is_Security1='N'

    IF @Tcode in (zz,'ts','to','si') AND @SecTypeCode1 IN ( zz, 'ec', tt, 'ca', 'fc' ,Gb) AND @SecTypeCode2 IS NULL

    IF @Tcode in ('pa','ai','ps','rs') AND @SecTypeCode2 IN ( zz, 'ec', tt, 'ca', 'fc' ,Gb)

    IF @Tcode in ('as','by','cs') AND @SecTypeCode1 NOT IN ( zz, 'ec', tt, 'ca', 'fc' ,Gb) AND @SecTypeCode2 IN ( zz, 'ec', tt, 'ca', 'fc' ,Gb)

    IF (@Tcode = Kb) AND (@SecTypeCode1 in ('ep',Mb)) AND (@SecTypeCode2 IN ( zz, 'ec', tt, 'ca', 'fc' ,Gb))

    --IF @Is_Security1='N'

    IF (@Tcode = Kb) AND (@SecTypeCode1 NOT IN ( zz, 'ec', tt, 'ca', 'fc' ,Gb)) AND (@SecTypeCode1 NOT IN ('ep',Mb)) AND (@SecTypeCode2 IN ( zz, 'ec', tt, 'ca', 'fc' ,Gb))

    --IF @Is_Security1='N'

    IF @Tcode in ('li',zz,'ti','ts','to','tr','si','so') AND @SecTypeCode1 NOT IN ( zz, 'ec', tt, 'ca', 'fc' ,Gb) AND (@SecTypeCode2 IS NULL)

    --IF @Is_Security1='N'

    RETURN @Amount

    Maybe it's me or maybe I'm just missing something but this code would never work to begin with. Where is the implied THEN portion of any of those IFs? I don't see a one and I haven't tried it but SQl Server would return an error about such an omission.

    There are also some pretty glaring syntax errors. For example, the following extract from the code above would absolutely give an error because "zz", "tt", and "GB" are not quoted and there is no table reference for those items as columns.

    IF @Tcode in ('li',[font="Arial Black"]zz[/font],'ti','ts','to','tr','si','so')

    AND @SecTypeCode1 NOT IN ( [font="Arial Black"]zz[/font], 'ec', [font="Arial Black"]tt[/font], 'ca', 'fc' ,[font="Arial Black"]Gb[/font])

    AND (@SecTypeCode2 IS NULL)

    --IF @Is_Security1='N'

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