September 23, 2008 at 10:57 am
I have the following function but it is taking 23 seconds to process 50 records.
We have over 300,000 records which means it would take 38 hours to run our query that calls this function to set a value for each record in teh table.
Help....
CREATE FUNCTION dbo.GetDiagPtr(@billingprocedureid as Int)
RETURNS varchar(8)
AS
BEGIN
DECLARE @DiagPtr as varchar(8)
DECLARE @Diag1 as Char(1)
DECLARE @Diag2 as Char(1)
DECLARE @Diag3 as Char(1)
DECLARE @Diag4 as Char(1)
SET @Diag1 = (SELECT diagnosis1 FROM billingprocedures WHERE billingprocedureid = @billingprocedureid)
SET @Diag2 = (SELECT diagnosis2 FROM billingprocedures WHERE billingprocedureid = @billingprocedureid)
SET @Diag3 = (SELECT diagnosis3 FROM billingprocedures WHERE billingprocedureid = @billingprocedureid)
SET @Diag4 = (SELECT diagnosis4 FROM billingprocedures WHERE billingprocedureid = @billingprocedureid)
SET @DiagPtr = @Diag1
IF @Diag2 > 0 SET @DiagPtr = @DiagPtr + ', ' + @Diag2
IF @Diag3 > 0 SET @DiagPtr = @DiagPtr + ', ' + @Diag3
IF @Diag4 > 0 SET @DiagPtr = @DiagPtr + ', ' + @Diag4
RETURN @DiagPtr
END
September 23, 2008 at 11:08 am
Any reason why you're doing this in a function and not just as a straight select statement???
You can get the same results with something like
SELECT diagnosis1 +
CASE WHEN diagnosis2 > 0 THEN ', ' + diagnosis2 END +
CASE WHEN diagnosis3 > 0 THEN ', ' + diagnosis3 END +
CASE WHEN diagnosis4 > 0 THEN ', ' + diagnosis4 END
FROM billingprocedures
WHERE billingprocedureid = @billingprocedureid
If it's to do an update Why not something like
Update MyTbl
Set DiagPtr = diagnosis1 +
CASE WHEN diagnosis2 > 0 THEN ', ' + diagnosis2 END +
CASE WHEN diagnosis3 > 0 THEN ', ' + diagnosis3 END +
CASE WHEN diagnosis4 > 0 THEN ', ' + diagnosis4 END
FROM billingprocedures
WHERE billingprocedureid = @billingprocedureid
September 23, 2008 at 11:09 am
Of course that brings to mind the whole concept of repeating groups and such but if this is going to a report or a front end piece or some sort of Data Warehouse then that's fine too... Just something to think about.
-Luke.
September 23, 2008 at 11:11 am
Thanks ,Luke, I was unsure how to do that and we will just do it in the select like
you showed.
September 23, 2008 at 11:14 am
This seems like some sort of health care bit, so perhaps you might not have the ability to do this, but just out of curiosity, how's come you have 4 columns for diagnosis? why not pull the diagnosis out to a separate table linked back tot he patient via a patient ID or something...
If the table was properly normalized you'd find you would not be having some much difficulties trying to get this data put together the way you want it.
-Luke.
September 23, 2008 at 11:26 am
We actually do, this is a script to convert a competitor's data.
This is how I actually ended up formatting it:
RTRIM(CAST(diagnosis1 AS CHAR(1))
+ CASE WHEN (diagnosis2 > 0) THEN ', ' + CAST(diagnosis2 AS CHAR(1)) ELSE '' END
+ CASE WHEN (diagnosis3 > 0) THEN ', ' + CAST(diagnosis3 AS CHAR(1)) ELSE '' END
+ CASE WHEN (diagnosis4 > 0) THEN ', ' + CAST(diagnosis4 AS CHAR(1)) ELSE '' END) AS DiagPtr
If I didn't add the ELSE the values would come up NULL if there were < 4 diagnosis codes.
September 23, 2008 at 11:31 am
yup that's true the NULLS would be in there. See what happens when I get a bit ahead of myself... also, good to hear you actually have the data normalized and you're just trying to correct some one else's mistake.
Thanks for the follow up.
-Luke.
September 24, 2008 at 7:34 am
To be honest, the data is for a medical practice management solution that has both a Windows and web client that use the same data.
It is horribly architected and, even though ours isn't too much better, it is better than theirs.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply