Using a Function in a Where clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Using a Function in a Where clause

Author

Topic

mgee
Starting Member

2 Posts

Posted - 2012-06-29 : 14:20:40

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'

--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' )) --FELONY&MISD CASE TYPES
AND P.PARTY_ROLE_TYP_ID = 1000071 --DEFENDANT
AND FC.VOID <> 'Y'
AND P.VOID <> 'Y'

Mike Gee
Lead Programmer/Analyst
Arizona Supreme Court
Phoenix, Az
mgee@courts.az.gov

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-06-29 : 14:30:13

What happens when you run the query without using the function but using the value returned by the function? (value for AOC_RPT.UDFSTATGETCASESTATUS(CS.CASE_ID,'05/01/2006') )
if result comes back real fast then you know the issue is with your function otherwise do you have indexes on all of these columns you are filtering on
CASE_FILING_DT
CASE_STATUS_DATE
CATEGORY_DESC
PARTY_ROLE_TYP_ID
VOID <> 'Y'
VOID <> 'Y'

are your 'date' columns really date columns or are they varchar fields, if so why not use real datetime columns
are your 'boolean' columns really booleancolumns or are they varchar fields, if so why not use bit columns

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-06-29 : 18:24:21

Could it be that the function is being invoked against the entire dataset and not against the filtered data? Perhaps a subquery that perfroms the filtering first would help:

select subqry.*
from (
select <whatever>
from (tables joined together>
WHERE FC.CASE_FILING_DT < '05/01/2006'
AND CS.CASE_STATUS_DATE < '05/01/2006'
-- -- -- <Not yet> AND (AOC_RPT.UDFSTATGETCASESTATUS(CS.CASE_ID,'05/01/2006') IN (1000001,1000002,1000006))

AND ( CC.CATEGORY_DESC IN ('CRIMINAL','OUT OF COUNTY CRIMINAL' )) --FELONY&MISD CASE TYPES
AND P.PARTY_ROLE_TYP_ID = 1000071 --DEFENDANT
AND FC.VOID <> 'Y'
AND P.VOID <> 'Y'
) subqry
WHERE
AOC_RPT.UDFSTATGETCASESTATUS(subqry.CASE_ID,'05/01/2006') IN (1000001,1000002,1000006))

Just a thought...

=================================================
I don't know what your destiny will be, but one thing I do know: the only ones among you who will be really happy are those who have sought and found how to serve. -Albert Schweitzer

mgee
Starting Member

2 Posts

Posted - 2012-06-30 : 20:54:08

I think the subquery idea is good.
I will try this and report back.
Thanks for the idea.

Mike Gee
Lead Programmer/Analyst
Arizona Supreme Court
Phoenix, Az
mgee@courts.az.gov

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources