Deleteing keys from Item Ledger entry table

kamalbaaklini
kamalbaaklini Member Posts: 37
edited 2013-10-31 in NAV/Navision Classic Client
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:

Comments

  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    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.
    Marije Brummel | Freelance Business Central Specialist
  • kamalbaaklini
    kamalbaaklini Member Posts: 37
    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
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    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.
    Marije Brummel | Freelance Business Central Specialist
  • kamalbaaklini
    kamalbaaklini Member Posts: 37
    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
    Kishorm Member Posts: 921
    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
    vaprog Member Posts: 1,166
    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
    bbrown Member Posts: 3,268
    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.
    There are no bugs - only undocumented features.
  • rmv_RU
    rmv_RU Member Posts: 119
    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.
    Looking for part-time work.
    Nav, T-SQL.
  • kamalbaaklini
    kamalbaaklini Member Posts: 37
    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)

Categories