June 13, 2017 at 4:45 am
How to improve performance of a query.
Please find attached actual and estimated execution plans of the query.
June 13, 2017 at 7:08 am
You're taking most of the data fromthese tables and that severely limits your options - which might not be a badthing. The main issue with your query is that you have two levels ofaggregation and SQL Server is performing them separately using the streamingaggregate operator. The data isn't sorted in a useful order for either of thosestreaming aggregates to work so there are sort operators in the plan to supportthem.
Firstly, rearrange the query so that youhave a single level of aggregation.
Secondly, sometimes it helps to shufflethe column list of the GROUP BY clause to match the list and order of columnsof the sort operator.
Thirdly, test with and without forcing ahash aggregate, see query below.
SELECT
FACILITY_KEY,ORG_KEY,PATIENT_NAME,PATIENT_ID_NUMBER,PATIENT_DOB,PATIENT_GENDER,DOS,DAYS,PAYOR,chg_post_date,MPID,INS_PLAN,SERVICE_LOCATION,PLACE_OF_SERVICE,
CPT_CATEGORY,INSURANCE_PROVIDER,
Service_Provider,FINANCIAL_LOCATION,CHARGE_AMOUNT,TOTAL_PAYMENTS,ADJUSTMENTS,CURRENT_BALANCE,
case when days BETWEEN 0 AND 30 then '0-30 Days'
when days between 31 and 60 then '31-60 Days'
when days between 61 and 90 then '61-90 Days'
when days between 91 and 120 then '91-120 Days'
when days between 121 and 150 then '121-150 Days'
when days > 150 then '150+ Days'end age,
ENCNT_VIS_KEY,
BILL_STATUS
FROM (
SELECT FACILITY_KEY,ORG_KEY,CHARGE_ID_NUMBER,PATIENT_NAME,PATIENT_ID_NUMBER,PATIENT_DOB,PATIENT_GENDER,
DOS,max(days) days,PAYOR,min(chg_post_date) chg_post_date, MPID,INS_PLAN,SERVICE_LOCATION,PLACE_OF_SERVICE,
Service_Provider,FINANCIAL_LOCATION,CPT_CATEGORY,INSURANCE_PROVIDER,
SUM(CHARGE_AMOUNT) CHARGE_AMOUNT,SUM(TOTAL_PAYMENTS) TOTAL_PAYMENTS,
SUM(ADJUSTMENTS) ADJUSTMENTS,SUM(CURRENT_BALANCE) CURRENT_BALANCE,ENCNT_VIS_KEY,BILL_STATUS
FROM ( SELECT FACILITY_KEY,ORG_KEY,ENCNT_VIS_KEY, TRANS_CHARGES_KEY,CHARGE_ID_NUMBER, DOS,PAYOR,CHARGE_AMOUNT,SUM(TOTAL_PAYMENTS) TOTAL_PAYMENTS,
SUM(ADJUSTMENTS) ADJUSTMENTS,(CHARGE_AMOUNT - SUM(TOTAL_PAYMENTS) - SUM(ADJUSTMENTS) ) CURRENT_BALANCE,
PATIENT_NAME,PATIENT_ID_NUMBER,PATIENT_DOB,PATIENT_GENDER,days,MPID,INS_PLAN, SERVICE_LOCATION,PLACE_OF_SERVICE,
Service_Provider, FINANCIAL_LOCATION,chg_post_date ,CPT_CATEGORY,INSURANCE_PROVIDER,BILL_STATUS
FROM ( SELECT
A.FACILITY_KEY FACILITY_KEY,
A.ORG_KEY ORG_KEY,
A.ENCNT_VIS_KEY,
b.TRANS_CHARGES_KEY,
B.CHARGE_ID_NUMBER,
B.DOS_FROM DOS,
B.CHARGE_POST_DATE chg_post_date,
b.BILL_STATUS,
A.INSURANCE_CATEGORY PAYOR,
A.INS_PLAN_CODE_NAME INS_PLAN,
B.CHARGE_AMOUNT,
case when F.TRANSACTION_TYPECODE in ('P','I') then F.TRANSACTION_AMOUNT*-1 else 0 end Total_Payments, -- ###
case when F.TRANSACTION_TYPECODE='A' then F.TRANSACTION_AMOUNT*-1 else 0 end Adjustments, -- ###
G.PATIENT_NAME,
G.PATIENT_ID_NUMBER,
G.PATIENT_DOB,
G.PATIENT_SSN,
G.PATIENT_GENDER,
MPID,
ABS(DATEDIFF(day,B.CHARGE_POST_DATE, GETDATE())) days ,
A.SERVICE_LOCATION,
A.PLACE_OF_SERVICE,
B.SERVICE_PROVIDER_CODE_NAME Service_Provider,
A.FINANCIAL_LOCATION,
A.INSURANCE_PROVIDER_CODE_NAME INSURANCE_PROVIDER,
N.CPT_GROUPING CPT_CATEGORY
FROM TBL_F_ENCOUNTER_VISITS A
INNER JOIN TBL_F_TRANS_CHARGES B ON A.ENCNT_VIS_KEY = B.ENCNT_VIS_KEY
left outer JOIN TBL_F_PAY_ADJ F ON B.TRANS_CHARGES_KEY = F.TRANS_CHARGES_KEY
INNER JOIN TBL_F_PATIENT_GURANTOR G ON A.PATIENT_GRANTOR_KEY = G.PATIENT_GRANTOR_KEY
INNER JOIN D_ORGANIZATION ON A.ORG_KEY = D_ORGANIZATION.ORG_KEY
INNER JOIN D_FACILITY ON A.FACILITY_KEY = D_FACILITY.FACILITY_KEY
LEFT OUTER JOIN (SELECT CODE, FACILITY_KEY, CPT_GROUPING FROM TBL_D_CPT WHERE FLAG = 1) N
ON B.CHARGE_CODE = N.CODE AND B.FACILITY_KEY = N.FACILITY_KEY
--WHERE D_ORGANIZATION.ORG_KEY = '1' AND 1=1 AND D_FACILITY.FACILITY_KEY = 29
) A
GROUP BY FACILITY_KEY,ORG_KEY,ENCNT_VIS_KEY, CHARGE_ID_NUMBER, DOS, PAYOR,
PATIENT_NAME, PATIENT_ID_NUMBER, PATIENT_DOB, PATIENT_GENDER,
MPID, INS_PLAN, SERVICE_LOCATION, PLACE_OF_SERVICE, SERVICE_PROVIDER, FINANCIAL_LOCATION,
CPT_CATEGORY, INSURANCE_PROVIDER, BILL_STATUS,
TRANS_CHARGES_KEY, CHARGE_AMOUNT, days, chg_post_date -- ###
) A
GROUP BY FACILITY_KEY,ORG_KEY,ENCNT_VIS_KEY, CHARGE_ID_NUMBER, DOS,PAYOR,
PATIENT_NAME, PATIENT_ID_NUMBER, PATIENT_DOB, PATIENT_GENDER,
MPID, INS_PLAN, SERVICE_LOCATION, PLACE_OF_SERVICE, Service_Provider, FINANCIAL_LOCATION,
CPT_CATEGORY, INSURANCE_PROVIDER, BILL_STATUS --ENCNT_VIS_KEY -- ###
) A WHERE CURRENT_BALANCE <> 0
OPTION (HASH GROUP)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 13, 2017 at 7:16 am
krishna83 - Tuesday, June 13, 2017 4:45 AMHow to improve performance of a query.Please find attached actual and estimated execution plans of the query.
Quick option:
Reduce the number of rows returned. You have no filters on your query. This is going to read most of the tables' rows.
June 14, 2017 at 1:07 am
Luis Cazares - Tuesday, June 13, 2017 7:16 AMkrishna83 - Tuesday, June 13, 2017 4:45 AMHow to improve performance of a query.Please find attached actual and estimated execution plans of the query.
Quick option:
Reduce the number of rows returned. You have no filters on your query. This is going to read most of the tables' rows.
In addition, once you add filters on your query, ensure to have proper indexes in place.
You may use the script below to examine column uniqueness
SELECT
'CURRENT_BALANCE' AS ColumnName,
COUNT(DISTINCT CURRENT_BALANCE) AS DistinctColValues,
COUNT(CURRENT_BALANCE) AS NumberOfRows,
(CAST(COUNT(DISTINCT CURRENT_BALANCE) AS DECIMAL) / CAST(COUNT(CURRENT_BALANCE) AS DECIMAL)) AS Selectivity,
(1.0/(COUNT(DISTINCT CURRENT_BALANCE))) AS Density
FROM
TBL_F_ENCOUNTER_VISITS
The column with the highest number of unique values (or selectivity) can be the best candidate for indexing
when referred to in a WHERE clause or a join criterion.
Furthermore, you may use the script below to check missing indexes.
SELECT
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
+ '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
+ ' ON ' + mid.statement
+ ' (' + ISNULL (mid.equality_columns,'')
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
+ ISNULL (mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply