Below is some code from an old system that is used to calculate balances - this code is running on SQL2008 R2.
Right now the below code returns data in about 4-6 seconds - this is significantly improved over where the code was at (was using a cursor) that was taking several minutes to run.
If possible I'd like to get this query to run even faster as as these table grow the query will continue to perform slower. I am looking for syntax changes that might help. I've ran the query thru tuning advisor in our test environment and tried to apply relevant recommendations but 4-6 seconds is about as fast as it gets currently. I have relative free reign over the table structure in this database.
Any ideas would be appreciated.
SELECT ClientKey, FULLNAME, PayorKey, DeptKey, DeptName, PayorName, Balance FROM (
SELECT ClientKey, FULLNAME, PayorKey, DeptKey, DeptName, PayorName, SUM(Balance) AS Balance
FROM (
SELECT
ClientKey, FULLNAME,
CASE WHEN Balances = 'SelfPayBalance' THEN 19847 ELSE PayorKey END AS PayorKey,
CASE WHEN PayorKey = 19847 OR Balances = 'SelfPayBalance' THEN 999 ELSE DeptKey END AS DeptKey,
CASE WHEN PayorKey = 19847 OR Balances = 'SelfPayBalance' THEN 'All' ELSE DeptName END AS DeptName,
CASE WHEN Balances = 'SelfPayBalance' THEN 'Self Pay' ELSE PayorName END AS PayorName, SUM(Balance) AS Balance, Balances
FROM (
SELECT T4.ClientKey, FULLNAME, SUM(InsBalance) AS InsBalance, SUM(SelfPayBalance) AS SelfPayBalance, BP.PayorKey, T4.DeptKey, BD.DeptName, BP.PayorName FROM
( SELECT T3.ClientKey, T3.ChargeKey, T3.DeptKey,
T3.InsPolicyKey AS InsPolicyKey,
CASE WHEN ISNULL(BP.PayorTypeLU,2) = 7 THEN
T3.InitialCharge + ClientAdjustments + InsAdjustments + ClientPayments + InsPayments
ELSE
T3.CopayAmt + ClientAdjustments + ClientPayments
END AS SelfPayBalance,
CASE WHEN ISNULL(BP.PayorTypeLU,2) <> 7 THEN
T3.InitialCharge + InsPayments + InsAdjustments - T3.CopayAmt
ELSE 0
END AS InsBalance
FROM
(
SELECT ClientKey,Test.ChargeKey,Test.inspolicykey,Test.DeptKey, SUM(Test.InitialCharge) AS InitialCharge,
SUM(Test.ClientAdjustments) AS ClientAdjustments,
SUM(Test.InsAdjustments) AS InsAdjustments,
SUM(Test.ClientPayments) AS ClientPayments,
SUM(Test.InsPayments) AS InsPayments,
SUM(Test.CopayAmt) AS CopayAmt
FROM (
SELECT
Clientkey,
ChargeKey,
InsPolicyKey,
DeptKey,
CASE WHEN T2.TranstypeLU IN (2,6) AND (ISNULL(IsCopay,'F') = 'T' OR ISNULL(ChargePayorType,2) = 7) THEN ISNULL(T2.Delta,0) ELSE 0 END AS [ClientAdjustments],
CASE WHEN T2.TransTypeLU IN (2,6) AND ((ISNULL(IsCopay,'F') = 'F') AND ISNULL(ChargePayorType,2) <> 7) THEN ISNULL(T2.Delta,0) ELSE 0 END AS InsAdjustments,
CASE WHEN T2.TranstypeLU IN (4) AND (ISNULL(IsCopay,'F') = 'T' OR ISNULL(ChargePayorType,2) = 7) THEN ISNULL(T2.Delta,0) ELSE 0 END AS [ClientPayments],
CASE WHEN T2.TransTypeLU IN (4) AND ((ISNULL(IsCopay,'F') = 'F') AND ISNULL(ChargePayorType,2) <> 7) THEN ISNULL(T2.Delta,0) ELSE 0 END AS InsPayments,
CASE WHEN T2.TransTypeLU = 3 THEN ISNULL(T2.Delta,0) ELSE 0 END AS InitialCharge,
CASE WHEN T2.TransTypeLU = 5 AND ISNULL(IsCopay,'F') = 'T' THEN ISNULL(T2.DisplayAmount,0) ELSE 0 END AS CopayAmt,
TransTypeLU,
ChargePayorType,
ChargePolicyKey,
ChargePayorKey,
DisplayAmount
FROM (
SELECT ChargeRows.ClientKey, ChargeRows.ChargeKey, ChargeRows.InsPolicyKey, ChargeRows.Deptkey, ChargeRows.Delta,ChargeRows.TransTypeLU,
ChargeRows.IsCopay,ChargeRows.Descr,ChargeRows.DisplayAmount,
BPCharge.PayorTypeLU AS ChargePayorType,IPCharge.InsPolicyKey AS ChargePolicyKey,
BPCharge.PayorKey AS ChargePayorKey
FROM (
SELECT CS.ClientKey, CS.ChargeKey, CTL.InsPolicyKey, CS.DeptKey, Delta, TransTypeLU, IsCopay, Descr, DisplayAmount
FROM dbo.BIL_ChargeTransLog AS CTL
LEFT JOIN dbo.BIL_Chargeslips AS CS ON CTL.chargekey = CS.ChargeKey
) AS ChargeRows
LEFT JOIN dbo.BIL_InsPolicies AS IPCharge ON ChargeRows.inspolicykey = IPCharge.InsPolicyKey
LEFT JOIN dbo.BIL_Payors AS BPCharge ON IPCharge.PayorKey = BPCharge.PayorKey
) AS T2
) AS Test
GROUP BY Test.ClientKey, TEST.ChargeKey, Test.InsPolicyKey, Test.DeptKey
) AS T3
LEFT JOIN dbo.BIL_ARtransLog AS ATL2 ON T3.ChargeKey = ATL2.chargekey AND ATL2.OP__DOCID
= (SELECT MAX(OP__DOCID) FROM dbo.BIL_ARtransLog AS ATL3 WHERE ATL2.chargekey = ATL3.ChargeKey AND TransTypeLU NOT IN (18,19))
LEFT JOIN dbo.BIL_InsPolicies AS IP ON ATL2.inspolicykey = IP.InsPolicyKey
LEFT JOIN dbo.BIL_Payors AS BP ON IP.PayorKey = BP.PayorKey
) AS T4
INNER JOIN dbo.BLU_Departments AS BD ON T4.Deptkey = BD.DeptKey
LEFT JOIN dbo.BIL_InsPolicies AS IP ON IP.InsPolicyKey = T4.inspolicykey
INNER JOIN dbo.BIL_PAYORS AS BP ON BP.PayorKey = IP.PayorKey
LEFT JOIN dbo.FD__CLIENTS AS FC ON T4.ClientKey = FC.ClientKey
GROUP BY T4.ClientKey,FC.FULLNAME, T4.DeptKey, PayorName, BP.PayorKey, BD.DeptName
) AS T5
UNPIVOT
( Balance FOR Balances IN (InsBalance,SelfPayBalance)
) AS p
GROUP BY ClientKey, FULLNAME, PayorKey, DeptName, Balances, PayorName, DeptKey
) AS T7
GROUP BY ClientKey, FULLNAME, PayorKey, DeptName, PayorName, DeptKey
) AS FinalBalanceCalc WHERE Balance <> 0