Index problems - optimisation....

Hi, On a number of different versions of Navision we have now seen behaviour which seems really, really strange to me. When the database becomes a bit sluggish, we tend to optimise using the Navision client. After this sometimes certain tables run a lot faster, sometimes they run a lot slower. The sales header table is a classic example of this. Slowly in this case means it will take up to 15 seconds to open the sales order form. After running optimise again for this table, it will run very quickly again, and all will be fine. However, when it is running extremely slowly, we can set a filter (e.g. on order no’s.) to include all the data, and next time the form opens, it runs very very quickly. When removing the filter, it is slow again. Anybody else seen this behaviour anywhere? It is bizarre, because with the filter it still includes all the data, so there should be no difference in speed. We have experienced it both with version 3.01 and version 3.70A. The form does not have a key defined, so it should just use the primary key when opening. Regards Meint

What database are you using? Are there any other filters on the form? regards Anders

Hi Meint, I have the same situation for most of the clients where the first person logs in always runs everything slowly, but then speeds up a lot afterwards. I think Navision initializes everything the first time the form is open, but then again, I’m only assuming.

Are you running SQL or native?

native

SQL Server 2000 with SP2 for us, by default there are no filters on the form unless the user sets them. Meint

Meint, You may have the ‘poor performance after .fbk restore on SQL’ problem we’ve seen before. See http://www.navision.net/forum/topic.asp?TOPIC_ID=10479 Optimize may do the trick.

Yeah, optimise certainly fixes it, but it is still bizarre, we find the problem also happens when we change an object, i.e. sales header form, it almost looks as if it corrupts the key and optimising fixes it. Bit painful because we now have to check it on a periodic basis. Regards Meint

Well, it happened again this morning after loading a batch of modified objects. Sales order form took ages to open, ran optimise several times and nothing happened, re-compiled the form, ran optimise again and then it worked. While it was runinng slowly it almost seemed like there was no index there at all and it was operating on a full table scan, it was that slow. Anybody have any ideas why this could be? We are a bit baffled by this, but as said before, it also happened on our old 3.01A version as well as our current 3.70A version. Many thanks Meint