Avoiding if conditional to reduce CPU usage

Question

Avoiding if conditional to reduce CPU usage

Minaz Amin

SSChampion

Points: 11052
More actions
June 23, 2015 at 9:29 pm

#301966

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
"More Green More Oxygen !! Plant a tree today"

Viewing 3 posts - 1 through 2 (of 2 total)

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

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

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

Quick suggestion, turn it into an inline Table Value Function, here is a simple suggestion on how the logic can be coded

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @Tcode char(10) = 'ac';

DECLARE @SecTypeCode1 char(10) = 'zz';

DECLARE @SecTypeCode2 char(10) = 'zz';

DECLARE @TradeAmount float = 1000;

DECLARE @Is_Security1 Char(1) = 'X';

DECLARE @Amount float

;WITH BASE_TCODE(TC,CNO,EQ) AS

(

SELECT TC,CNO,EQ FROM

(VALUES ('li',1,1)

,('ti',1,1)

,('tr',1,1)

,('so',1,1)

,('sa',2,1)

,('dv',2,1)

,('dr',2,1)

,('ir',2,1)

,('pd',2,1)

,('rc',2,1)

,('in',2,1)

,('ac',3,1)

,('sl',3,1)

,('ss',3,1)

,('wd',4,1)

) AS X(TC,CNO,EQ)

)

,BASE_SECC_A(SCA,CNO,EQ) AS

(

SELECT SCA,CNO,EQ FROM

(VALUES ('zz',1,1)

,('ec',1,1)

,('ca',1,1)

,('fc',1,1)

,('Gb',1,1)

,('zz',3,0)

,('ec',3,0)

,('ca',3,0)

,('fc',3,0)

,('Gb',3,0)

,('ep',4,1)

,('Mb',4,1)

) AS X(SCA,CNO,EQ)

)

,BASE_SECC_B(SCB,CNO,EQ) AS

(

SELECT SCB,CNO,EQ FROM

(VALUES ('NULL',1,1)

,('zz',2,1)

,('ec',2,1)

,('ca',2,1)

,('fc',2,1)

,('Gb',2,1)

,('zz',3,1)

,('ec',3,1)

,('ca',3,1)

,('fc',3,1)

,('Gb',3,1)

,('zz',4,1)

,('ec',4,1)

,('ca',4,1)

,('fc',4,1)

,('Gb',4,1)

) AS X(SCB,CNO,EQ)

)

SELECT

BT.TC

,BT.CNO

,BT.EQ

,SA.EQ

,SB.EQ

FROM BASE_TCODE BT

LEFT OUTER JOIN BASE_SECC_A SA

ON BT.CNO = SA.CNO

LEFT OUTER JOIN BASE_SECC_B SB

ON BT.CNO = SB.CNO

WHERE BT.TC = @Tcode

AND SA.SCA = ISNULL(@SecTypeCode1,'NULL')

AND SB.SCB = ISNULL(@SecTypeCode2,'NULL')

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

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)