Posting Invoice with more than 10,000 Lines

philipuskd

Is anyone has any experiences of posting a Document (Sales Invoice in my case) with lines more than 10,000 or even 15,000 Lines?

In one of the implementation site which my team in charge, this kind of transaction is at least happens once a month for 15 to 20 Customers in NAV. Is NAV design to handle this kind of transaction? What is the consequences to post this kind of Invoice? What is the effect to the performance? Will another user will get the Lock Timeout error eventually?

David_Singleton

philipuskd wrote:

Is anyone has any experiences of posting a Document (Sales Invoice in my case) with lines more than 10,000 or even 15,000 Lines?

In one of the implementation site which my team in charge, this kind of transaction is at least happens once a month for 15 to 20 Customers in NAV. Is NAV design to handle this kind of transaction? What is the consequences to post this kind of Invoice? What is the effect to the performance? Will another user will get the Lock Timeout error eventually?

What were the results of your testing?

philipuskd

The PIC on site reported 2 big result that might happens.
1. The classic client will not responding (white blank form) and doesn't show any sign of progress until hours or even days (the customer will choose to end the task). At this point we can not be sure if the process still running.
2. The posting process will finish with a great cost of time. sometime the process finish in hours (3 up to 5 hours or more), but sometimes the same kind of transaction will only need about a half of hour.

During the posting, there are also reports of
1. Drop in performance
2. Lock timeout error

Update Statistic is done daily and Rebuilding Index Weekly. The process time will decrease dramatically (from days to less than an hour) after the update statistic and rebuilding index.

David_Singleton

You need to tune the database and find the problems.

kine

And check the hardware and settings...

davmac1

I think the biggest psrt of the performance problems is that you have to post all the lines before a commit is done.
You also have the possibility that some code is executed every time for each line depending on your settings.
Worst case is 10,000 * 10,000 * 3 (NAV likes to repeat sometimes).
So analyze what it is doing.

philipuskd

David Singleton wrote:

You need to tune the database and find the problems.

kine wrote:

And check the hardware and settings...

Up to this point I can assume that NAV is capable and/or design to capable to handle this kind of transaction. Am I right?

What is the acceptable range (fastest-slowest) of time for finishing the posting process with 10K invoice line? In what range of time (the process finished) that we can say there's abnormalities (in setting, hardware, database problems, etc)?

For picturing the situation:
There are 17 concurrent user. Most of them creating Sales Order, browsing sales line (on a custom form. the lines increase about 5000-10000 line a day), post shipments and post invoice (some with more than 10K line).

Where do we start to identify the problems?

Alex_Chow

You can start by listing your hardware specs and how it's configured (how many drives, what RAID, CPU, HD RPM, etc).

kine

Even when you will cound e.g. 0,5seconds per line, it will give you 83 minutes per document. It means you need to post one line at least in 0,1second to have total time 16 minutes for 10 000 lines document. 0,1 per line is very short time and the process must be optimized. I think even with optimal hardware, without application changes, you will have problems to have this time to post something. But it could be done. It depends on what you are posting too. E.g. resouces, G/L Accounts, Items, Charges... different types need different time.

philipuskd

Alex Chow wrote:

You can start by listing your hardware specs and how it's configured (how many drives, what RAID, CPU, HD RPM, etc).

Hi Alex,

Here what I can tell for now.

Database Server:
Model: Dell PowerEdge T710
Processor: Intel XEON E5620 @2.4 GHz
RAM: 12288 MB
RAID: None
HDD: Not sure how many, but they've separate the HDD for OS, Data Files and Log Files. The RPM is vary between 15K and 7K

Alex_Chow

philipuskd wrote:

Alex Chow wrote:

You can start by listing your hardware specs and how it's configured (how many drives, what RAID, CPU, HD RPM, etc).

RAID: None
HDD: Not sure how many, but they've separate the HDD for OS, Data Files and Log Files. The RPM is vary between 15K and 7K

Make sure the data log files and database files are on the 15k drives. Also, check the memory allocated for SQL Server.

If the hardware configuration is not the problem, you will need to run the profiler to identify what queries are taking a long time to run, then optimize accordingly.

David_Singleton

philipuskd wrote:

HDD: Not sure how many,..

"Not Sure" is the wrong answer.

Step one is to know the hardware configuration.

philipuskd

During the posting process, some entry must be made and there are locking for sure. is the table lock happens for entry table and than will be released after all the transaction committed? If yes, then the other posting process that will use the same entry table must wait for the lock to be released, am I correct?

Alex_Chow

philipuskd wrote:

During the posting process, some entry must be made and there are locking for sure. is the table lock happens for entry table and than will be released after all the transaction committed? If yes, then the other posting process that will use the same entry table must wait for the lock to be released, am I correct?

Looking at the locktable is why down the line. You haven't done the first steps yet.

Savatage

philipuskd wrote:

RAID: None

:shock: