I have a client with a large database (65 Million records in T17, 136 Million in T355). They are running Windows Server 2008 x64, SQL Server 2005 x64, NAV 5.0 SP1 (Build 26084). Every time I compile objects it recreates ALL the VSFIT indexes for tables being compiled, EVEN IF the table has NOT been modified. For T17 this takes 12 hours.
Anybody have any suggestions?
0
Comments
RIS Plus, LLC
RIS Plus, LLC
Firstly there is something seriously wrong here. T355 has no SIFT fields, in fact it doesn't even have any decimal fields that you could sum on, so I would check what that's about.
Also T17 has only 5 VSIFT "tables" even with 65 million records I wouldn't expect that long, maybe one or two hours but 12 hours seems way too long.
Heavily customized ? :-k
I did not mean to imply T355 had SIFT indexes, I was trying to give an impression of the size.
I know there are performance "issues" at the client site and we are addressing these.
However none of this answers the question as to why, when the table has not changed, NAV is recreating SIFT indexes when compiling. I have tried to duplicate this in SQL 2008 and I am unable to do so. Unfortunately I can't bring the client data "in-house" to do more "testing" due to its size (it takes 3 hours to do a SQL backup).
RIS Plus, LLC
My guess is that is will be related to the step towards managed code. I think when that happens we will see a lot of changes, and they are probably moving what they can to get us used to it. (of course that's just a guess).
I'd say its time for a redesign of that then. I assume this is the manage funds. You need to think of a new table structure to separate the donor/manager to the fund from the recipient of the fund and do the many to one links with a new table that has a minimum of fields. But keep it all out of the GL. It will look like that will create more records (which it will) but they will have less sifts and be much smaller.
Until Navision gets rid of SELECT * you need to keep tables small.
Sorry. On rereading your post I see I misunderstood. #-o
NAV is recreating indexes during compile when the related table object has NOT changed.
Is there a way to stop this? If so, how? That is what I need answered.
I think on either MiBuSo or DUG, someone had a tool that reads the imported objects and then allows you to do some modifications on the list. So you could compare the objects (date, time, version, etc) and if they are the same then set to skip.
Personally I always manually do this, because I want to be sure.
I think this is the only way.
RIS Plus, LLC
So as Denster says, NOPE can't be done.
Is this a SQL specific issue?
RIS Plus, LLC
See for details https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?WTNTZSMNWUKNTMMYXUPYZQPOUXNXSPSYOPPPXKNYWRNOUVYUKMPTYXYNQZPXKOLZ
RIS Plus, LLC
Hi
You need Client Build 27368.
https://mbs.microsoft.com/knowledgebase ... -us;957733
All SIFT views of a table are rebuilt when you change the table in Microsoft Dynamics NAV 5.0 with Service Pack 1 installed
You can request this hotfix with partnersource or asking to your partner to download it for you.
Bye
Matteo