Compile Recreating VSIFTxx Indexes

GreatScott000

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?

DenSter

Don't compile all objects at once, and only compile the ones you really need to compile.

GreatScott000

Agreed, I did this by accident but the real question is why is it doing this in the first place.

DenSter

I don't know why they made it that way, I think it's very (VERY) annoying too. It would be very nice if the compiler knew not to rebuild VSIFT when it didn't change.

David_Singleton

GreatScott000 wrote:

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?

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.

kapamarou

David Singleton wrote:

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

GreatScott000

Yes T17 is customized for our Serenic Navigator product (17 Indexes, 13 SIFT views).

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).

DenSter

GreatScott000 wrote:

why, when the table has not changed, NAV is recreating SIFT indexes when compiling

Unless someone in the NAV team is willing to share the reason behind this decision (and I am sure that there is a reason for it), we'd be speculating as to why.

David_Singleton

DenSter wrote:

GreatScott000 wrote:

why, when the table has not changed, NAV is recreating SIFT indexes when compiling

Unless someone in the NAV team is willing to share the reason behind this decision (and I am sure that there is a reason for it), we'd be speculating as to why.

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).

David_Singleton

GreatScott000 wrote:

Yes T17 is customized for our Serenic Navigator product (17 Indexes, 13 SIFT views).

...

I know there are performance "issues" at the client site and we are addressing these.
...

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.

David_Singleton

GreatScott000 wrote:

I did not mean to imply T355 had SIFT indexes, I was trying to give an impression of the size.

Sorry. On rereading your post I see I misunderstood. #-o

GreatScott000

Thank you for the responses and the suggestions however the current problem remains.

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.

David_Singleton

GreatScott000 wrote:

Thank you for the responses and the suggestions however the current problem remains.

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.

DenSter

GreatScott000 wrote:

Is there a way to stop this? If so, how? That is what I need answered.

It is just the way that it works. There is no way to change what happens when objects are compiled.

David_Singleton

Ah compiled sorry thinking import.

So as Denster says, NOPE can't be done.

GreatScott000

As noted earlier in my testing I can not get this to happen under SQL2008 only 2005.

Is this a SQL specific issue?

DenSter

I don't think the SQL Server version has any effect on what happens when NAV objects are compiled. Perhaps 2008 is faster than 2005.

GreatScott000

Per Microsoft this is a problem with NAV 5.0 SP1 builds prior to 27368.

See for details https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?WTNTZSMNWUKNTMMYXUPYZQPOUXNXSPSYOPPPXKNYWRNOUVYUKMPTYXYNQZPXKOLZ

DenSter

Well what do you know, never knew that it is a "bug" that was "fixed". Thanks for the link, will be getting that hotfix shortly :thumbsup:

matteo_montanari

GreatScott000 wrote:

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?

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