Large Item Ledger Entries

vicky_dada

Hello Experts,

Please advice me on this, I have my client who manufactures liquids and packs in bottles. The bottles are recyclable i.e., full bottles are transfferd from Loc A to Loc B and again empty bottles are transffered from loc B to Loc A, there is no selling of bottles.

So I have used Item Reclass Jnl to make these type of entries.

Now the problem is once the number of transactions(around 50000 entries per day) got increased to some millions, system is taking time to post only item reclass journal (other postings like adjustments are fine). After running client monitor, the only function which is taking time is "CheckCyclicFwdToAppliedEnties" and based on that I made the following observation on system taking time:
1. the immense number of Transfer postings
2. the fact that those transfer postings forward its cost over an enormous level among a couple of locations back and forth.

Since the costing chain is linked, even Adjust cost entries batch job is taking time. Please give any suggestions on increasing the posting time.I have an idea of making adjustment entries instead of transfers but what if the same thing happens after some hundread billions entries. #-o

I am working on NAV2009 and costing method of Items is FIFO and the issue only comes for the location and Item with large number of transaction, If I use the same item in a different location which has less number of transactions then there is no issue. I have tried with NAV2009 SP1 but the problem is the same.

I have tried the same scenario for new items with Average Costing system, system is working fine. [-(

The inventory setup is as follows:
Automatic Costing - No
Automatic Cost Adjustment - Never
Average Cost Calc. Type - Item

Since it is a special case, I dont think it has thing with performance of Database, although I tried indexing, tuning, optimisation, fragmentation, creating SQL index for keys, disabling maintain SQL index for unused keys in Item Application, Item Ledger, Value entry etc. My hardware is fine, 64 bit, data files placed on different drives, RAID 0+1, everything what a good system should have.

Hoping your replies

bbrown

What is the intended purpose of the separate locations? Is it to to allow for separate inventory valuation reporting or just to indicate the bottles are in different places? If just to indicate the bottles are in different places, then consider using a single location with bins. This would eliminate the extra Item Ledger Entries created by the transfers (reclass). You could still use the same item reclass process. You may need to do some performance tuning around the warehouse entry table as it would get quite large over time.

generic

i'm sorry that you are experiencing performance issue.

You can also read this thread on the same subject.

viewtopic.php?f=23&t=36514&hilit=CheckCyclicFwdToAppliedInbnds


Navision needs to go through all the entries to applied entries to check for cyclic application.
In order to do this it has written recursive functions to do this.

This performance could have been solved if NAV used SQL HIERARCHYID but it was only released in sql 2008.

The other option is not do what you are doing and use bins as mentioned above.

vicky_dada

Thanks for the replies,

We are using SQL 2008, can you please tell me about SQL HIERARCHYID?

bbrown

vicky dada wrote:

Thanks for the replies,

We are using SQL 2008, can you please tell me about SQL HIERARCHYID?

You can read about it in SQL Books OnLine. Is there a particular question you had about it? How do you see this resolving your issue? I don't.

vicky_dada

Hi bbrown,

I heard something new about hierachy ID, so I was stressing on it.

The thing is insead of transfers I am using adjustment entries.

while transfering Loca A - Loca B - Item reclass (i.e loading of full botles)
while transfering Loca B - Loca A - Adjustments (retuning of empty bottles)

but I am still not sure how will this effect the system?
Regarding Bin transfer, the same scenario repeats, even this is transfers. I hope ther would be any improvement for ACIE

Please comment on it

bbrown

vicky dada wrote:

Hi bbrown,

I heard something new about hierachy ID, so I was stressing on it.

The thing is insead of transfers I am using adjustment entries.

while transfering Loca A - Loca B - Item reclass (i.e loading of full botles)
while transfering Loca B - Loca A - Adjustments (retuning of empty bottles)

but I am still not sure how will this effect the system?
Regarding Bin transfer, the same scenario repeats, even this is transfers. I hope ther would be any improvement for ACIE

Please comment on it

Bin to Bin transfers, within the same location, do not create Item Ledger Entries. This would eliminate the Adjust Cost issue.

Why are you doing an adjustment to move from A to B and not a transfer? That is going to mess up your costing.