Dynamics Community Forum Thread Details

Which part should be corrected to make Query performance faster in SSMS?

select
ivc.InvoiceNumber
, ivc.BillComplContrHdrNumber
, ivc.SummaryInvoiceNumber
, ivc.IndivOrChgCmplContrNumber
, ivc.CustAcctSiteCode
, hd.CustAcctSiteCode
from
earth_ad_CustInvoice as ivc join earth_ad_CustInvoiceHeader hd
on (
ivc.SummaryInvoiceNumber != ''
and ivc.SummaryInvoiceNumber = hd.SummaryInvoiceNumber
)
or (
ivc.SummaryInvoiceNumber = ''
and ivc.InvoiceNumber = hd.InvoiceNumber
)
where
ivc.ChrgComplInvNumModifier in ('000', 'Z')
and ivc.BillComplContrHdrNumber not like N'H%'
and ivc.InvoiceProgress in ('Z', '6')
and ivc.ConfirmFormDisplayFlag = 'N'
and ivc.InvoiceEditedFlag in ('1', '2')
and ivc.InvalidFlag = '0'
and ivc.EmergencyStopFlag != '1'
and ivc.InpSlipCorrectionFlag = 'N'
and ivc.DATAAREAID = 'b002'
and not exists (
select
ChannelClassification
from
earth_md_Corporation co join CustTable ct
on ct.earth_md_CorporationCode = co.CorporationCode
and ct.DATAAREAID = 'b002'
join earth_ad_SalesTradingContracts sa
on sa.CustAcctSiteId = ct.AccountNum
and sa.SalesTradingContractNumber = ivc.IndivOrChgCmplContrNumber
and sa.DATAAREAID = 'b002'
where
co.ChannelClassification =2
)
and not exists (
select
SummaryInvoiceNumber
from
earth_ad_CustSummaryInvoice csi
where
csi.SummaryInvoiceNumber = ivc.SummaryInvoiceNumber
and csi.ChrgComplWaitingFlg = 'Y'
and csi.DATAAREAID = 'b002'
)

Quick Links

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL