keys in Nav

Angelo

Hi master,

When I compare nav4.0 SP1 and Nav5.0 there is change in key. for example in Sales line. the primary key is "Document Type,Document No.,Line No." (nonclustered) and secondary key is "Document No.,Line No.,Document Type" (clustered)

If I want to change my key in nav4.0 SP1 same as Nav 5.0, is it true if I just add new key below the primary key (Document No.,Line No.,Document Type) ,remove clustered in primary key, and then make clustered key in my new key?

if primary key is still using "Document Type,Document No.,Line No." .why add new key make performance improve?

please advices.......

rgds,
angelo

ara3n

The selectivity of first first the in the index is higher for the index
Document No.,Line No.,Document Type



Document No is more unique than Document Type.

Angelo

yes,Doc no is more unique.

if my codes :

SalesLine.setrange(Doctype,doctype::"zzz");
SalesLine.setrange(Line No,xxxx);
SalesLine.setrange(DocNo,'yyyy');

if I'm not use setcurrentkey,nav still using primary key(Doctype,lineno,docno). So,what the function of new key (docno,lineno,doctype)? should I use setcurrentkey(docno,lineno,doctype) before setrange?

pls advice

kine

If you are using SQL, SQL will use best index regardless the SETCURRENTKEY. SETCURRENTKEY on SQL is just about sorting order, nothing about indexes.

Angelo

i'm using SQL.

Kine,

you mean if I do not put setcurrentkey before setrange, sql will automatically use secondary key(docno,lineno,doctype) although the primary key is doctype,lineno,docno ?

kine

SQL will each time select best key based on statistics and actual parameters. Sorting order can influence this selection process but not so much. All this is true if IndexHinting is disabled. SETCURRENTKEY just add "Order By" clausule to the select statement. SQL has own mechanism to select best index for reading data from DB.

Angelo

Ok, I see.

if I dont make new secondarykey like Nav5.0. Keep using standard nav4.0 sp1, using doctype,lineno,docno key but I set sqlindex to docno,lineno,doctype. the performance will be same as in Nav5.0?

kine

Problem is, that you cannot change the key field order on Clustered index through SQLIndex property and it is why they created new key and set this as clustered... (but I am not sure)...

ara3n

kine wrote:

Problem is, that you cannot change the key field order on Clustered index through SQLIndex property and it is why they created new key and set this as clustered... (but I am not sure)...

You can change the pk that is clustered and have a diff sql index.

kriki

ara3n wrote:

kine wrote:

Problem is, that you cannot change the key field order on Clustered index through SQLIndex property and it is why they created new key and set this as clustered... (but I am not sure)...

You can change the pk that is clustered and have a diff sql index.

But why didn't they just change the ordering on SQL instead of creating a new key? Now SQL has to maintain 2 keys.
I never tried to change the SQL key on the primary key because, to be honest, I am afraid it can create trouble.

DenSter

I used to set the SQLIndex of the primary key to DocNo, LineNo, DocType, and then the additional key was added as the clustered index. The person who did that says it gives better performance that way, so ever since I've been doing it that way as well.

Angelo

Ok,

Now, How to compare and see the performances?
I make some scenarios in Sales Header:
1. Using Default primary key(DocType,No), clustered = Yes
2. make new key(No,DocType) and make it clustered (like Nav5.0)
3. Using Default primary key(DocType,No),clustered = Yes, sqlindex = No,DocType

then I make query : "Select * from Sales Header" then compare CPU time, Reads, Write in SQL Profiler. Why sometime No. 1 is better than another?Why No. 2 better than another and Why also sometime No. 3 is better than another? What the exactly way to compare performance beside using SQL Profiles?is there another tool?

Pls Advices.....