Deleteing keys from Item Ledger entry table

kamalbaaklini

Need assurance:

I found out that in my Item ledger entry (60Million records) there are keys that can be deleted.

example 1 : I have key for [Item No.] and another Key for [Item No.], [Posting Date]
is it ok to delete the first one as the second key already include [Item No.] at the beginning.

I have other similar cases with keys of many fields, and having SumIndexFields
example 2:
KEY x: Item No.,Open,Variant Code,Positive,Location Code,Posting Date
KEY y:Item No.,Open,Variant Code,Positive,Location Code,Posting Date,Expiration Date,Lot No.,Serial No.
both keys have same SumIndexFields (Quantity, Remaining Quantity)

can I delete the first one (Key x)

Waiting your replies

:shock:

Marije_Brummel

Have you looked at these MSDN articles? That should get you started.

http://msdn.microsoft.com/en-us/library/dd355336.aspx

For more specific questions you can go back to us.

kamalbaaklini

Mark Brummel wrote:

Have you looked at these MSDN articles? That should get you started.

http://msdn.microsoft.com/en-us/library/dd355336.aspx

For more specific questions you can go back to us.

That is something different. and I will work on it as well, to enhance performance.
but that doesn't directly answer my questions.

Marije_Brummel

I was hoping you'ld show enough initiative to click around and learn.

How about this

http://msdn.microsoft.com/en-us/library/dd338955.aspx

There is so much info out there about keys and performance.

kamalbaaklini

Mark Brummel wrote:

I was hoping you'ld show enough initiative to click around and learn.

How about this

http://msdn.microsoft.com/en-us/library/dd338955.aspx

There is so much info out there about keys and performance.

these links are all about performance. that's not my Subject.

I need to be sure if these keys are considered duplicates. the lesser field can be removed or not considering that they have same sequence of fields.
[-o<

Kishorm

You have to remember that secondary keys automatically have all fields from the primary key include, so in your example 1 the real index created in SQL would be "Item No.","Entry No." and "Item No.","Posting Date","Entry No." For the 2 NAV keys. Therefore, assume you have a report which has the sorting set to "Item No."...

a) with both keys enabled the entries would sorted by Item No. first followed by Entry No. for entries with the same Item No.

b) with the first key deleted/disabled, the report would still work but this time the entries would be sorted by Item. No., then Posting Date (for entries with the same Item No.) and then by Entry No. (for entries with the same Item No. and Posting Date)

...now if this change in the sorting is not important then you can safely disable the 1st key. If however the sorting is important then you can set the MaintainSQLIndex to false on the first key - SQL will then not create an index for this but you can still sort in that order (SQL will generally use the index for the 2nd NAV key and then sort the results before passing to NAV)

Hope that helps.

vaprog

You can safely remove the SumIndexFields on the first key in your second example above. You might have to adjust the SIFTLevelsToMaintain property if it had been tweaked before.

NAV uses any key that contains all fields on which you placed a filter, in no particular order, to determine the value of a FlowField or to calculate a CALCSUMS.

bbrown

vaprog wrote:

...You might have to adjust the SIFTLevelsToMaintain property if it had been tweaked before...

SIFTLevelsToMaintain does not exist in newer versions since the change to SQL Indexed Views.

rmv_RU

kamalbaaklini wrote:

Need assurance:
example 1 : I have key for [Item No.] and another Key for [Item No.], [Posting Date]
is it ok to delete the first one as the second key already include [Item No.] at the beginning.

It depends on data. I think first key can be deleted.

kamalbaaklini wrote:

example 2:
KEY x: Item No.,Open,Variant Code,Positive,Location Code,Posting Date
KEY y:Item No.,Open,Variant Code,Positive,Location Code,Posting Date,Expiration Date,Lot No.,Serial No.
both keys have same SumIndexFields (Quantity, Remaining Quantity)
can I delete the first one (Key x)

It depends on data. It must be deleted if you don't use Lot No. and Serial No. functionality, another case you should check.
I try to explain my position. There are big difference between sift tables in Nav version early than 5.0 - you can manipulate balance between writing speed and calcsums speed by using sift levels to maintain, and indexed views after 5.0 - you can't do it, indexed view always contain maximum data slice. Lets have a look into second example - for Nav 3.7 you can disable the first key and maintain Item No.,Open,Variant Code,Positive,Location Code,Posting Date level index in second key - it will greatly speed up posting and never cause problem with calcsums, but in Nav 6.0 it may cause problem with Item remains calculation if you use lot no. or serial no. tracking.
So, for versions early than 5.0 i prefer use long key and play with sift levels to speed up writing , for other version i have to divide long keys wherever possible to keep balance between writing and calcsums calculation.

kamalbaaklini

rmv_RU wrote:

kamalbaaklini wrote:

Need assurance:
example 1 : I have key for [Item No.] and another Key for [Item No.], [Posting Date]
is it ok to delete the first one as the second key already include [Item No.] at the beginning.

It depends on data. I think first key can be deleted.

kamalbaaklini wrote:

example 2:
KEY x: Item No.,Open,Variant Code,Positive,Location Code,Posting Date
KEY y:Item No.,Open,Variant Code,Positive,Location Code,Posting Date,Expiration Date,Lot No.,Serial No.
both keys have same SumIndexFields (Quantity, Remaining Quantity)
can I delete the first one (Key x)

It depends on data. It must be deleted if you don't use Lot No. and Serial No. functionality, another case you should check.
I try to explain my position. There are big difference between sift tables in Nav version early than 5.0 - you can manipulate balance between writing speed and calcsums speed by using sift levels to maintain, and indexed views after 5.0 - you can't do it, indexed view always contain maximum data slice. Lets have a look into second example - for Nav 3.7 you can disable the first key and maintain Item No.,Open,Variant Code,Positive,Location Code,Posting Date level index in second key - it will greatly speed up posting and never cause problem with calcsums, but in Nav 6.0 it may cause problem with Item remains calculation if you use lot no. or serial no. tracking.
So, for versions early than 5.0 i prefer use long key and play with sift levels to speed up writing , for other version i have to divide long keys wherever possible to keep balance between writing and calcsums calculation.

thank you for the replies.

so i will disable the first key and see what will happen.
i should have faster performance and decrease in tables size (currenlty the ILE table size is 67.4GB)