1

I don't see any missing indexes on the execution plan apart from Hash Match, Parallelism and Index Scan.

I get 218,673 rows in 5 minutes. Planning the improve the performance. Here is the sql query, let me know if there is room to improvement:

SELECT
epi.EPI_ID as EPI_ID
,ISNULL(npi.ProviderLastName,'') + ', ' + ISNULL(npi.ProviderDegree,'') + ' ' + ISNULL(npi.ProviderFirstName,'')  as ProviderLastNameFirst
,ISNULL(npi.ProviderFirstName,'') + ' ' + ISNULL(npi.ProviderLastName,'') + ' ' + ISNULL(npi.ProviderDegree,'') + ' (' + CONVERT(varchar(20),e.ProviderNPI) + ')' as 'EPI Top Rank Provider'
,CASE WHEN e.PracticeProviderAffiliatedFlag = 1 
THEN ISNULL(npi.ProviderFirstName,'') + ' ' + ISNULL(npi.ProviderLastName,'') + ' ' + ISNULL(npi.ProviderDegree,'') + ' (' + CONVERT(varchar(20),e.ProviderNPI) + ')' 
ELSE NULL END as 'EPI Top Rank Provider w Affiliation'
,epi.DRG_CD + ' - ' + drg.[MS_DRG_Title] as DRG
,epi.ANCHOR_BEG_DT as 'Anchor Start Date'
,cpl.CarePlanName as 'EPI Joint Type'
,e.CPT + ' ' + cpt.[Description] as 'CPT with Description'
,CASE WHEN e.Trauma = 1 
THEN 'Trauma' 
ELSE 'Elective' END as 'Surgery Type'
,ccn.LocationName + ' (' + e.PROVIDER_CCN +')' as 'Acute Provider'  
,LEFT(DATENAME(month,epi.ANCHOR_BEG_DT),3) as 'Month'
,YEAR(epi.ANCHOR_BEG_DT) as 'Year'
,p.LastName + ', ' + p.FirstName + CASE WHEN p.MiddleName IS NULL 
        THEN '' 
        ELSE ' ' + LEFT(p.MiddleName,1) END as 'Patient Name'
,p.DateOfBirth as 'Patient DOB'
,CASE WHEN p.AGE < 65 THEN '< 65' 
WHEN p.AGE BETWEEN 65 AND 70 THEN '65-70' 
WHEN p.AGE BETWEEN 71 AND 75 THEN '71-75'
WHEN p.AGE BETWEEN 76 AND 80 THEN '76-80'
WHEN p.AGE BETWEEN 81 AND 85 THEN '81-85'
WHEN p.AGE IS NULL THEN NULL
ELSE '86+' END as 'Age Range'
,1 as 'EPIs'
,epi.EPI_ALLOWED_PAY as 'Total Spend'
,ext.AcuteTotalSpend as 'Acute Total Spend'
,ext.AcutePartATotalSpend as 'Acute Part A Total Spend'
,ext.AcutePartBTotalSpend as 'Acute Part B Total Spend'
,ext.PostAcuteTotalSpend - ISNULL(ext.ReadmitTotalSpend,0.00) as 'Post Acute Total Spend'
,case when ext.IRFTotalSpend > 0 THEN 1 else 0 END as 'IRF EPIs'
,case when ext.IRFTotalSpend > 0 THEN ext.IRFTotalSpend ELSE NULL END as 'IRF Total Spend'
,irf.LengthOfStay as 'IRF Total Days'
,case when ext.SNFTotalSpend > 0 OR ISNULL(snf.LengthOfStay,0) > 0 THEN 1 else 0 END as 'SNF EPIs'
,case when ext.SNFTotalSpend > 0 THEN ext.SNFTotalSpend ELSE NULL END as 'SNF Total Spend'
,snf.LengthOfStay as 'SNF Total Days'
,case when ext.HHTotalSpend > 0 OR ISNULL(hhh.VisitCount,0) > 0 THEN 1 else 0 END as 'HH EPIs'
,case when ext.HHTotalSpend > 0 THEN ext.HHTotalSpend ELSE NULL END as 'HH Total Spend'
,hhh.LengthOfStay as 'HH Total Days'
,ext.OutpatientTotalSpend as 'Outpatient Total Spend'
,CASE WHEN ext.ReadmitTotalSpend > 0 THEN ext.ReadmitTotalSpend ELSE NULL END as 'Readmit Total Spend'
,CASE WHEN ISNULL(r.[Readmit Incidents],0) > 0 THEN 1 ELSE 0 END AS 'Readmit EPIs'
,ISNULL(r.[Readmit Incidents],0) as 'Readmit Incidents'
,ext.PostAcutePartBTotalSpend as 'Readmit & Post Acute Part B Total Spend'
--,case when ext.ReadmitTotalSpend > 0 THEN ext.ReadmitTotalSpend+ext.PostAcutePartBTotalSpend ELSE NULL END as 'Readmit & Post Acute Part B Total Spend'
,ext.DMETotalSpend as 'DME Total Spend'
,ext.LTCHTotalSpend as 'LTCH Total Spend'
,CASE WHEN e.PracticeProviderAffiliatedFlag = 1 THEN 'Affiliated' ELSE 'Not Affiliated' END as 'Affiliation'
,epi.BPID as 'Group ID'
,ent.[Name] as 'Group'
,ent.[Name] as 'Selected Group'
,CASE WHEN e.DRGAssigned = 1 THEN 'TRUE' ELSE 'FALSE' END as 'Include/Exclude?'
,CONVERT(varchar(20),e.ProviderNPI)  as 'ProviderNPI'
,substring(datename(mm, getdate()),1,3) + cast(datepart(yyyy, getdate()) as varchar) as [Date]
,'' as [Period]
,'' as TimeFrame
,hhh.VisitCount as 'HH Visit Count'
,ext.WindsorizedTotalSpend AS 'Total Spend (Winsorized)'
,ext.NPRA as 'NPRA'
,CASE WHEN ISNULL(oph.Visits,0) > 0 THEN 1 ELSE 0 END as 'OP PT EPIs'
,oph.Visits as 'OP PT Visit Count'
,oph.REVPMT+oph.PTNRSP as 'OP PT Total Spend'
,pp.PracticeID
,isnull(ppp.Name, 'Unknown [' + cast(isnull(pp.PracticeID,0) as varchar)+ ']') as 'Practice Name'
,epi.EpisodeID
,e.EnterpriseID
,e.DRG_CD as DRG_Code
,e.TargetTrauma
,e.CJRTrauma
,e.DRGAssigned
,ent.Name as EnterpriseName
FROM dbo.[EPI] epi
LEFT JOIN dbo.[EPI_EXT] ext     
ON epi.EPIRecordID = ext.EPIRecordID
LEFT JOIN dbo.[Episode] e 
ON epi.EpisodeID = e.EpisodeID
LEFT JOIN dbo.[Patient] p 
ON e.PatientID = p.PatientID
LEFT JOIN dbo.[LU_MS_DRG_VALUES] drg 
ON epi.DRG_CD = drg.MS_DRG 
AND YEAR(epi.ANCHOR_END_DT) = drg.[Year]
LEFT JOIN dbo.[MasterNPI] npi 
ON e.ProviderNPI = npi.NPI
LEFT JOIN dbo.[PracticeProvider] pp
ON e.ProviderNPI = pp.ProviderNPI AND e.BPID = pp.BPID AND e.ANCHOR_BEG_DT >= pp.BPCIBeginDate AND e.ANCHOR_BEG_DT < ISNULL(pp.BPCIEndDate,CONVERT(date,'2099-12-31'))
LEFT JOIN dbo.[Practice] ppp
ON pp.PracticeID = ppp.PracticeID
LEFT JOIN dbo.[MasterCCN] ccn 
ON ccn.CCN = e.PROVIDER_CCN
LEFT JOIN dbo.[Enterprises] ent 
ON e.EnterpriseID = ent.EnterpriseID
LEFT JOIN dbo.[MasterCPT] cpt 
ON e.CPT = cpt.CPT
LEFT JOIN dbo.[MasterCarePlans] cpl 
ON cpt.DefaultPinPointCarePlanID = cpl.PinPointCarePlanID
LEFT JOIN (
SELECT ip.EpisodeID, COUNT(*) as 'Readmit Incidents'
FROM dbo.[IPHDR] ip
LEFT JOIN dbo.[IPHDR_EXT] ipx ON ip.IPHDRRecordID = ipx.IPHDRRecordID
WHERE ipx.AnchorStay = 0 AND ipx.ProviderType NOT IN ('IRF','LTCH') AND ip.COSTINC = 1
GROUP BY ip.EpisodeID
) r 
ON epi.EpisodeID = r.EpisodeID
LEFT JOIN (
SELECT ip.EpisodeID, SUM(ipx.LengthOfStay) as LengthOfStay
FROM dbo.[IPHDR] ip
LEFT JOIN dbo.[IPHDR_EXT] ipx ON ip.IPHDRRecordID = ipx.IPHDRRecordID
WHERE ipx.ProviderType IN ('IRF')
GROUP BY ip.EpisodeID
) irf 
ON epi.EpisodeID = irf.EpisodeID
LEFT JOIN (
SELECT sn.EpisodeID, SUM(snx.LengthOfStay) as LengthOfStay
FROM dbo.[SNHDR] sn
LEFT JOIN dbo.[SNHDR_EXT] snx ON sn.SNHDRRecordID = snx.SNHDRRecordID
GROUP BY sn.EpisodeID
) snf 
ON epi.EpisodeID = snf.EpisodeID
LEFT JOIN (
SELECT hh.EpisodeID, SUM(hhx.LengthOfStay) as LengthOfStay, (SELECT COUNT(DISTINCT REV_DT) FROM dbo.[HHDTL] WHERE EpisodeID = hh.EpisodeID) AS VisitCount
FROM dbo.[HHHDR] hh
LEFT JOIN dbo.[HHHDR_EXT] hhx ON hh.HHHDRRecordID = hhx.HHHDRRecordID
GROUP BY hh.EpisodeID
) hhh 
ON epi.EpisodeID = hhh.EpisodeID
LEFT JOIN (
SELECT dtl.EpisodeID
,COUNT(DISTINCT [REV_DT]) AS Visits
,SUM([REVPMT]) as REVPMT
,SUM([PTNRSP]) as PTNRSP
FROM dbo.[OPDTL] dtl
where REV_CNTR in ('0420','0421','0422','0423','0424','0429','0977')
GROUP BY dtl.EpisodeID
) oph 
ON epi.EpisodeID = oph.EpisodeID
  • 2
    If anyone is going to have a chance at improving this query, you'll need to add the definitions of all the tables included, as well as the indexes and approximate row counts. An actual query plan would also help. – Daniel Hutmacher Jul 8 '16 at 19:02
  • 1
    One quick-win that may get you closer to your target could be if you pre-loaded all your subqueries (r, irf, snf, hhh, oph) into their own respective temp tables and add a unique clustered index on those temp tables (on the EpisodeID column). Then join those temp tables instead. – Daniel Hutmacher Jul 8 '16 at 19:06
  • 1
    Post the table definition + actual execution plan. Is there any blocking when you run the query ? Is it running slow today or always ? – Kin Shah Jul 8 '16 at 19:19
0

Not sure if it will improve performance but you are killing a number of left join with the where
WHERE ipx.ProviderType IN ('IRF') turns that into a join

SELECT ip.EpisodeID, SUM(ipx.LengthOfStay) as LengthOfStay
FROM dbo.[IPHDR] ip
LEFT JOIN dbo.[IPHDR_EXT] ipx ON ip.IPHDRRecordID = ipx.IPHDRRecordID
WHERE ipx.ProviderType IN ('IRF')
GROUP BY ip.EpisodeID

same as

SELECT ip.EpisodeID, SUM(ipx.LengthOfStay) as LengthOfStay
FROM dbo.[IPHDR] ip
JOIN dbo.[IPHDR_EXT] ipx 
      ON ip.IPHDRRecordID = ipx.IPHDRRecordID
     AND ipx.ProviderType IN ('IRF')
GROUP BY ip.EpisodeID

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.