mibuso.com
Home› SQL Performance
Options

MaintainSQLIndex

AngeloAngelo Member Posts: 180
edited 2008-06-11 in SQL Performance
Base on Resource Kit I read "dont maintain indexes that are only for sorting purpose because sql server just need the indexes for data-retrieval".

rec.setcurrentkey(A,B,C);
rec.setrange(A,1);
rec.setrange(B,2);
rec.setrange(C,3);
if rec.findset then repeat until....

for this key(A,B,C), I set MaintainSQLindex = No. from the statement above,if I do the right thing? and make system better performance?

rgds,
Angelo

Comments

  • Options
    kinekine Member Posts: 12,562
    It depends on which from the fields A B and C has best selectivity, if there is another key beginning with that field and if the Index Hinting is disabled. SQL will use the index which will lead to fastest retrieval of the data, based on statistics etc. and this is the main core of the optimal DB...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Options
    AngeloAngelo Member Posts: 180
    Ok, if we see at table Warehouse Entry. Key : Location Code has property MaintainSQLIndex = No and MaintainSIFTIndex = No in NAV5 SP1. this key is used in table 14 and report 5756(using development toolkit). why MaintainSQLIndex is set False?

    Base on Resource Kit, "Dont maintain SIFT indexes on small tables or on temporary tables". This is mean we have to maintain SIFT index in Ledger table because warehouse entry is not small table.Why MaintainSIFTIndex also FALSE?

    Please advice....

    rgds,
    Angelo
  • Options
    kinekine Member Posts: 12,562
    Because all depends on the usage. If the sums using this index are not calculated often or there is another index with nearly same fields and SumIndexFields, you do not need to maintain the sums twice... than you can disable one on SQL to save some time when modifying the records. For better understand I recommend to read and watch some materials about NAV and MS SQL, performance optimalization etc.
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Options
    davmac1davmac1 Member Posts: 1,283
    I have been following this blog about SQLindex and I finally got my question answered - "sort of".
    The main problem was index hints. The SQLindex may work fine - they just don't know for sure because they dropped index hints and all SQLindex definitions at the same time.
    Read the blog and then the comment chain.

    http://blogs.msdn.com/microsoft_dynamic ... x#comments


    My understanding of the order by clause in SQL Server 2005 is that by itself, it won't force an index scan if it does not match an existing index.
    David Machanick
    http://mibuso.com/blogs/davidmachanick/
Sign In or Register to comment.