Compile Recreating VSIFTxx Indexes
GreatScott000
Member Posts: 40
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?
Anybody have any suggestions?
0
Comments
-
Don't compile all objects at once, and only compile the ones you really need to compile.0
-
Agreed, I did this by accident but the real question is why is it doing this in the first place.0
-
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.0
-
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.David Singleton0 -
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 ? :-k0 -
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).0 -
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.GreatScott000 wrote:why, when the table has not changed, NAV is recreating SIFT indexes when compiling0 -
DenSter wrote:
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.GreatScott000 wrote:why, when the table has not changed, NAV is recreating SIFT indexes when compiling
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 Singleton0 -
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 Singleton0 -
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. #-oDavid Singleton0 -
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.0 -
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.David Singleton0 -
It is just the way that it works. There is no way to change what happens when objects are compiled.GreatScott000 wrote:Is there a way to stop this? If so, how? That is what I need answered.0 -
Ah compiled sorry thinking import.
So as Denster says, NOPE can't be done.David Singleton0 -
As noted earlier in my testing I can not get this to happen under SQL2008 only 2005.
Is this a SQL specific issue?0 -
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.0
-
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?WTNTZSMNWUKNTMMYXUPYZQPOUXNXSPSYOPPPXKNYWRNOUVYUKMPTYXYNQZPXKOLZ0 -
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:0
-
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
MatteoReno Sistemi Navision Developer0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.7K Microsoft Dynamics NAV
- 18.8K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 329 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions