Monday, December 9, 2013

Management Reporter 2012 RU6, CU7 integration issue causing TempDb size to blow

In Management reporter 2012, starting from RU6, Microsoft introduces customer and vendor attributes, however under some circumstances this might cause the TempDb size to blow out.

The SQL query that causes the issue is:
set dateformat mdy;
SELECT COUNT(DISTINCT GJAE.RECID) FROM (SELECT
GJAE.RECID, GJAE.Partition as Partition, GJAE.TRANSACTIONCURRENCYAMOUNT, GJAE.ACCOUNTINGCURRENCYAMOUNT, GJAE.REPORTINGCURRENCYAMOUNT,
                                  GJAE.QUANTITY, GJAE.ISCREDIT, GJAE.TRANSACTIONCURRENCYCODE, C.TXT AS CURRENCYNAME, C.SYMBOL AS CURRENCYSYMBOL, GJAE.PAYMENTREFERENCE, GJAE.POSTINGTYPE, GJAE.TEXT,
                                  GJAE.GENERALJOURNALENTRY, GJAE.REASONREF, GJAE.LEDGERDIMENSION,
                                  GJE.ACCOUNTINGDATE, GJE.JOURNALNUMBER, GJE.POSTINGLAYER, GJE.LEDGER, GJE.FISCALCALENDARPERIOD, GJE.ACKNOWLEDGEMENTDATE,
                                  GJE.DOCUMENTDATE, GJE.LEDGERPOSTINGJOURNAL, GJE.DOCUMENTNUMBER, GJE.JOURNALCATEGORY,
                                  DALVV.DIMENSIONATTRIBUTE, DALVV.DISPLAYVALUE, DALVV.ENTITYINSTANCE,
                                  RTR.REASON, RTR.REASONCOMMENT,
                                  LE.CONSOLIDATEDCOMPANY, LE.ISBRIDGINGPOSTING, MA.AccountCategoryRef, VDPT.NAME as VENDORNAME, CDPT.NAME as CUSTOMERNAME,
                                  LJT.NAME as JOURNALDESCRIPTION, LJT.JOURNALTYPE, LJT.JOURNALNAME,
                                  GJE.SUBLEDGERVOUCHER, TRT.TRACENUM, TRT.REVERSED, LJTR.REVERSEENTRY, NULL as MODIFIEDDATETIME, NULL as MODIFIEDBY, LJT.POSTEDDATETIME as JOURNALENTRYDATE, NULL as HISTORICALEXCHANGERATEDATE
FROM GENERALJOURNALACCOUNTENTRY GJAE
                           left outer join GENERALJOURNALENTRY GJE on GJAE.GENERALJOURNALENTRY = GJE.RECID
                           left outer join DIMENSIONATTRIBUTELEVELVALUEVIEW DALVV on GJAE.LEDGERDIMENSION = DALVV.VALUECOMBINATIONRECID
                           left outer join REASONTABLEREF RTR on GJAE.REASONREF = RTR.RECID
                           left outer join LEDGERENTRY LE on GJAE.RECID = LE.GENERALJOURNALACCOUNTENTRY
                           left outer join LEDGERENTRYJOURNAL LEJ on GJE.LEDGERENTRYJOURNAL = LEJ.RECID
                           left outer join LEDGERJOURNALTABLE LJT on LEJ.JOURNALNUMBER = LJT.JOURNALNUM
                           left outer join CURRENCY C on GJAE.TRANSACTIONCURRENCYCODE = C.CURRENCYCODE and C.Partition = GJAE.Partition
                           left outer join VENDTRANS VTR on GJE.SUBLEDGERVOUCHER = VTR.VOUCHER and GJE.DOCUMENTDATE = VTR.DOCUMENTDATE
                           left outer join VENDTABLE VTA on VTR.ACCOUNTNUM = VTA.ACCOUNTNUM
                           left outer join DIRPARTYTABLE VDPT on VTA.PARTY = VDPT.RECID
                           left outer join CUSTTRANS CTR on GJE.SUBLEDGERVOUCHER = CTR.VOUCHER and GJE.DOCUMENTDATE = CTR.DOCUMENTDATE
                           left outer join CUSTTABLE CTA on CTR.ACCOUNTNUM = CTA.ACCOUNTNUM
                           left outer join DIRPARTYTABLE CDPT on CTA.PARTY = CDPT.RECID
                           left outer join TRANSACTIONREVERSALTRANS TRT on GJAE.RECID = TRT.REFRECID and TRT.REFTABLEID = 3119
                           left outer join LEDGERJOURNALTRANS LJTR ON LJTR.REVERSEENTRY = 1 AND GJE.SUBLEDGERVOUCHER = LJTR.VOUCHER AND GJE.ACCOUNTINGDATE = LJTR.TRANSDATE
                           left join DIMENSIONATTRIBUTE DA ON DA.RECID = DALVV.DIMENSIONATTRIBUTE
                           left join MAINACCOUNT MA on MA.RECID = DALVV.ENTITYINSTANCE AND DA.TYPE = 2  LEFT OUTER JOIN CHANGETABLE(CHANGES GENERALJOURNALACCOUNTENTRY, 0) GJAE_CT ON GJAE.RECID = GJAE_CT.RECID
 LEFT OUTER JOIN CHANGETABLE(CHANGES GENERALJOURNALENTRY, 0) GJE_CT ON GJE.RECID = GJE_CT.RECID
 LEFT OUTER JOIN CHANGETABLE(CHANGES LEDGERJOURNALTABLE, 0) LJT_CT ON LJT.JOURNALNUM = LJT_CT.JOURNALNUM
 LEFT OUTER JOIN CHANGETABLE(CHANGES LEDGERJOURNALTRANS, 0) LJTR_CT ON LJTR.RECID = LJTR_CT.RECID
) GJAE WHERE GJAE.POSTINGTYPE <> 19


As you can see the query does not consider partition or dataAreaId when do the joins to the CustTable, CustTrans, VendTable, and VendTrans. Depending what kind of data you have, this might cause the TempDb size to blow out of the proportion.

In our case I had a 169GB AX database, and the TempDb size blew to over 500GB because of this query. The database does have a lot of customers and vendors using the same account numbers across 130+ companies.

I was told to use Management reporter RU5 instead, where it doesn't have any customer and vendor attributes, and that worked fine.

2 comments:

  1. Hello,
    Were you able to determine what is calling this query? I ask because if we were to use a forceseek hint the query would return in seconds. I cannot locate where this query is coming from.

    ReplyDelete
  2. This query comes from inside of management reporter application. I don't think there is a way to modify the query at all from inside the management reporter.

    This issue has been resolved in CU8

    ReplyDelete