MaintainSQLIndex

Angelo

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

kine

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...

Angelo

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

kine

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.

davmac1

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.