August 18, 2011 at 5:50 am
Hi guys.
Using SQL2000.......
I have a UDF that takes a single parameter (integer) returns a scalar value (integer).
The function has four blocks of code. Each one tries to find the value in a different way and only gets executed if the previous block fails to find anything.
One of the queries is taking about 20 seconds with no options specified. If I use OPTION (MAXDOP 1) the time drops to about 12 seconds. If I use OPTION (foce order) then it is instantaneous.
When I try to save the UDF I get error messages about incorrect syntax against the OPTION line.
why can't I use optimiser hints in the query
sample code is below of the first two SELECT blocks.
BEGIN
DECLARE @referralID AS INT
SET @referralID = 0
-- get the ID from the linked referral it is exists
SET @referralID = (SELECT referral_ID FROM contact WHERE contact_ID = @contactID)
--select @referralID
IF (@referralID = -1 or @referralID is null )
BEGIN
-- get the last referral to the team
SET @referralID = ( SELECT TOP 1
R.Referral_ID
FROM
contact C
join
patient_main PM1 on PM1.patient_ID = C.patient_ID AND PM1.z_pr__language_userdesc is null
left join
patient_main PM2 on PM2.nhs_number = PM1.nhs_number
and PM2.patient_ID <> PM1.patient_ID
and PM2.z_pr__language_userdesc is null
join
referral R on R.patient_ID in (PM1.Patient_ID,PM2.Patient_ID)
and R.staff_date < C.staff_date
and R.staff_team = c.staff_Team
WHERE
C.contact_ID = 16811563--@contactID
ORDER BY
R.staff_date DESC
-- Put OPTION (Force Order) in here --
)
END
...
END
return @referralID
END
August 18, 2011 at 8:16 am
update...
I have rewritten the UDF to be an in-line table function that always returns one column and one row.
In SQL2000 you cannot pass a row dependent value to a table function (i.e. this won't work)
select T1.ID, udf_tablefunction(T1.ID) from table1 T1
NOTE: you can do this in SQL2005.
so... I copied the UDF to the 2005 server and called all the tables across the servers. Each row takes 5 seconds to return - too slow.....
Finally I resorted to CURSORS and built a cursor for the records that need updating then call the UDF for each record. This is processing about 15 records per second but I currently have 60K records to update and this is only going to grow (Current time estimate is 70 minutes)
Any other bright ideas????
August 18, 2011 at 1:20 pm
I don't understand why you're even using a UDF for this update. It looks like you should just be able to join your tables directly to perform the updates.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 18, 2011 at 3:48 pm
I have now rewritten it directly in the query. The reason for a UDF is it is general functionality and a UDF is the easiest way to achive code reuse. Its kind of messy to have to coalesce four co-related sub queries each time I need to do the calculation.
I H8 SQL2000. I spent all day on this problem when I could have fixed it in under an hour on SQL2005.
August 19, 2011 at 6:44 am
Have you considered using a view? That's also a way to reuse code, but it's still set-based.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply