SQLTeam.com | Weblogs | Forums

Optimize Code With Many Subqueries


#1

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

#2

Please provide:

  • table descriptions as create statements
  • sample data as insert statements
  • expected output from the sample data you provide