SQL tuning

illug
illug Member Posts: 21
edited 2007-04-06 in SQL Performance
Hi ...

I want to start experimenting with using the SQLIndex property. But first I would like to ask if anyone knew what the results would be if for a key in Navision MaintainSQLIndex would be False but MaintainSIFTIndex would be true?

Comments

  • David_Singleton
    David_Singleton Member Posts: 5,479
    Using copy paste, I could pre-post for you all the replies that you are about to get for this topic :D \:D/

    And all though I don't know the exact answer, I am curious as to how this would be possible? I guess SQL would create the SIFT table, but would not index it?
    David Singleton
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    Why don't you test this?

    I use this sometimes to eliminate dirty fields like variant code from the sift levels without chaning the NAV Key.

    David,

    The SQL Index and SIFT Table have nothing to do with each other.

    The SQL index is just an index on the existing table, while the SIFT Table is a new table with it's own (2) indexes.

    So the answer is YES, you can have a SIFT table without turning on the SQL Index.
    Marije Brummel | Freelance Business Central Specialist
  • DenSter
    DenSter Member Posts: 8,307
    illug wrote:
    if anyone knew what the results would be if for a key in Navision MaintainSQLIndex would be False but MaintainSIFTIndex would be true?
    The Navision key would not be created as an index on the SQL Server table, and there would be a SIFT table to keep track of bucket values for the sumindexfields that are associated with the key in Navision. This is of course assuming that there are sumindexfields, and at least one SIFT level is actuvated.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • David_Singleton
    David_Singleton Member Posts: 5,479
    Mark Brummel wrote:
    ...
    The SQL index is just an index on the existing table, while the SIFT Table is a new table with it's own (2) indexes.

    So the answer is YES, you can have a SIFT table without turning on the SQL Index.

    Yes that seems to make sense, thanks.
    David Singleton
  • illug
    illug Member Posts: 21
    Thank you for your replies.

    I´ve just migrated from Native to SQL 2005 and probably have a lot of tuning to do. What I am thinking about (and the reason for my original question) doing has to do with this key in the Item Ledger Entry:

    Source Type,Source No.,Item No.,Variant Code,Posting Date

    Some users are using this to see item sales for specific customers. I thought it might make sense not to maintain it in SQL, and create a new key Source No.,Item No. that would be used in stead.
  • kriki
    kriki Member, Moderator Posts: 9,121
    illug wrote:
    Thank you for your replies.

    I´ve just migrated from Native to SQL 2005 and probably have a lot of tuning to do. What I am thinking about (and the reason for my original question) doing has to do with this key in the Item Ledger Entry:
    Source Type,Source No.,Item No.,Variant Code,Posting Date

    Some users are using this to see item sales for specific customers. I thought it might make sense not to maintain it in SQL, and create a new key Source No.,Item No. that would be used in stead.
    If you use 4.00SP3, you can keep the key like it is, but in the key-property SQLIndex, you can change it.
    This way you don't need to create a new key in Navision.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • kriki
    kriki Member, Moderator Posts: 9,121
    [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!

Categories