How to improve posting process time

vyankuvyanku Member Posts: 791
edited 2009-05-21 in SQL Performance
How can I improve posting process time on NAV 5.0 SP1 SQL database? :-k

Comments

  • David_SingletonDavid_Singleton Member Posts: 5,479
    Post less data.
    David Singleton
  • jannestigjannestig Member Posts: 999
    We handled this in a two stringed scenario. First we updated to SP1 5.0 to gain the automatic technical benefits we were looking for in out posting routines that were sometimes taking 8-12 hours in peak 4th quater trading period.

    We used the Bulk insert functionality Which while not reducing time directly always does allow other systems users to post at the same time and avoids the table lock issue for the vast majority.

    The second was that we removed some posting routines from nav and directly translated them to TSQL these were our own customised routines not nav standard. So while this is risky it was our only option to make our overnight posting routines meet the demand we required.

    There is a section in partnersource dealing with Large customers and what services MS can offer to partners NOTE they won't do the work for you but are advisors.

    You can also look and dropping parts of the posting code where its is checking or using functionality not relevant to your installation.

    Please note the example client was heavily customised
  • krikikriki Member, Moderator Posts: 9,094
    Did you do some SQL tuning? There is a lot of information on the forum. But it is best done by a specialist.

    [Topic moved from 'NAV/Navision' forum to 'SQL Performance' forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • davmac1davmac1 Member Posts: 1,283
    I moved all the heavy duty volume posting to the job queue where they:
    1) could be scheduled
    2) run one at a time (avoids collisions)

    This allows a high volume of transactions to be handled every day.
  • Alex_ChowAlex_Chow Member Posts: 5,063
    Post less data.

    :lol::lol::lol:

    Seroiusly, there are a lot of topics on this forum regarding performance. Look at this thread for more information on hardware setup vs. performance tuning:
    viewtopic.php?f=34&t=29547
  • strykstryk Member Posts: 645
    jannestig wrote:
    The second was that we removed some posting routines from nav and directly translated them to TSQL these were our own customised routines not nav standard. So while this is risky it was our only option to make our overnight posting routines meet the demand we required.
    I would say this absolutely jeopardizes the "correctness of bookkeeping". The standard NAV posting routines actually grant legality; if you fiddle with this that much, I'm not sure you fiscal reporting etc. is all right ... how did you convice your auditor to accept this? Which auditor?
    I - personally - think this is totally insane, and I'm quite curious what was the reasons for doing this. How much data (invoices, lines, etc.) had to be processed/posted? What is you system size and platform specifications?
    vyanku wrote:
    How can I improve posting process time on NAV 5.0 SP1 SQL database? :-k
    As mentioned here before: search mibuso for "SQL Performance" and you'll get tons of advices ...
    Generally speaking, you have to look into ...

    ... Hardware/Platform, Configuration
    ... used Versions & Editions (NAV and SQL)
    ... DB Maintenance & Analysis
    ... Index Optinization, Query Hinting
    ... VSIFT Optimization
    ... C/AL Optimization
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
Sign In or Register to comment.