Deleting Zero SIFT Records

headley27

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

David_Singleton

Have you applied the somewhat (in)famous Update 6?

DenSter

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.

headley27

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

DenSter

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

garak

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=

headley27

Thanks guys. I appreciate the help.

David_Singleton

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.

Eugene

yep it was very unfortunate that this bug after being fixed in v4 was reintroduced in v5 and had to be patched again

Savatage

DenSter wrote:

I believe there is even an update 8 to fix a bug in update 6.

That stuff always cracks me up

garak

Savatage: Accept my deep sympathy. [-o<
:P

DenSter

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.

kriki

[Topic moved from Navision forum to SQL Performance forum]