mibuso.com
Home› SQL Performance
Options

Navision Keys <> SQL Index's

SteveSteve Member Posts: 81
edited 2008-09-17 in SQL Performance
Hello All,

In version 4.x we were told that creating sql index's independent from Navision. (That is we would have 5 keys in Navision that were created and managed inside Navision and those 5 plus lets say 2 others created specifically inside SQL manager.) that Navision could become "out of sync" and require an object to be removed and recreated to correct. ???

I can see how this maybe a correct statement within the Navi 4.x environment since it is maintaining the index as well as sift as separate tables.

Is this or will this be the same type of problem in Navi 5.x since sift tables are more less going away in replace of sql views?

I hope all understand.

Thanks
Steve

Comments

  • Options
    kinekine Member Posts: 12,562
    There is no change in that. Still, whole database structure is handled by NAV itself, if you make a modification from outside, it is not good for NAV (with exceptions...).
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Options
    DenSterDenSter Member Posts: 8,304
    You should never modify any indexes for any NAV tables directly on SQL Server. This is especially important for indexes that are created by the NAV executables. If you modify the index that is associated with a NAV key, you can end up with uncompilable objects.

    For NAV databases, you should always create the keys inside the NAV table designer, and manage the SQL Index by modifying the SQLIndex and MaintainSQLIndex properties of the key inside the NAV table designer. This is the best way to keep table definitions up to date, as it will always be managed by the NAV executables.
    Daniel Rimmelzwaan
    RIS Plus, LLC
    MVP - Business Apps
  • Options
    SteveSteve Member Posts: 81
    Just to toss this into the mix, but what about create index views and using included fields within sql?

    I agree and would not change a index that is directly related to a navi key, but more over questioning creating additional ones. Above and beyond the ones listed in Navi.
    Steve
  • Options
    bbrownbbrown Member Posts: 3,268
    I can't think of a situation where you would want to do this. I see plenty of situations where you may want a NAV key without an underlying SQL index (turn off MaintSQLIndex). But I don't see a situation where I would want a SQL index that NAV is not aware of.
    There are no bugs - only undocumented features.
  • Options
    SteveSteve Member Posts: 81
    Hello BBRown,

    I agree, but from a performance aspect I found increased perfromance indexing a view with included fields then just creating a Navi key and including the calculated field as a sumindex. The results were 150% faster calculations verse the standard Navi key method.

    I don't disagree with maintaining everything within Navision, but I 'm expolrering why there is such a difference? This could be as simple as our modifications, setup or the developers code within. I'm justtrying to make sense of what I have seen so far.
    Steve
  • Options
    krikikriki Member, Moderator Posts: 9,096
    [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!

  • Options
    azerty74azerty74 Member Posts: 82
    There are a number of things to keep in mind when thinking about Keys in NAV and Indexes on Sql.

    -Are they needed, or only creating overhead on IUD operations ?
    -Are the Selective enough, are the fields on the correct order ?
    -Statistics, Fragmentation,...
    -Remember the Fill Factor, espacially for HOT tables.
    -Should the "Entry No." be the clustered key in NAV Ledger tables ?
    -...
    Debugging is twice as hard as writing code. Therefore if you write the code as cleverly as possible you are by definition not smart enough to debug it.
Sign In or Register to comment.