You’re offline. This is a read only version of the page.
We have a 64 bot system with 40 GB RAM. Is it recommended to have 60 MB of virtual memory? We are having very slow response on updates to Management Reporter.
*This post is locked for comments
This problem appears to be resolved when we updated Statistics on SY40100
It sounds like you are running into a performance issue with the Data Mart integration updating. I would recommend insuring the System Requirements are met for Management Reporter and all applications installed on the environment to resolve the performance delay.
Management Reporter for Microsoft Dynamics ERP Installs and Service Packs Overview
mbs.microsoft.com/.../MROverview
If the issue is still occurring please create a Support Request to troubleshoot the performance issue.
The query that take over an hour.. I know we even added an index to GL40200. We can add one to GL20000, but since that table wll have about 10 million records in it at the end o the year, I am concerned about the impact of posting to that table or adding a nonstandard index. We post thousands of transaction a day and the GL40200 has ten years o data. The ChangeTable function itself can be slow returning values when I query it by itself. I noticed the the majority of what is being tracked in Change Tracking is unposted, and we never report on signposted items.
Can we turn off Change Tracking for Gl10000, and GL00001? Will that help performance?
Can we safely add and index to GL20000 and not have it impact posting to the table? In the past when I attempted to dd indexes to PM302000, it caused batches to get stuck and not post.
C
Select count(distinct a.GLTransactionID) from ( SELECT seg1.ACTNUMBR_1, seg1def.DEX_ROW_ID as ACTNUMBRID_1, seg2.ACTNUMBR_2, seg2def.DEX_ROW_ID as ACTNUMBRID_2, seg3.ACTNUMBR_3, seg3def.DEX_ROW_ID as ACTNUMBRID_3, seg1.ACTDESCR, GL102.ACCATDSC as Category, GL102.DEX_ROW_ID as CategoryID,
GL105.ACTNUMST, GL105.ACTINDX, GL0.PERIODID, GL0.TRXDATE, GL0.SOURCDOC, GL0.REFRENCE,
seg1.ACCATNUM, GL0.TRXSORCE, GL0.XCHGRATE, GL0.ORTRXSRC, GL0.SERIES, GL0.VOIDED,
GL0.Adjustment_Transaction, GL401.Ledger_Name, GL0.JRNENTRY, GL0.CURRNIDX, MC4.FUNCRIDX, GL0.DEX_ROW_ID as GLTransactionID, GL0.DSCRIPTN,
GL0.ORDOCNUM, GL0.ORMSTRID, GL0.ORMSTRNM, GL0.ORTRXTYP, GL0.DEBITAMT as DebitAmount, GL0.CRDTAMNT as CreditAmount,
GL0.ORDBTAMT as OrigDebitAmount, GL0.ORCRDAMT as OrigCreditAmount,GL0.SEQNUMBR, (rtrim(cast(GL0.DEX_ROW_ID as nvarchar(30)))) as keyString, SY401.BBF, OPENYEAR as YEAR1, 'GL20000' KeyTable FROM GL20000 GL0
join GL00105 GL105 on GL0.ACTINDX = GL105.ACTINDX
join GL40001 GL401 on GL401.Ledger_ID = GL0.Ledger_ID
cross join MC40000 MC4 JOIN GL00100 seg1 on seg1.ACTINDX = GL105.ACTINDX
left join GL40200 seg1def on seg1def.SGMNTID = seg1.ACTNUMBR_1 and seg1def.SGMTNUMB = 1 JOIN GL00100 seg2 on seg2.ACTINDX = GL105.ACTINDX
left join GL40200 seg2def on seg2def.SGMNTID = seg2.ACTNUMBR_2 and seg2def.SGMTNUMB = 2 JOIN GL00100 seg3 on seg3.ACTINDX = GL105.ACTINDX
left join GL40200 seg3def on seg3def.SGMNTID = seg3.ACTNUMBR_3 and seg3def.SGMTNUMB = 3 left join GL00102 GL102 on GL102.ACCATNUM = seg1.ACCATNUM join
(select 0 BBF, YEAR1, PERIODID, PERIODDT, PERDENDT from SY40100 where FORIGIN = 1 and PERIODID = 0
union
select 1 BBF, YEAR1, PERIODID, PERIODDT, PERDENDT from SY40100 where FORIGIN = 1 and PERIODID != 0) SY401
on GL0.OPENYEAR = SY401.YEAR1
and ((SY401.BBF = 0 and GL0.TRXDATE = SY401.PERIODDT and GL0.SOURCDOC in ('BBF', 'P/L')) OR (SY401.BBF = 1 and GL0.TRXDATE <= SY401.PERDENDT and GL0.TRXDATE >= SY401.PERIODDT)) INNER JOIN CHANGETABLE(CHANGES GL20000, 7149664) GL0_CT ON GL0.DEX_ROW_ID = GL0_CT.DEX_ROW_ID AND GL0_CT.SYS_CHANGE_VERSION <= 7224285 ) a
What do you mean by return to legacy> We were setup only to use the datamart as far as I know,
There is a query that does a count, and this query appears to tale over an hour to run. The running of that query often even blocks the users from yjeir normal posting processes, or slows them dramatically. One transaction can take fifteen minutes to post.
We have about 300,000 rows in Change Tracking in three days, but the majority of these relate to unposted GL items, Are we able turn off Change Tracking for GL10000 and GL10100, since we just want to report on posted items,
I will post the query that takes an hour or more to run in anotherr response.
Various things you can try.
1. Ignore the recommended specification - and beef it up as much as you can!!
2. temporarily remove the data mart if you have it installed, and return to the legacy - see if it makes a performance difference.
What kinds of performance issues are you seeing - doing what and timings etc.
MR is slower than FRx - so if you are coming from FRx, you will notice a difference. But they are two completely different products.
When you say ;updates' - what do you mean?
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,188
Super User 2024 Season 2
#2
Martin Dráb
230,030
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (