Deleting Zero SIFT Records

headley27headley27 Member Posts: 235
edited 2008-04-08 in SQL Performance
Hello,

I have spent some time optimizing my Navision Database (NAV 5.0 on SQL2000) and have had quite a bit of success doing so.

However, I am a little confused about what should be done with respect to SIFT maintenance.

I have looked at the SQL Server Stored Procedure "ssi_delzerosift" provided by STRYK System Improvement as well as a SQL Server Stored Procedure provided by PaddyMullaney (modified by ara3n and Waldo) called "pDeleteZeroSiftValues".
http://www.mibuso.com/forum/viewtopic.php?t=16985
http://www.mibuso.com/forum/viewtopic.php?t=17035

My concern is this...how will NAV 5.0 handle these 'missing' SIFT records?

I understand that the built-in optimize function performs SIFT maintenance and I believe that it is deleting zero SIFT records as well.
I tried this only to find that it causes certain flowfields to total incorrectly.
I then had to immediately rebuild the SIFT tables by deselecting the MaintainSIFTIndex checkbox on all my keys where an associated SIFT table existed, saving my changes.
Immediately following, I turned the checkboxes back on, effectively rebuilding 'corrected' SIFT tables after saving the table changes a second time.

It seems that this is a known bug from 4.0 SP3. :bug:
http://www.mibuso.com/forum/viewtopic.php?t=19387

So with all this in mind...won't these two stored procedures cause the same problem?

Although deleting the zero SIFT records is recommended by some forum users that I feel know a great deal about Navision, the posts that introduce the SQL Server Stored Procedures are older than the one that explains the bug.

Do I have cause to be concerned?
I might be totally be off-base here and I might be confusing two different things, but I want to be sure because I certainly don't want to rebuild all my SIFT tables again, should something go wrong.

Any feedback would be greatly appreciated.

Thank you,
headley27

Comments

  • David_SingletonDavid_Singleton Member Posts: 5,479
    Have you applied the somewhat (in)famous Update 6?
    David Singleton
  • DenSterDenSter Member Posts: 8,304
    The issue is that sift buckets don't total correctly (in certain fairly rare cases) when the empty sift records are deleted, regardless of how they were deleted. I am not familiar with the Stryk toolset, but do work with another one. We recommend not deleting the zero SIFT records until you have update 6 applied. Not by running any stored procedures or by doing a regular NAV table optimize.
  • headley27headley27 Member Posts: 235
    Perfect. Thanks guys, that's what I was afraid of. Good to know.

    2 more questions then:

    1) I was under the impression that update 6 was for version 4.x and I am using version 5.0. Can you please clarify if update 6 is what I need?

    2) How do I apply if update 6 is what I require?

    Thanks again.
    headley27
  • DenSterDenSter Member Posts: 8,304
    'update 6' is for 4.0 SP3 specifically, and I believe there is even an update 8 to fix a bug in update 6.

    The SIFT bug was fixed for 5.0 in update 1 I think, check with your partner.
  • garakgarak Member Posts: 3,263
    Yes for HF 6 is an another HF to fix a bug in HF 6.

    Take a look here:
    http://www.mibuso.com/forum/viewtopic.p ... highlight=
    Do you make it right, it works too!
  • headley27headley27 Member Posts: 235
    Thanks guys. I appreciate the help.
  • David_SingletonDavid_Singleton Member Posts: 5,479
    headley27 wrote:
    ...
    It seems that this is a known bug from 4.0 SP3. :bug:
    http://www.mibuso.com/forum/viewtopic.php?t=19387

    So with all this in mind...won't these two stored procedures cause the same problem?
    ...

    FYI, I was replying to this comment when I mentioned Update 6.
    David Singleton
  • EugeneEugene Member Posts: 309
    yep it was very unfortunate that this bug after being fixed in v4 was reintroduced in v5 and had to be patched again
  • SavatageSavatage Member Posts: 7,142
    DenSter wrote:
    I believe there is even an update 8 to fix a bug in update 6.

    That stuff always cracks me up :lol:
  • garakgarak Member Posts: 3,263
    Savatage: Accept my deep sympathy. [-o<
    :P
    Do you make it right, it works too!
  • DenSterDenSter Member Posts: 8,304
    Eugene wrote:
    yep it was very unfortunate that this bug after being fixed in v4 was reintroduced in v5 and had to be patched again
    Actually, they didn't discover the bug in 4.0 SP3 until after 5.0 was released.
  • krikikriki Member, Moderator Posts: 9,086
    [Topic moved from Navision forum to SQL Performance forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


Sign In or Register to comment.