mw112009 (10/2/2015)
Let me start fresh here....BTW-- I commented out the first queryLets forget the first query.. That takes only taking 90 milliseconds.. Ignore that for now.
This is what is taking 4 seconds... I even took the DISTINCT away and I am depending on the UNION to return a distinct set of rows.
Works... But the 4 seconds has to be cut down.. I have attached the query plan
QUERY PLAN For one single query is attached.
INSERT into #tmpMHPCLMDET
(ADMDT,FILET,FORMN,SSVDT,PCDCD,PRVNO,PCDQT,BILAM,ALWAM,COPAM,WITAM,NETAM,PAYST,
AJRSN,DCTAM,NCVAM,NCRSN,EDI835RSN,
CHKNO,CHKDT,HCPCS,OICPD,REVCD,HLIID,IDAMT,APCCD,
APPAYST,BANCD,RCVDT,APCSI,ESSNPI,SSNPI,
IsReversal,PatientResponsibility,Denied,ENSVDT,Mod2,Mod3,Mod4)
Select cd.ADMDT,'H' FILET, cd.FORMN,cd.SSVDT,cd.PCDCD,cd.PRVNO,cd.PCDQT,cd.BILAM,cd.ALWAM,cd.COPAM,cd.WITAM,cd.NETAM,cd.PAYST,
cd.AJRSN,cd.DCTAM,cd.NCVAM,cd.NCRSN, CAST('' AS VARCHAR(50)) EDI835RSN ,
hh.CHKNO,cd.CHKDT,cd.HCPCS,cd.OICPD,cd.REVCD,cd.HLIID,cd.IDAMT,cd.APCCD,
cd.APPAYST,cd.BANCD,cd.RCVDT,cd.APCSI,'' ESSNPI, '' SSNPI,
'0' IsReversal , CAST('' AS VARCHAR(50)) PatientResponsibility,
0 Denied , 0 ENSVDT, '' Mod2, '' Mod3, '' Mod4
from
dbo.hd835dp dd
INNER JOIN dbo.hh835DP hd
ON ((dd.PSVDT = hd.PSVDT ) AND (dd.FORMN = hd.FORMN ) AND (dd.FILET = hd.FILET) )
INNER JOIN dbo.hh835hp hh
ON ((hh.chkdts = hd.chkdt ) AND (hh.BANCD = hd.BANCD ) AND (hh.chkno = hd.chkno) )
INNER JOIN dbo.hinsdp cd ON ((dd.PSVDT = cd.admdt) AND (dd.formn = cd.formn ) AND (hh.CMPCD = cd.CMPCD))
where
(@VendorNumber is null or (hh.vndno = @VendorNumber))
AND hh.chkdts = @CheckRunDate
AND dd.DPSTF = 'N'
AND hd.FILET = 'H'
AND cd.EDI835Exclude = 0
---and (@CompanyCode IS NULL OR(cd.CMPCD = @CompanyCode))
--and (@CheckNumber IS NULL OR(hh.CHKNO like @CheckNumber))
union
Selectcd.PSVDT as ADMDT ,'E' FILET, cd.FORMN,cd.SSVDT,cd.PCDCD,cd.PRVNO,cd.PCDQT,cd.BILAM,cd.ALWAM,cd.COPAM,cd.WITAM,cd.NETAM,cd.PAYST,
cd.AJRSN,cd.DCTAM,cd.NCVAM,cd.NCRSN, CAST('' AS VARCHAR(50)) EDI835RSN ,
hh.CHKNO,cd.CHKDT,'' HCPCS, cd.OICPD,'' REVCD,cd.HLIID,cd.IDAMT,'' APCCD,
cd.APPAYST,cd.BANCD,cd.RCVDT,'' APCSI,cd.ESSNPI,cd.SSNPI,
'0' IsReversal , CAST('' AS VARCHAR(50)) PatientResponsibility,
0 Denied , 0 ENSVDT, '' Mod2, '' Mod3, '' Mod4
from
dbo.hd835dp dd
INNER JOIN dbo.hh835DP hd ON ((dd.FILET = hd.FILET ) AND (dd.FORMN = hd.FORMN) AND (dd.PSVDT = hd.PSVDT ) )
INNER JOIN dbo.hh835hp hh ON ((hh.chkdts = hd.chkdt ) AND (hh.BANCD = hd.BANCD ) AND (hh.chkno = hd.chkno ) )
INNER JOIN dbo.hencdp cd ON ((dd.PSVDT = cd.psvdt ) AND (dd.formn = cd.formn ) AND (hh.CMPCD = cd.CMPCD))
where
--(@VendorNumber is null or (hh.vndno = @VendorNumber))
--and
hh.chkdts = @CheckRunDate
and dd.DPSTF = 'N'
and hd.FILET = 'E'
and cd.EDI835Exclude = 0
--and (@CompanyCode IS NULL OR(cd.CMPCD = @CompanyCode))
--and (@CheckNumber IS NULL OR(hh.CHKNO like @CheckNumber))
Comment out the INSERT part leaving the two UNIONed SELECTS - the insert is taking a whopping 72% of the total cost which will overwhelm the costs of the SELECT. Ensure that the filters (the WHERE clause) of the two selects is exactly what you want the to be (they are currently different, the top SELECT filters on hh.vndno, the bottom one doesn't), then grab the actual execution plan again.
EDIT: also, calculate the number of rows returned by the upper and the lower query and determine if deduplication (by UNION) is performing expensive work for nothing. You, as a developer, should know if deduplication is required by this query.
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