How to improve posting process time

vyanku

How can I improve posting process time on NAV 5.0 SP1 SQL database? :-k

David_Singleton

Post less data.

jannestig

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

kriki

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]

davmac1

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_Chow

David Singleton wrote:

Post less data.

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

stryk

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