June 21, 2010 at 10:45 am
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
June 21, 2010 at 10:56 am
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.
June 21, 2010 at 10:58 am
Thanks for the reply
It's a scalar function
Returns an integer 1 or 0
jhh
June 21, 2010 at 11:03 am
Looks like it could be rewritten as an inline tvf and called using cross apply. How is the results of the function used again?
June 21, 2010 at 11:07 am
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
June 21, 2010 at 11:10 am
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.
June 21, 2010 at 11:13 am
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
June 21, 2010 at 11:17 am
Check out this blog post: Comparing Hardcoded functions, In-Line TVF's, and Scalar Functions.
June 21, 2010 at 12:31 pm
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
June 21, 2010 at 1:00 pm
Phunhog (6/21/2010)
Thank you SSC forums & LynnYou 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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply