how to improve item ledger entry performance for NAV2015

tanjenny

I have upgraded from Nav 4.0 SP3 to NAV2015 and noticed that the item ledger entry is very slow when displaying the movements. Any idea how this can be improved. I have only 1 year of data for the item but it is very slower when compared to my old database containing 11 years of data.

Would appreciate very much if someone could advise?

latronamarran

1) are there any custom codes on "onopenpage" or "onaftergetrecord" triggers of item ledger entries page?
2) are there any custom flowfield fields in item ledger entry table
3) did you try reindexing the value entry and item ledger entry tables

logger

Hi @tanjenny ,
I've faced with performance issue when table contained too much FlowFields. System wastes too much time to calculate them when user opened table. Is not it your case?

TrippyZ

How many records and what is the sort key being used for display?

tanjenny

there are 1.7 m records in total

tanjenny

i am not sure what is the sort key used for the display. for an item with 3,400 records, it takes 1 minute to display a page of 18 records and it is like waiting forever to display all the 3,400 records

latronamarran

1) are there any custom codes on "onopenpage" or "onaftergetrecord" triggers of item ledger entries page?
2) are there any custom flowfield fields in item ledger entry table
3) did you try reindexing the value entry and item ledger entry tables

tanjenny

Value entry is working fine.
Have not re-indexed item ledger entry
The keys have been enabled
The following are the keys :-
Headers are:-

Key SumIndexFields MaintainSQLIndex MaintainSIFTIndex Clustered

Item No.,Entry Type,Variant Code,Drop Shipment,Location Code,Posting Date
Quantity,Invoiced Quantity Yes Yes No

Source Type,Source No.,Item No.,Variant Code,Posting Date
Quantity Yes Yes No

Item No.,Open,Variant Code,Positive,Location Code,Posting Date Quantity,Remaining Quantity Yes Yes No

Item No.,Open,Variant Code,Positive,Location Code,Posting Date,Expiration Date,Lot No.,Serial No. Quantity,Remaining Quantity Yes Yes No

Item No.,Open,Variant Code,Location Code,Item Tracking,Lot No.,Serial No. Remaining Quantity No No No

Item No. Yes Yes No
Item No.,Posting Date Yes Yes No

Country/Region Code,Entry Type,Posting Date Yes Yes No
Document No.,Document Type,Document Line No. Yes Yes No


Item No.,Applied Entry to Adjust Yes Yes No
Item No.,Positive,Location Code,Variant Code Yes Yes No
Entry Type,Nonstock,Item No.,Posting Date Yes Yes No

Item No.,Open,Variant Code,Positive,Expiration Date,Lot No.,Serial No.
No No No

Item No.,Location Code,Posting Date Yes Yes No
Location Code,Item No.,Posting Date Yes Yes No
Expiration Date,Lot No. Yes Yes No
Item No.,Expiration Date,Lot No. Yes Yes No

lubost

You missing keys:
Enabled Key SumIndexFields Clustered
Yes Entry No. Yes
Yes Item No. No
Yes Item No.,Posting Date No

If your key list is complete, your first key is primary by default and NAV adds this key at the end of each other key. "Entry No." should be your first key. The primary key must be unique and your first key isn't.

tanjenny

Yes I have those as the keys

Enabled Key SumIndexFields MaintainSQLIndex MaintainSIFTIndex Clustered
Yes Entry No. Yes Yes Yes
Yes Item No. Yes Yes No
Yes Item No.,Posting Date Yes Yes No

tanjenny

finally, the performance issue is resolved. It is because there is no key defined in valueentry for item ledger entry no for the summation of value fields like cost amount expected, cost amount actual, etc which is required when displaying item ledger entry. thanks a lot to all for your assistance.