how to optimize adjust costing

amphysvenaamphysvena Member Posts: 113
i want to make adjust costing operation faster
by using SQL20005 table partitioning method

here's my question :
1. which table in SQL that i should choose to part?
2. or if i have to find it myself,
is there any tools in SQL that could trace the involved table during the operation (read/write/update)?

previously, i already some suggested method to get an improvement:
1. table partition (by guessing) on item ledger enty (ILE) by using entry no. as the separator and put it on the different disk
2. rebuilt database index (as suggested by Tuning Navision for better peformance rev 5 - Patrice Dupont)
so far those. Thx

Comments

  • ara3nara3n Member Posts: 9,255
    what version are you on? Take a look at 5.0 for code changes.
    This will improve performance a lot more than moving specific tables to a separate raid.

    Also look at the Keys in nav and make the sql friendly.

    As far as what tables adjust cost looks at, depends on what costing method.
    The tables are Item ledger, Value entry, Item application entry, BOM Ledger, capacity ledger,

    You can find them by starting and using sql profiler.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • andreadjandreadj Member Posts: 12
    whoa thks for a fast reply...
    i'm the real poster, i ask my friend to post it due to the bad connection

    i use a NAV4 SP2
    so, i could just copy the Codeunit from NAV5 and replace it on NAV4?
    the CU5804?
    The tables are Item ledger, Value entry, Item application entry, BOM Ledger, capacity ledger,
    the costing method is average, so that's all the table involved?
    besides the code replacement,
    if i also part those table using table partition (beside code replacing), will it contribute to the performance?

    as for SQL profiler, i'm kinda new on those thing, any links for me to start learning?

    many thanks :D
  • cnicolacnicola Member Posts: 181
    Waaaaaiiitttttt =; !!!!!!

    You cannot just replace codeunits. Just have a look at 5.00 to see what changes they have done to improve the speed and see if and where you can apply them to your codeunit.
    I did my own changes to the 3.60 by analyzing where it was running slow and in the end I also changed it to run it by item. I frankly don't think table partitioning will help that much but would be interested to find out if I am wrong.
    Apathy is on the rise but nobody seems to care.
  • DenSterDenSter Member Posts: 8,304
    Partitioning those tables is not going to make a lot of difference, and will cost you HUGE amounts of time to figure out. The best way (i.e. the most results with the least effort) to speed that up is to follow Rashed's suggestion and take advantage of the new version's improvements.
  • krikikriki Member, Moderator Posts: 9,094
    And better also use 4.00SP3 client for SQL2005.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


Sign In or Register to comment.