If exists in boolean user defined function

Question

If exists in boolean user defined function

Phunhog

SSCommitted

Points: 1562
More actions
June 21, 2010 at 10:45 am

#225879

Good day to all
I have a large table with nearly 1/2 million records.
I've created an integer primary key field on the table to serve as my clustered index.
My task is to update each row with the results of this boolean function that uses the exists predicate
I pass 4 parameters into my function to see if the current row matches other records within the past 3 years. It return a 1 if there are no matches and a 0 if there are any
======================================
BEGIN
DECLARE @ResultVar int
IF EXISTS
(
SELECT DISTINCT [Scheduling Visit Number]
FROM tblSurgAllAppts2006 AS s
WHERE s.[Patient Mrn] = @MRN
and datediff(yyyy,@Date,s.[Appointment Date])< 3
AND s.[Scheduling Visit Number] <> @SchedNo
AND s.[Appointment Provider Reporting Section]= @Section
)
-- Return the result of the function
BEGIN
Select @ResultVar = 0
END
ELSE
BEGIN
Select @ResultVar = 1
END
RETURN @ResultVar
===================================
Unfortunately, this TSQL within the function must execute for each row in my large table and is VERY costly. CPU utilization on my server jumps to 100% and locks up the server.
Does any one have a better suggestion for using the exists predicate in the function?
Many thanks in advance for your suggestions.
jhh

Viewing 10 posts - 1 through 9 (of 9 total)

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

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

Is this function a scalar function or a table-valued function? Can't tell from the code posted as it looks like a snippet, not the entire function.

Phunhog SSCommitted Points: 1562 More actions · Answer 2

Thanks for the reply

It's a scalar function

Returns an integer 1 or 0

jhh

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

Looks like it could be rewritten as an inline tvf and called using cross apply. How is the results of the function used again?

Phunhog SSCommitted Points: 1562 More actions · Answer 4

i simply update the sixth field in the table to 1 (no matching records in the past 3 years because exists predicate returns nothing) or 1 (yes there are matching records in the past 3 years and they got returned by the exists predicate)

thanks!!

jhh

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

Totally untested (passed the parse) as you didn't provide any DDL for the table(s) involved, sample data, or expected results.

create function dbo.fn_myFunction(@MRN sometype1, @Date datetime, @SchedNo sometype2, @Section sometype3)

returns table

as

return(

select case when

EXISTS

(

SELECT DISTINCT [Scheduling Visit Number]

FROM tblSurgAllAppts2006 AS s

WHERE s.[Patient Mrn] = @MRN

and datediff(yyyy,@Date,s.[Appointment Date])< 3

AND s.[Scheduling Visit Number] <> @SchedNo

AND s.[Appointment Provider Reporting Section]= @Section

) then 0 else 0 end as RecExists)

Other changes are possible depending on what values are passed to the function.

Phunhog SSCommitted Points: 1562 More actions · Answer 6

Thanks Lynn

I'll give it a try

Tell me

Why do you return a table?

I just want to evaluate (Y/N) does the record have any matches within the past 3 years?

jhh

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

Check out this blog post: Comparing Hardcoded functions, In-Line TVF's, and Scalar Functions.

Phunhog SSCommitted Points: 1562 More actions · Answer 8

Thank you SSC forums & Lynn

You are indeed a SQL Jeddi Master Lynn

The TVF function with cross apply works like lightning

I have never seen cross apply used before

I've learned something new (again)!

Keep it up

JH Higgins

Informatition

Dartmouth-Hitchcock Medical Center

WayneS SSC Guru Points: 95461 More actions · Answer 9

Phunhog (6/21/2010)
Thank you SSC forums & Lynn
You are indeed a SQL Jeddi Master Lynn

Yes, that he is!

The TVF function with cross apply works like lightning
I have never seen cross apply used before
I've learned something new (again)!
Keep it up
JH Higgins
Informatition
Dartmouth-Hitchcock Medical Center

It actually has more to do with that scalar function - it's a hidden form of row-by-row processing.

Another good article is on sqlpass.org: "Set-Based Programming for the Loop-Based Programmer" by Sam Bendayan. Note that you need to be a PASS member (free) to view it, but this article (IMO) should be required reading.

Edit: corrected url

Edit2: You also might want to check out the "Using APPLY" link in my signature. Be sure to read part 2 also (linked to in the article).

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2