Delay in Posting JV & INvoices

abhikadu@gmail.com

Posting gets delayed due to update on posting flag is on. In analysis view there are more than 30,00,000 Records are present and date compression for analysis view is Date. There are around daily 15000 entries for certain accounts. When we remove update on posting the posting has been done successfully.

But This problem i find it unique because this problem is inconsistent. The updation is very fast for few days and then it get slows down for 2 weeks. Then again it started posting successfully.

We have check all the hardware we had not find any problem. We have restarted the server but still the problem is exist. We also tried to post throgh RDP but still the problem is exist.

David_Singleton

abhikadu@gmail.com wrote:

Posting gets delayed due to update on posting flag is on.

I can be about 99% certain that that is not THE reason that postig is slow. Of course its most likely that it is ONE of the many reasons that posting is slow, but cettainly not the only one.

Firstly you need to post some informatino about your system.

Start with hardware (even though you think the hard ware is perfect, there is a high probability it is not).

What are you posting. Do you mean NAV sales orders posting to invoices with inventory? Is warehouse involved.

How many trnasactions, (I assume 30,00,000 means 30 thousand Laks) 3,000,000 is not a lot of analysis views entries, so it shoudl not be a major issue. But how many GL entries are there? What else is going on.

You really need to post more information, and start at the begining.

kriki

David Singleton wrote:

Start with hardware (even though you think the hard ware is perfect, there is a high probability it is not).

And even if it is, there is a high probability it is configured badly.

David_Singleton

kriki wrote:

David Singleton wrote:

Start with hardware (even though you think the hard ware is perfect, there is a high probability it is not).

And even if it is, there is a high probability it is configured badly.

yep...

abhikadu@gmail.com

Thanks for the reply,

I had visited with my System administrator, he had checked all the hardware. But at the time of posting i open the sql profile. when voucher or any invoice getting posted at time of updating analysis view it goes in a loop which is querying analysis view entry.

But when the the posting is doing susseccfully there is no as such quering anaysis view entry table.

In fact I makea clustered index in the analysis view entry. Still the problem is not resolved.

When I remove Update on posting flag the entries are get posted successfully.

David_Singleton

abhikadu@gmail.com wrote:

Thanks for the reply,

I had visited with my System administrator, he had checked all the hardware. But at the time of posting i open the sql profile. when voucher or any invoice getting posted at time of updating analysis view it goes in a loop which is querying analysis view entry.

But when the the posting is doing susseccfully there is no as such quering anaysis view entry table.

In fact I makea clustered index in the analysis view entry. Still the problem is not resolved.

You need to understand that if you want help, you have to give out information. If its confidential, then you need to ask the question woth someone that you can give the information to.

What were you tyring to achieve with the clustered index?

abhikadu@gmail.com

I am checking if I create the clustred index in analysis view entry table whether the speed of posting get increased...

abhikadu@gmail.com

The Things which i have done is as follows
1. Harware setups
2. Observe The Entries

Understand the Network, Hardware setup of the Live, Stage and UAT Server

1. Live Server
- Intel Xeon 1.87 Ghz
- 4 GB RAM
- PAE switch is available (Apart from normal setting ‘/3GB/userva = 2900’ is given)
- Page File Utilization(3.35 GB)
- Physical Memory 3405680 K
- Available memory 1530980 K
Observation
- Sql Server Management Studio & Sql Server Profile is taking more than 2 min to Open
- Third Party Application is also on live server. Total users are 650 and 350 concurrent users are connected to live server
- Other Applications installed on clients
0 Third Party Application
o MS Sql 2005
o Visual Studio 2005
o Oracle 10 G

2. Stage Server

- Intel Xeon 1.87 Ghz
- 4 GB RAM
- PAE switch is available (Apart from normal setting ‘/3GB/userva = 2900’ is given)
- Page File Utilization(2.62 GB)
- Physical Memory 3405680 K
- Available memory 561664K
- Observation
- Sql Server Management Studio & Sql Server Profile is taking more than 2 min to Open

3. UAT Server
- 4 GB RAM
- PAE switch is available (Apart from normal setting ‘/3GB’ is given)
- Page File Utilization(2.47 GB)
- Physical Memory 3405680 K
- Available memory 901886K
- Observation
- Sql Server Management Studio & Sql Server Profile is taking more than 2 min to Open

Observe the entries in Live, Stage & UAT Server at the time of posting and list out the observation.

1. Live Server
On 29/08/08 The live server was very slow at the time of posting vouchers, So the backup has been taken by SQL DBA. The Sql server services has been restarted but still user find it difficult to post the vouchers and invoices. There has been no locking of any tables or anything but the cpu process is at its highest level

But From 30/08/08 users informed IT department that the posting has been done successfully. The speed was also good.
We have checked the sql profiler, the entries has been posted very quickly

Accountant has informed us that, on 04/09/08 they have posted 136 purchase vouchers, 4 bank payments, 2 Bank receipts, 1 JV, 15000 to 20000 entries from Third Party Application. All has been posted without any problem.

2. Stage Server

On 04/09/08 the database of 29/08/08(Backup taken by DBA) has been restore in Stage Server.

We executed the application through RDP on stage server. But the same problem is arises on stage server which is similar to live server on 29/08/08. After debugging the application. We found that on code unit 13 Updating of Analysis view is taking lot of time. So removing the flag update on posting from all analysis view. The posting of vouchers has been done successfully from RDP as well as from client machine.

Rows in Analysis view entry table are apprx. 38,08,680. We applied clustered index to Analysis view entry table but still there is no difference in the performance.

All the Analysis views, in Navision have update on posting enabled. Date compression for all views is Day. From Third Party Application to Navision there has been more than 15000 to 30000 records has been transferred.

3. UAT Server
The Objects on UAT Server and live server are same. The data is very less on UAT server. So the posting of vouchers is very fast.


Major Concern
It has been noticed that posting of vouchers works successfully for few weeks and then suddenly it slows down. If there is a problem in posting it should be consistent.

David_Singleton

Is that seriously your live server?

I find it a little hard to believe that you are able to run 650 or 350 users on anythign that small. The average teenager has a higher spc laptop for runnign facebook :shock:

I am sure you got the numbers wrong.

Also you completely forgot the single most important thing; The Storage Array. What is the disk arrnagement?


do you want to think again about this:

abhikadu@gmail.com wrote:

We have check all the hardware we had not find any problem.

David_Singleton

How many users?
Database size?
Granules used?
etc etc.

David_Singleton

And how long have you been live?

abhikadu@gmail.com

The client is live from 01/03/08(dd/MM/yy)

abhikadu@gmail.com

I dont know about storage array...please give me the details....What are the other things which i have to find out.

My major problem is at client side they have lot of security constraint. As a navision partner they just asked us to create a navision database on Sql Server and to configured the system.

We have Implemented Accounts payable, Fixed Assets. Daily 15000 to 20000 entries come from the third party software which get uploaded and posted through job schedular(Navision)

abhikadu@gmail.com

abhikadu@gmail.com wrote:

I dont know about storage array...please give me the details....What are the other things which i have to find out.

My major problem is at client side they have lot of security constraint. As a navision partner they just asked us to create a navision database on Sql Server and to configured the system. All the details about the Live server is given by client(including the third party application and the no of connections), our system admin person just check the hardware and network part.

We have Implemented Accounts payable, Fixed Assets. Daily 15000 to 20000 entries come from the third party software which get uploaded and posted through job schedular(Navision)

This kind of problem i have never face in any of our client.

Please guide me....

David_Singleton

abhikadu@gmail.com wrote:

My major problem is at client side they have lot of security constraint. As a navision partner they just asked us to create a navision database on Sql Server and to configured the system.

Well hindsight it 20/20, but you should not have gotten yourselves into that situation.

Just tell them that if they can't let the experts have access to the system then they will have to live with a slow system. What they are sayingis like me taking my car to the service center, tellign them that the engine has no power, but then telling them they need to fix it asap, but that I can't let them have the keys to the car, nor can they open the hood (bonnet) so they can only work form outside the car.

Sorry, but there is no chance to help them.

kine

1) Too low RAM on the server for this count of users
2) No info about storage - there is no info about the Harddisks, if there is some SAN, or they are directly attached to the server, which RAID is used, how many spindles, which size, which RPMS...

abhikadu@gmail.com

Whether you are using SAN... Answer:-Yes
If It is So are they directly attached to server – Yes :- Fiber connectivity
Which RAID you are using: - Raid 5
How many spindles you are using and which size : (Array 1:- 146 Gb * 7 – Drive-E: 90 Gb) – (Array 2:- 146 Gb * 7 – Drive-F: 10 Gb)
Which RPMS you are using: Answer:- 15 K

kine

RAID 5??? Did you read discussions about RAID and NAV? NEVER use RAID 5 with NAV.. ;-)

abhikadu@gmail.com

Please send me any link which can give me information that raid 5 is not advisable

kine

http://www.mibuso.com/forum/search.php

garak

take also a look on your NAV CD /DVD. In the folder Doc are manuals. There are also examples for the Hardware requ.

Also use the search function, follow, kine's link. Search for "raid 5" or generally for "RAID"

regards