Using a Function in a Where clause

Question

Using a Function in a Where clause

mgee1038

SSC Veteran

Points: 268
More actions
June 29, 2012 at 12:38 pm

#392630

I need to use a value returned from a function in a where clause.
I get the code to compile ok but it runs forever.
Will you please look at this and see if you can help.
Thanks
WHERE FC.CASE_FILING_DT < '05/01/2006'
AND CS.CASE_STATUS_DATE < '05/01/2006'
--CALL FUNCTION TO RETURN THE LAST STATUS EARLIER THAN THE START DATE
--MAKE SURE IT IS OPEN, REOPENED OR CONSOLIDATED TO QUALIFY AS PENDING
--the following is the problem code. I have tested the function and it returns an integer
-- as expected in less than one second.
AND (AOC_RPT.UDFSTATGETCASESTATUS(CS.CASE_ID,'05/01/2006') IN (1000001,1000002,1000006))
AND ( CC.CATEGORY_DESC IN ('CRIMINAL','OUT OF COUNTY CRIMINAL' ))
AND P.PARTY_ROLE_TYP_ID = 1000071 --DEFENDANT
AND FC.VOID <> 'Y'
AND P.VOID <> 'Y'
--ANY HELP GREATLY APPRECIATED

Viewing 14 posts - 1 through 13 (of 13 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Scalar UDF's introduce RBAR code. This function has to be called for each record. Post the code for the query and the function. It may be possibile to rewrite the function in a way the will improve performance by making it an inline table valued function used in the FROM clause of query using cross apply.

venoym SSCarpal Tunnel Points: 4161 More actions · Answer 2

mgee1038 (6/29/2012)
I need to use a value returned from a function in a where clause.
I get the code to compile ok but it runs forever.
Will you please look at this and see if you can help.
Thanks
WHERE FC.CASE_FILING_DT < '05/01/2006'
AND CS.CASE_STATUS_DATE < '05/01/2006'
--CALL FUNCTION TO RETURN THE LAST STATUS EARLIER THAN THE START DATE
--MAKE SURE IT IS OPEN, REOPENED OR CONSOLIDATED TO QUALIFY AS PENDING
--the following is the problem code. I have tested the function and it returns an integer
-- as expected in less than one second.
AND (AOC_RPT.UDFSTATGETCASESTATUS(CS.CASE_ID,'05/01/2006') IN (1000001,1000002,1000006))
AND ( CC.CATEGORY_DESC IN ('CRIMINAL','OUT OF COUNTY CRIMINAL' ))
AND P.PARTY_ROLE_TYP_ID = 1000071 --DEFENDANT
AND FC.VOID <> 'Y'
AND P.VOID <> 'Y'
--ANY HELP GREATLY APPRECIATED

The answer is that you can't get both performance and function returns in your where clause. It just won't happen.

That being said, what does the Case Status UDF do? I can tell it uses the Case_ID and a date to check for a numeric return value. Is it possible to change this from a UDF to an inner query that does the same thing? If so, your performance will be hugely improved using an inner/sub query instead of the UDF.

Gullimeel Hall of Fame Points: 3871 More actions · Answer 3

Apart from the function you have so many conditions ,are there proper indexes on some(or all) of these columns?

Try to comment the function call (AND (AOC_RPT.UDFSTATGETCASESTATUS(CS.CASE_ID,'05/01/2006') IN (1000001,1000002,1000006))

) condition and run the query..If it comes fast then issue might be with function otherwise issue could be something else like not proper indexes or not proper stats etc. So best way is to post the plan if possible, otherwise post the ddl for the query and for the tables in that query.

GulliMeel

Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]

subbu1 Hall of Fame Points: 3281 More actions · Answer 4

any way

user defined function.

calling.

select * emp where empcode in(select * split('1a','1b','1c'))

Jeff Moden SSC Guru Points: 1004292 More actions · Answer 5

subbareddy542 (6/30/2012)
any way
user defined function.
calling.
select * emp where empcode in(select * split('1a','1b','1c'))

Does your "split" function have either a While Loop, Recursive CTE, or some other form of recursion in it?

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

mgee1038 SSC Veteran Points: 268 More actions · Answer 6

I don't even know who put the split function in here.

I need to be able to get the last date earlier than a parameter entered date.

I write a function to get that.

I need to be able to put that into a where clause to select the correct records.

rhythmk SSCertifiable Points: 7271 More actions · Answer 7

mgee1038 (6/30/2012)
I don't even know who put the split function in here.
I need to be able to get the last date earlier than a parameter entered date.
I write a function to get that.
I need to be able to put that into a where clause to select the correct records.

This won't help.Please post your complete requirement with sample data.

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

Jeff Moden SSC Guru Points: 1004292 More actions · Answer 8

mgee1038 (6/30/2012)
I don't even know who put the split function in here.

Not to worry. My comment was directed to the person who posted it namely "subbareddy".

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

Jeff Moden SSC Guru Points: 1004292 More actions · Answer 9

mgee1038 (6/30/2012)
I need to be able to get the last date earlier than a parameter entered date.
I write a function to get that.
I need to be able to put that into a where clause to select the correct records.

I believe I understand what you need. As the others will undoubtably tell you, though, we need to see a bit more before we can help.

You're using a function in the where clause that we know nothing about. You're trying to have us help in a database and on tables that we know nothing about. Please take a look at the second link in my signature line below so we can help you figure all of this out. Be sure to post the code for the function, as well.

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

subbu1 Hall of Fame Points: 3281 More actions · Answer 10

i was told split is function.

if you will pass any parameter life empid like '1a,1b,1c'

that time

how to pass that string in where class.

select * from emp

where empid in('1a,1b,1c'

).

spit function code

Text

CREATE FUNCTION SPLIT(@VAL VARCHAR(MAX))

RETURNS @T1 TABLE(COL1 VARCHAR(MAX))

AS

BEGIN

WHILE CHARINDEX(',',@VAL)>0

BEGIN

INSERT INTO @T1 VALUES(SUBSTRING(@VAL,1,(CHARINDEX(',',@VAL))-1))

SET @val=SUBSTRING(@VAL,(CHARINDEX(',',@VAL))+1,LEN(@VAL))

END

INSERT INTO @T1 VALUES(@VAL)

RETURN

END

Jeff Moden SSC Guru Points: 1004292 More actions · Answer 11

subbareddy542 (7/1/2012)
i was told split is function.
if you will pass any parameter life empid like '1a,1b,1c'
that time
how to pass that string in where class.
select * from emp
where empid in('1a,1b,1c'
).
spit function code
Text
CREATE FUNCTION SPLIT(@VAL VARCHAR(MAX))
RETURNS @T1 TABLE(COL1 VARCHAR(MAX))
AS
BEGIN
WHILE CHARINDEX(',',@VAL)>0
BEGIN
INSERT INTO @T1 VALUES(SUBSTRING(@VAL,1,(CHARINDEX(',',@VAL))-1))
SET @val=SUBSTRING(@VAL,(CHARINDEX(',',@VAL))+1,LEN(@VAL))
END
INSERT INTO @T1 VALUES(@VAL)
RETURN
END

That's what's known as a "nibbler" splitter. Please see the folowing article for why you might want to use something else, instead.

http://www.sqlservercentral.com/articles/Tally+Table/72993/

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

Alok Kumar Sharma SSC Veteran Points: 297 More actions · Answer 12

any way

user defined function.

calling.

select string from FN_Split('a,aaa,aff,hh,',',') where string='aaa'

Thanks

Alok Kumar sharma

Jeff Moden SSC Guru Points: 1004292 More actions · Answer 13

alokmca1984 (7/7/2012)
any way
user defined function.
calling.
select string from FN_Split('a,aaa,aff,hh,',',') where string='aaa'
Thanks
Alok Kumar sharma

Heh... "any way", that might be a part of the reason why your code is so slow.

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