emeadaxsupport: Performance Improvement on "Multisite activation – Dimension inconsistency clean-up" Job

[Blog bot]

Источник: http://blogs.msdn.com/b/emeadaxsuppo...-quot-job.aspx
==============

After enabling Multisite activation (Administration > Setup > System > Multisite Activation), you would typically run the "Multisite activation – dimension inconsistency clean up" job (Administration > Periodic > System > Multisite activation – dimension inconsistency clean up). However, I have found in some cases there can be a performance issue with this process.


Here are some details on this issue including a workaround for this to improve the performance.


Details of the problem...


Within this job there is a particular query used, that depending on the customer’s data constellation, can greatly reduce the runtime performance. This query is as follows:

SELECT A.INVOICEID,A.INVOICEDATE,A.LINENUM,A.INVENTTRANSID,A.INVENTREFID,

A.ITEMID,A.EXTERNALITEMID,A.NAME,A.TAXGROUP,A.CURRENCYCODE,A.DLVCOUNTY,

A.PRICEUNIT,A.QTY,A.SALESPRICE,A.DISCPERCENT,A.DISCAMOUNT,A.LINEAMOUNT,

A.SALESGROUP,A.LEDGERACCOUNT,A.DIMENSION,A.DIMENSION2_,A.DIMENSION3_,

A.QTYPHYSICAL,A.PARTDELIVERY,A.REMAIN,A.DLVSTATE,A.SALESID,A.SALESUNIT,

A.SALESMARKUP,A.COMMISSCALC,A.COMMISSAMOUNTCUR,A.TRANSACTIONCODE,

A.DELIVERYTYPE,A.COUNTRYREGIONOFSHIPMENT,A.ITEMCODEID,A.TAXAUTOGENERATED,

A.TAXITEMGROUP,A.TAXAMOUNT,A.TAXWRITECODE,A.MULTILNDISC,A.MULTILNPERCENT,

A.LINEDISC,A.LINEPERCENT,A.ORIGSALESID,A.LINEHEADER,A.TRANSPORT,

A.INVENTDIMID,A.ORIGCOUNTRYREGIONID,A.NUMBERSEQUENCEGROUP,A.STATPROCID,

A.DLVDATE,A.LINEAMOUNTTAX,A.PORT,A.CUSTOMERLINENUM,A.LINEAMOUNTMST,

A.TAXAMOUNTMST,A.LINEAMOUNTTAXMST,A.COMMISSAMOUNTMST,A.SUMLINEDISC,

A.SUMLINEDISCMST,A.INTERCOMPANYINVENTTRANSID,A.OLAPCOSTVALUE,

A.EINVOICEACCOUNTCODE,A.RETURNDISPOSITIONCODEID,A.STATLINEAMOUNTMST,

A.INTRASTATDISPATCHID,A.INVENTQTY,A.ORIGSTATE,A.INVENTREFTYPE,

A.INVENTREFTRANSID,A.DLVCOUNTRYREGIONID,A.WEIGHT,A.RETURNARRIVALDATE,

A.RETURNCLOSEDDATE,A.REASONREFRECID,A.RECVERSION,A.RECID,B.INVENTDIMID,

B.INVENTBATCHID,B.WMSLOCATIONID,B.WMSPALLETID,B.INVENTSERIALID,

B.INVENTLOCATIONID,B.CONFIGID,B.INVENTSITEID,B.RECVERSION,B.RECID

FROM CUSTINVOICETRANS A,INVENTDIM B WHERE (A.DATAAREAID='CEU')

AND ((B.DATAAREAID='CEU') AND (((B.INVENTSITEID='3')

AND (B.INVENTLOCATIONID='34')) AND (A.INVENTDIMID=B.INVENTDIMID)))

AND EXISTS (SELECT 'x' FROM INVENTTRANS C

WHERE ((C.DATAAREAID='CEU') AND

(((((C.DATESTATUS>='2010-02-26 00:00:00.000')


<span style="font-family: 'Courier New'; font-size: 8pt; mso-fareast-font-family: 'Times New Roman'; mso-fareast-language: EN-GB;">AND (C.DATESTATUS