Item ledger Entries and performance

Some of my users are complaning about poor performance when pressing CTRL-F5 in an Item card (Item Ledger Entries). it sometimes cost 5-10 seconds before the list is completed. On the Navision Database Server respons is very fast. I’ve taken some steps to improve performance already:

  • Increased Navision DBMS Cache (750 Mb on a 12 Gb database (73% in use), 30 users)
  • Fast clients with 512 Mb and Gigabit NIC
  • Increased Navision client object cache to 20 Mb
  • Split up de database files on 3 hard disks (RAID1)

As far as I’m aware we have no customization in this area. I can come up with two more possible enhancements:

  • Periodically do an Optimization of Tables in Database Information
  • Replace my existing hubs for Gigabit Switches

Would these two options be appropriate for solving my problem? Or are there other things I should look in to?
Thanks for any advice on this issue!

Hi Michael

It is a bit strange that only some users experience the poor performance. The reason could be that some users have removed the flowfields from the Item Ledger Entries form, that they are looking at different items or that some users have “slow” computers.

I would try to

  1. Find out why only some users have the performance issues.
  2. Change the form property SourceTablePlacement to “First” (MBS used to recommend this for forms running of tables with many record and where it took long time to open the form).
  3. Consider changing the property “Clustered” to Yes for a key in the Item Ledger Entry table that have “Item No.” as the first field in the key.

I don’t know if Gigabit Switches will help - it is rarely the network that causes performance issues in Navision. Optimizing the tables will help but I think your poor performance is caused by other factors.

Hopes this helps

Regards

Claus

Hi Claus,

I’m afraid I cannot find this property on a key in the Item Ledger Entry Table? My Navison version is 3.70. I guess this property can only be found in 4.0.

That’s already the case. But I also have a SourceTableView of “SORTING(Entry No.) ORDER(Descending)”. Could that possibly slow down performance?

and later …

Hmm, its very important to understand that this is a customization, a HUGE one. and Yes its almost certainly (Id guess to withing 99.9999% chance) the reason for your performance issues.

I assume the users need to see the items in a certain sort order. In this case you need to crete the key in the Item Ledger Entry table.

BUT please Don’t make any changes to indexing in this table, you really need to have your NSC do it. doiing it wrongly can really screw up performance in other areas.

By the way, I am assuming you made these changes your self, and I can usnderstand why you would wnat do it. But if in fact your NSC did it for you, please send me their details, and I will send around “the boys” with baseball bats to teach them some Navision programing. [;)]

Seriously though, before you change sorting even on Forms, especially on forms that filter huge tables, please talk to your NSC first, having them bill you one hour to do this properly may have saved you days of frustration and user discontent.

-Don’t worry about clustered. It is only for SQL (and only from 4.00SP1).
-Don’t optimize the tables in which a lot of writes happen (like the entry-tables). Optimizing increases reading performance but decreases writing performance. (Optimizing on SQL is always good).
-Check if the users don’t have some filters on the form and using a bad index for the filters.
-Check if they don’t have to many flowfields to be calculated.

Hi Michael

The “Clustered” property was introduced in v. 4.0 and as David said you should not change this property unless you know exactly what you are doing. Anyway you can solve you issue without using this property.

I agree with David, the changed sorting in the form is the reason for the poor performance. The entries are filtered by “Item No.” but you are using a key without the “Item No.”. To solve the issue and still keep the current sorting you should use the following key (“Item No.”,“Entry No”). This key has to be created in the table and you will have to evaluate if the decreased performance of maintaining this key can justify the “Entry No.” sorting on the Item Ledger Entry form.

Regards

Claus

Haha! No, of course I made this change myself [:$]. That’s probably the problem of having end users without too much technical background playing around with Form & Report designer! I will keep your advice in mind, David.

Also thank you Claus and Alain for the advice! I will test the suggested key solution.

As an end user, I would give serious concideration to NOT making any changes to the Item Ledger entry table. Please contact your NSC, and have them do it properly. I know that time and money are probably an issue, but in the long term, they should be doing this. You can very easliy change somethign you shouldn’t, and cause a lot of problems. For example you could copy paste a key, and not realize that a long runnign bug in Navision (with certain key strokes) then deletes one of the keys, or you go to select a key, and accidently click on or off the key, can’t remember which one, and then you reactivate a differnt one. Or you could put the key in the worng position in the list, and then blow out the performanc eof a completely unrelated report, then you go and blame the NSC that it is too slow.

In this case I don’t think that he has made any changes to the Item Ledger Entry table as Entry No is the primary key.

David meant that when I would add the key Claus suggests (“Item No.”,“Entry No”) I would better pass it over to my NSC. I will.

Exactly, and very wise.

Other stuff you can do youself, but you really want to be carefull adding keys to any of the complex tables.

David, hope you’ll read this reply. I asked my NSC the same question I did in this forum. Here’s the answer:

Quote:
‘The poor performance when openening form 38 is because the table contains 367.000 records. Navision has to go through the entire table everytime the form is opened. Unfortunately, we can do nothing about it’
End Quote.

No, I will not tell you the name of my NSC [:@]

Could you please tell me, if I want to add the key “Item No.,Entry No.” in the Item Ledger Entry table as Claus suggests, what position/sequence would be the best? Directly as the second after “Entry No.” or simply as the last entry?

Hi Michiel

Did you tell your NCS that you had changed the key on the form? This is very important information and needed to answer your performance question.

Anyway, if you insist on creating the key your self, then place it as the last key in the table. Never add keys in-between existing keys or change existing keys unless you know what you are doing. Also don’t make this change in the live system, as the system can lock up for some time while the key is being created.

Regards

Claus

Hi Claus,

Thanks for the advice.
And yes, my NSC had the same information you have. Man, I’m happy this forum exists! [:D]

Freundliche Grüsse!

This is dissapointing in one way, but good in another. THe good part is that it menas Freelancers like my self are assured of plenty of work in the future [<:o)]

Actually the mod is not a huge thing, and claus’s instructions will get you there fine, just make sure you do it on a test system, and then import the object into live at night when no one is logged in.

But another issue. You may wonder why I didn’t just tell you how to foix this in my first post, in fact I almost did, but I thought back to this old post:

http://www.mbsonline.org/forum/topic.asp?TOPIC_ID=16933

Basically trin was askking how to do something from an endusers perspective, but somehtign that could not be done. So the correct answer was “No this can not be done you need to do some customization”. Why? Well Read David Cox’s reply, he is 110% correct. If someone posts a question in the End User threaad, we need to post End User Solutions. And I see adding a key to the Item Ledger Entry table as a Developer issue, meaning, that doing it wrongly could cause damage to the system. The danger is not really for you, but for the other “End Users” that might have the same issue, and since its inthe End User forum, they would think its a DIY issue.

This forums (made up of dynamcis users and mbsonline) is the largest Dynamics forum out there with abotu 25,000 members. But in fact last time I counted the stats, we had a ratio of 3 non members (guests) using the Search Facility for every one actual member logged in. On top of that, this thread which has 5 people active also has 137 views, so you can see that a lot of people are using this information. And one of Erik’s objectives always for this forum has been to be a learnign tool, and Dynamics Knowledge base.

So don’t hink I was being rude for not helping, I just wanted to keep seperate End User and Developer threads. Actually as moderator I should have split this thread a while a go, but now its a bot complex to do so.

Anyway, (on behalf of Erik), I’d like to say “thank’s for your kind words about this forum”.

You’re welcome.

Hi David

I have just read the post you referred to - good point.

Thanks for sharing this.

Claus

Hi Claus,

Added the key as you suggested. The users are very happy with the result!
Many thanks for the advices guys!

Time for a new solution center, to give such a generic answer and to think you are so naive that you will believe is well un-beliveable. We have a relativly small database compared to many others at about 17 gig, and we have 1,390,118 record in the item ledger entry table. And we have added keys to table, althought the keys we added are just used on our own forms or reports, we try to not change the key on a stanard Navision form or report unless there is a very good reason. Of which many times there are good reasons, becuase many reports and forms use bad keys