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′) |
|
AND (C.DATESTATUS<=’2010-11-25 00:00:00.000′)) |
|
AND (A.INVENTTRANSID=C.INVENTTRANSID)) |
|
AND (A.INVOICEID=C.INVOICEID)) AND (A.INVOICEDATE=C.DATEFINANCIAL)))) |
|
ORDER BY A.DATAAREAID,A.SALESID,A.INVOICEDATE,A.INVOICEID, |
|
A.LINENUM,A.NUMBERSEQUENCEGROUP |
|
OPTION(FAST 1) |
The following is the actual execution plan for this…
On my test data, this query was only returning 13 rows.:The performance problem is generated by the fact that the query will return all of the records in the CUSTINVOICETRANS table, it actually does this twice, see below…
Therefore if the customer has a large CUSTINVOICETRANS table, then this query will run very slowly.
On first appearance it would seem that the SQL Query optimiser is not efficiently generating the execution plan for this. You will also see that the final part of the execution plan performs the filter on the INVENTTRANS table, returning only 13 records. So would assume that the database engine would normally filter on this first before accessing the other tables in the query. However, this plan is created in this way due to the “OPTION(FAST 1)” parameter. By default in AX, most forms will pass through this parameter on a query, with the assumption that it is important to return data to the client quickly. Conversely in this case, it actually causes the query optimiser not to generate an efficient execution plan. Removing the “OPTION(FAST 1)” produces the following execution plan…
Now you can see above that this execution plan is different, and in this case, the first part of the plan actually filters on the INVENTDIM table first, making the query much more efficient. For one customers data I tested this on, this change reduced the query execution time from 39 minutes to just 18 seconds!!!! Now you want to know how to make this work, see below…
Fixing the problem…
To make the query in this job run without this FAST 1 option, follow these simple steps…
- From the AOT, locate and open the class/method “InventSiteInconsistencyCleanUp/performUpdate”
- Just before these two lines (224 to 226)
- Add the following lines:
- So the final code will look like (see change highlighted in red):
Now when you run the job, the process should be much quicker.
|
–author: |
Thomas Treen |
|
–editor: |
Thomas Treen |
|
–date: |
25/Nov/2010 |