D365 Finance & Operations and Dynamics AX Forum

Expand all | Collapse all

Job Registration taking between 20 and 50 seconds to load

  • 1.  Job Registration taking between 20 and 50 seconds to load

    Posted 28 days ago
    Hi, maybe somebody has seen this issue before, on Dynamics AX 2012 R3, we are using manufacturing execution, lately we are having issues with the job registration, after entering the badge id and hit enter, it will take 20 to 50 seconds to load the jobs, I tried everything, clean up registrations, rebuilt the indexes, re-organize them, etc.Is there something else I can do to improve the performance?  the table shows about 6000 open jobs for several resources, each resource has from 100 to 1500 open jobs (as more open jobs more time takes). I tested the AX query in SQL and  same query (for a resource with 400 open jobs) in SQL takes less than 1 second and in AX 15 seconds.

    Any help is highly appreciated.

    Tanks

    ------------------------------
    Andres Verdin
    IT Eng.

    ------------------------------


  • 2.  RE: Job Registration taking between 20 and 50 seconds to load

    Posted 28 days ago
    Edited by Solomon Maricle 28 days ago
    I'd be curious to see what a trace would show for where it is spending its time.  I recommend you capture the action in an AX trace when the environment is most quiet.  I also recommend a parallel SQL profiler trace as that can yield better SQL based information than the AX trace and look at where the expense is.

    And if you're able, I'd copy the DB to a sandbox and see if you get the same behavior there.

    ------------------------------
    Solomon Maricle
    Technical Solutions Architect
    PAR Technology
    Norwich NY
    ------------------------------



  • 3.  RE: Job Registration taking between 20 and 50 seconds to load

    Posted 28 days ago
    Thank you  for your reply 
    Looks like the table that is taking most of the time is prodroutetrans which has 400K records  (about 300K for this company),
    Trace results


    ------------------------------
    Andres Verdin
    IT
    Wall Colmonoy
    LOS LUNAS NM
    ------------------------------



  • 4.  RE: Job Registration taking between 20 and 50 seconds to load

    TOP CONTRIBUTOR
    Posted 28 days ago
    Hi Andres, you said you were on R3 but not which CU.  there were a lot of changes and cleanup jobs added with CU10 and the latest I believe is CU13.  Point being could be a known issue that's been addressed.   I would recommend getting Dynperf set up as well.   It will help you gather data to track performance issues and places you might need to put new indexes based on your processes.   I keep an eye on queries running over 5 seconds script within dynperf myself.  Not that 5 seconds is a lot but I can address those there are taking 30 or more seconds.  It will provide you the query plan ax chose among other details and nuggets it captures.   Here's a link to Dynperf.  https://community.dynamics.com/ax/b/axinthefield/posts/dynamicsperf-2-0-is-here-1416871041

    ------------------------------
    Mark Zerr
    AX Technical Analyst
    New Sunshine LLC
    Indianapolis IN
    ------------------------------



  • 5.  RE: Job Registration taking between 20 and 50 seconds to load

    Posted 28 days ago
    Hi Mark, thank you for your reply we are on CU12, I'll take a look to dynperf.

    Thanks

    ------------------------------
    Andres Verdin
    IT
    Wall Colmonoy
    LOS LUNAS NM
    ------------------------------



  • 6.  RE: Job Registration taking between 20 and 50 seconds to load

    Posted 27 days ago

    Andres,

    Have you updated SQL statistics on the affected tables? 

    UPDATE STATISTICS DB.dbo.[TableName]


    ------------------------------
    Bart Kolodziej
    Midland Paper, Packaging + Supplies
    Wheeling IL
    ------------------------------



  • 7.  RE: Job Registration taking between 20 and 50 seconds to load

    Posted 27 days ago
    Yes I did (run statistics), I found the issue,  the JmgJobTable has 2 display methods to show reported  QtyQrror and QtyGood for the same job/operation, the execution of those queries is what it taking all the time to process. That means two more queries to the table ProdRouteTrans per record on the JmgJobTable query.
    Queries on the display methods:

    return (select sum(QtyError) from prodRouteTrans
    where prodRouteTrans.TransRefId == this.ProdId &&
    prodRouteTrans.TransRefType == ProdTransRefType::Production &&
    prodRouteTrans.JobId == this.JobId &&
    prodRouteTrans.TransType == ProdRouteTransType::Qty).QtyError;

    return (select sum(QtyGood) from prodRouteTrans
    where prodRouteTrans.TransRefId == this.ProdId &&
    prodRouteTrans.TransRefType == ProdTransRefType::Production &&
    prodRouteTrans.JobId == this.JobId &&
    prodRouteTrans.TransType == ProdRouteTransType::Qty).QtyGood;

    Even do this form (job registration) does not use these fields (at least is what I saw) these fields are added to the Cache on the init method for the jmgjobtable datasource:

    jmgJobTable_DS.cacheAddMethod(tableMethodStr(JmgJobTable,badQuantity));
    jmgJobTable_DS.cacheAddMethod(tableMethodStr(JmgJobTable,goodQuantity));

    If I comment or delete  these 2 lines, the form will take no more than 5 seconds to load, but I'm not sure if that will create another issue, we are testing it right now.

    Thank you all for your help.

    ------------------------------
    Andres Verdin
    IT
    Wall Colmonoy
    LOS LUNAS NM
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role