An application which we have built has undergone a large change in its database schema, particularly in the way financial data is stored. We have functions that calculate the total amount of billing, based on various scenarios; and the change is causing huge performance problems when the functions must be run many times in a row.
I'll include an explanation, the function and the relevant schema, and I hope someone sees a much better way to write the function. This is SQL Server 2008.
First, the business basis: think of a medical Procedure. The healthcare Provider performing the Procedure sends one or more Bills, each of which may have one or more line items (BillItems).
That Procedure is the re-billed to another party. The amount billed to the third party may be:
- The total of the Provider's billing
- The total of the Provider's billing plus a Copay amount,or
- A completely separate amount (a Rebill amount)
The current function for calculating the billing for a Procedure looks at all three scenarios:
CREATE FUNCTION [dbo].[fnProcTotalBilled] (@PROCEDUREID INT)
RETURNS MONEY AS
BEGIN
DECLARE @billed MONEY
SELECT @billed = (SELECT COALESCE((SELECT COALESCE(sum(bi.Amount),0)
FROM BillItems bi INNER JOIN Bills b ON b.BillID=bi.BillID
INNER JOIN Procedures p on p.ProcedureID=b.ProcedureID
WHERE b.ProcedureID=@PROCEDUREID
AND p.StatusID=3
AND b.HasCopay=0
AND b.Rebill=0),0))
-- the total of the provider's billing, with no copay and not rebilled
+
(SELECT COALESCE((SELECT sum(bi.Amount) + COALESCE(b.CopayAmt,0)
FROM BillItems bi INNER JOIN Bills b ON b.BillID=bi.BillID
INNER JOIN Procedures p on p.ProcedureID=b.ProcedureID
WHERE b.ProcedureID=@PROCEDUREID
AND p.StatusID=3
AND b.HasCopay=1
GROUP BY b.billid,b.CopayAmt),0))
-- the total of the provider's billing, plus a Copay amount
+
(SELECT COALESCE((SELECT sum(COALESCE(b.RebillAmt,0))
FROM Bills b
INNER JOIN Procedures p on p.ProcedureID=b.ProcedureID
WHERE b.ProcedureID=@PROCEDUREID
AND p.StatusID=3
AND b.Rebill=1),0))
-- the Rebill amount, instead of the provider's billing
RETURN @billed
END
I'll omit the DDL for the Procedure. Suffice to say, it must have a certain status (shown in the function as p.StatusID= 3).
Here are the DDLs for Bills and related BillItems:
CREATE TABLE dbo.Bills (
BillID int IDENTITY(1,1) NOT NULL,
InvoiceID int DEFAULT ((0)),
CaseID int NOT NULL,
ProcedureID int NOT NULL,
TherapyGroupID int DEFAULT ((0)) NOT NULL,
ProviderID int NOT NULL,
Description varchar(1000),
ServiceDescription varchar(255),
BillReferenceNumber varchar(100),
TreatmentDate datetime,
DateBilled datetime,
DateBillReceived datetime,
DateBillApproved datetime,
HasCopay bit DEFAULT ((0)) NOT NULL,
CopayAmt money,
Rebill bit DEFAULT ((0)) NOT NULL,
RebillAmt money,
IncludeInDemand bit DEFAULT ((1)) NOT NULL,
CreateDate datetime DEFAULT (getdate()) NOT NULL,
CreatedByID int,
ChangeDate datetime,
ChangeUserID int,
PRIMARY KEY (BillID)
);
CREATE TABLE dbo.BillItems (
BillItemID int IDENTITY(1,1) NOT NULL,
BillID int NOT NULL,
ItemDescription varchar(1000),
Amount money,
WillNotBePaid bit DEFAULT ((0)) NOT NULL,
CreateDate datetime DEFAULT (getdate()),
CreatedByID int,
ChangeDate datetime,
ChangeUserID varchar(25),
PRIMARY KEY (BillItemID)
);
I fully realize how complex the function is; but I couldn't find another way to account for all the scenarios.
I'm hoping that a far better SQL programmer or DBA will see a more performant solution.
Any help will be greatly appreciated.
Thanks,
Tom
UPDATE:
Thanks to everyone for their replies. I tried to add a little clarification in comments, but I'll do so here, too.
First, a definition: a Procedure is medical service from a Provider on a single Date of Service. We only concern ourselves with the total amount billed for a procedure; multiple persons do not receive bills.
A "Case" can have many Procedures.
Generally, a single Procedure will have a single Bill - but not always. A Bill may have one or more BillItems. The Copay (if one exists) is added to the sum of the BillItems. A Rebill Amount trumps everything.
The performance issue comes into play at a higher level, when calculating the totals for an entire Case (many Procedures) and when needing to display grid data that shows hundreds of Cases at once.
My query was at the Procedure level, because it was simpler to describe the problem.
As to sample data, the data in @Serpiton's SQL Fiddle is an excellent, concise example. Thank you very much for it.
In reviewing the answers, it seems to me that both the CTE approach of @Serpiton and @GarethD's view approach both are strong improvements on my original. For the moment, I'm going to work with the CTE approach, simply to avoid the necessity of dealing with the multiple results from the SELECT.
I have modified @Serpiton's CTE to work at the Case level. If he or others would please take a look at it, I'd appreciate it. It's working well in my testing, but I'd appreciate other eyes on it.
It goes like this:
WITH Normal As (
SELECT b.BillID
, b.CaseID
, sum(coalesce(n.Amount * (1 - b.Rebill), 0)) Amount
FROM Procedures p
INNER JOIN Bills b ON p.ProcedureID = b.ProcedureID
LEFT JOIN BillItems n ON b.BillID = n.BillID
WHERE b.CaseID = 3444
AND p.StatusID = 3
GROUP BY b.CaseID,b.BillID, b.HasCopay
)
SELECT Amount = Sum(b.Amount)
+ Sum(Coalesce(c.CopayAmt, 0))
+ Sum(Coalesce(r.RebillAmt, 0))
FROM Normal b
LEFT JOIN Bills c ON b.BillID = c.BillID And c.HasCopay = 1
LEFT JOIN Bills r ON b.BillID = r.BillID And r.Rebill = 1
GROUP BY b.caseid
BillItemsbefore joining (as in my answer) simplify the logic, remove the need to group by additional fields, and remove the requirement for two additional joins on toBills?CASEwas missing anEND