Personalized Community is here!
Quickly customize your community to find the content you seek.
Have questions on moving to the cloud? Visit the Dynamics 365 Migration Community today! Microsoft’s extensive network of Dynamics AX and Dynamics CRM experts can help.
2022 Release Wave 1 PlanDynamics 365 release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.
2022 release wave 1 plan
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Community | FastTrack Program | Finance and Operations TechTalks | Customer Engagement TechTalks | Upcoming TechTalks | All TechTalks
For one of my client I am using journal control functionality in AX 2012 R3 CU8. I have done setup of journal control against one of the journal having vendor invoice recording journal type. Journal control setup is divided into two parts header and lines. On the header part in the company accounts field I have selected one of the company and in the account type field I have selected ledger. On the lines I have selected same company accounts which I have taken on header. On the next field on lines selected same company account structure. In the third field Segment selected main accounts and mentioned from and to values of ledger account in the fourth and fifth fields respectively.After setup part is over, I have created one invoice journal using same journal. Now on the lines of journal while selecting the financial dimension against the ledger account, system is taking time and populating the value after 15-20 seconds not instantly.
Please help if any body has any idea for it.
Thanks in Advance.
If it takes that long, it should be easy to identify the offending query in the SQL Server Management Studio with performances monitoring, or DMV's (with a DBCC FREEPROCCACHE first).
I suspect statistics in cases like these up front, because it's surprisingly common a problem, and because the solution is also so easy and painless. Run an update statistics with full scan on all dimension tables, and on any other tables also in that query (once you find it).
setup SQL Statement trace log in AX with some time threshold in milliseconds (under Tools >> options >> SQL Statement). You will be able to troubleshoot the source of each query executions in the "use" tab of the form like a call stack. This form will also include the actual query execution time.
refer : technet.microsoft.com/.../aa582474.aspx
We have done update statistics with full scan on all dimension tables and then run DBCC but problem still not resolved.
Dimension set is working fine with non journal control account as problem is only if we setup journal control A/c for particular journal name.
Request you to suggest further solution if you have any.
Thanks & Regards,
Try doing a full "rebuild indexes" using the SQL maintenance plans for AX database. You will have to get business downtime before doing this operation since, it will slow down the AX operations.
We have done Rebuild Indexes but problem still not resolved and also we have run the trace parcer and found the related Std. below query is taking so much time (57564 ms) but this query is running perfectly fast from SQL:
SELECT T1.RECID, T1.VALUE, T1.DESCRIPTION FROM DIMENSIONFINANCIALTAG T1 CROSS JOIN DIMENSIONCONSTRAINTNODEVIEW T2 CROSS JOIN DIMENSIONCONSTRAINTNODEVIEW T3 CROSS JOIN DIMENSIONCONSTRAINTNODEVIEW T4 CROSS JOIN FINANCIALTAGCATEGORY T5 CROSS JOIN DIMENSIONCONSTRAINTTREE T6 CROSS JOIN DIMENSIONATTRIBUTEDIRCATEGORY T7 CROSS JOIN DIMENSIONCONSTRAINTNODE T8 CROSS JOIN DIMENSIONCONSTRAINTNODECRITERIA T9 CROSS JOIN DIMENSIONCONSTRAINTNODE T10 CROSS JOIN DIMENSIONCONSTRAINTNODECRITERIA T11 CROSS JOIN DIMENSIONCONSTRAINTNODE T12 CROSS JOIN DIMENSIONCONSTRAINTNODE T13 WHERE (T1.PARTITION=5637144576) AND (((((T2.PARTITION=5637144576) AND (T2.PARTITION#2=5637144576)) AND (T2.PARTITION#3=5637144576)) AND (T2.PARTITION#4=5637144576)) AND (((T2.DIMENSIONHIERARCHY=5637145432) AND (T2.DIMENSIONHIERARCHYLEVEL=5637153013)) AND (((T2.WILDCARDSTRING='') AND ((((T2.RANGEFROM='') OR ((T2.ISFROMOPEN=0) AND (T2.RANGEFROM<=510101000))) OR ((T2.ISFROMOPEN=1) AND (T2.RANGEFROM<510101000))) AND (((T2.RANGETO='') OR ((T2.ISTOOPEN=0) AND (510101000<=T2.RANGETO))) OR ((T2.ISTOOPEN=1) AND (510101000<T2.RANGETO))))) OR (510101000 LIKE T2.WILDCARDSTRING ESCAPE '\' )))) AND (((((T3.PARTITION=5637144576) AND (T3.PARTITION#2=5637144576)) AND (T3.PARTITION#3=5637144576)) AND (T3.PARTITION#4=5637144576)) AND ((((T3.DIMENSIONHIERARCHY=5637145432) AND (T3.DIMENSIONHIERARCHYLEVEL=5637153015)) AND ((T1.VALUE LIKE T3.WILDCARDSTRING ESCAPE '\' ) OR ((T3.WILDCARDSTRING='') AND ((((T3.RANGEFROM='') OR ((T3.ISFROMOPEN=0) AND (T3.RANGEFROM<=T1.VALUE))) OR ((T3.ISFROMOPEN=1) AND (T3.RANGEFROM<T1.VALUE))) AND (((T3.RANGETO='') OR ((T3.ISTOOPEN=0) AND (T1.VALUE<=T3.RANGETO))) OR ((T3.ISTOOPEN=1) AND (T1.VALUE<T3.RANGETO))))))) AND (T2.DIMENSIONCONSTRAINTNODE=T3.PARENTCONSTRAINTNODE))) AND (((((T4.PARTITION=5637144576) AND (T4.PARTITION#2=5637144576)) AND (T4.PARTITION#3=5637144576)) AND (T4.PARTITION#4=5637144576)) AND (((T4.DIMENSIONHIERARCHY=5637145352) AND (T4.DIMENSIONHIERARCHYLEVEL=5637148327)) AND ((T1.VALUE LIKE T4.WILDCARDSTRING ESCAPE '\' ) OR ((T4.WILDCARDSTRING='') AND ((((T4.RANGEFROM='') OR ((T4.ISFROMOPEN=0) AND (T4.RANGEFROM<=T1.VALUE))) OR ((T4.ISFROMOPEN=1) AND (T4.RANGEFROM<T1.VALUE))) AND (((T4.RANGETO='') OR ((T4.ISTOOPEN=0) AND (T1.VALUE<=T4.RANGETO))) OR ((T4.ISTOOPEN=1) AND (T1.VALUE<T4.RANGETO)))))))) AND ((T5.PARTITION=5637144576) AND (T1.FINANCIALTAGCATEGORY=T5.RECID)) AND ((T6.PARTITION=5637144576) AND (T6.RECID=5637159576)) AND ((T7.PARTITION=5637144576) AND ((T7.DIMENSIONATTRIBUTE=5637145331) AND (T5.RECID=T7.DIRCATEGORY))) AND ((T8.PARTITION=5637144576) AND ((T8.DIMENSIONHIERARCHYLEVEL=5637160326) AND (T6.RECID=T8.DIMENSIONCONSTRAINTTREE))) AND ((T9.PARTITION=5637144576) AND ((((510101000<=T9.RANGETO) AND (510101000>=T9.RANGEFROM)) OR (510101000 LIKE T9.WILDCARDSTRING ESCAPE '\' )) AND (T8.RECID=T9.DIMENSIONCONSTRAINTNODE))) AND ((T10.PARTITION=5637144576) AND ((T10.DIMENSIONHIERARCHYLEVEL=5637160327) AND (T8.RECID=T10.PARENTCONSTRAINTNODE))) AND ((T11.PARTITION=5637144576) AND (((((T1.VALUE LIKE T11.WILDCARDSTRING ESCAPE '\' ) OR ((T1.VALUE<=T11.RANGETO) AND (T1.VALUE>=T11.RANGEFROM))) OR ((T1.VALUE<=T11.RANGETO) AND (T11.RANGEFROM=''))) OR ((T11.RANGETO='') AND (T1.VALUE>=T11.RANGEFROM))) AND (T10.RECID=T11.DIMENSIONCONSTRAINTNODE))) AND ((T12.PARTITION=5637144576) AND ((T12.DIMENSIONHIERARCHYLEVEL=5637160328) AND (T10.RECID=T12.PARENTCONSTRAINTNODE))) AND ((T13.PARTITION=5637144576) AND ((T13.DIMENSIONHIERARCHYLEVEL=5637160329) AND (T12.RECID=T13.PARENTCONSTRAINTNODE))) AND NOT (EXISTS (SELECT 'x' FROM DIMENSIONATTRIBUTEVALUELEDGERVIEW T14 WHERE ((((T14.PARTITION=5637144576) AND ((T14.PARTITION#2=5637144576) OR (T14.PARTITION#2 IS NULL))) AND ((T14.PARTITION#3=5637144576) OR (T14.PARTITION#3 IS NULL))) AND ((((T14.DIMENSIONATTRIBUTE=5637145331) AND (T14.LEDGER=5637145326)) AND (((T14.ISSUSPENDED=1) OR (T14.ISTOTAL=1)) OR (((T14.ACTIVEFROM>'1900-1-1') AND (T14.ACTIVEFROM>'2016-4-7')) OR ((T14.ACTIVETO>'1900-1-1') AND (T14.ACTIVETO<'2016-4-7'))))) AND (T1.RECID=T14.ENTITYINSTANCE))))) GROUP BY T1.RECID, T1.VALUE, T1.DESCRIPTION ORDER BY T1.VALUE
Let me know if you have further any idea.
If it runs OK within SQL itself, it might have a cause due to table cache settings. Are you able to use e.g. AX trace parser to find out about the real database time (included and excluded time)? If it cannot be proved by real database time and/or coding it might be caused by table caching or usage of temporary tables by the AOS.
I was going through known issues in LCS, found this interesting KB 3074512 Performance issue - Account drop down takes a long time on journal forms.
Please go through the same. Let me know if it works.
Business Applications communities