Rule-of-thumb to choose the best key

Andwian

Dear Experts,

I want to filter the Customer Ledger Entries by:
Customer No.
Due Date
Open

Questions:

1. Should I create a new key in table Cust. Ledger Entries? And should it include all the filter fields? 2. Is the order does matter? 3. If I just want to use the available keys, which one is the best from these? Is there any rule-of-thumb to choose which key that most fit? a. Customer No.,Open,Positive,Due Date,Currency Code b. Open,Due Date c. Customer No.,Open,Positive,Calculate Interest,Due Date d. Customer No.,Open,Global Dimension 1 Code,Global Dimension 2 Code,Positive,Due Date,Currency Code e. Customer No.,Applies-to ID,Open,Positive,Due Date

Any comments are appreciated
Thank you.

kriki

0) there is no real "Rule-of-thumb to choose the best key". Every case is different. It depends on the data in the table, the data you want,...
1) that could be an option, but that table is already overindexed, so adding a new one is not a really good idea
2) DEFINITELY : you should put the most selective field in the beginning:
Example:
If you have a table with 1.000.000 records and need to filter on 2 fields (lets say the filter is "Field x" = Some value and NOT some range of values)
If the filter on "field X" returns only 10 records, you have a very selective field
If the fitler on "Field Y" returns 500.000 records, you have a very unselective field
=> index="field X","Field Y".
3) I would opt for "c. Customer No.,Open,Positive,Calculate Interest,Due Date"
It has Customer no. (I suppose you want to filter only 1 customer per request)
And you also have Open and "Due Date" in it. So SQL server can just read the index to know the records it needs (it still needs to do bookmark lookups on the table to get the remaining fields, but that is normal in this case) (BTW: do regular index rebuilds in SQL!).

If you tell me you have 1.000.000 records in your table and only 100 of them are "Open"=true and you need those, you can also use that index.

BTW: Selecting the best key is more a problem for the native DB. It is less a problem for SQL server if you do regular index rebuilds. But it is always best to have a SETCURRENTKEY that comes as close as possible to what you think is the best index.

Andwian

Thank you for your reply.

kriki wrote:

1) that could be an option, but that table is already overindexed, so adding a new one is not a really good idea

How do we know that it has overindexed? Is it because there are many keys in that table?

kriki wrote:

If the filter on "field X" returns only 10 records, you have a very selective field

Selective or not selective is depend on the ratio, right? Let's say 10 records out of 1 million records.

kriki wrote:

I would opt for "c. Customer No.,Open,Positive,Calculate Interest,Due Date"
It has Customer no. (I suppose you want to filter only 1 customer per request)
And you also have Open and "Due Date" in it.

Why don't choose (a)?

kriki wrote:

(BTW: do regular index rebuilds in SQL!).

Is it to keep the best performance when filtering records?

If we then use key "c. Customer No.,Open,Positive,Calculate Interest,Due Date", so the best practice is to do the SETRANGE/SETFILTER using the order of this key, i.e:

SETRANGE(Customer No.);
SETRANGE(Open);
SETRANGE(Due Date);

Please CMIIW.
Thank you.

kriki

Andwian wrote:

Thank you for your reply.

kriki wrote:

1) that could be an option, but that table is already overindexed, so adding a new one is not a really good idea

How do we know that it has overindexed? Is it because there are many keys in that table?

Yes.

Andwian wrote:

kriki wrote:

If the filter on "field X" returns only 10 records, you have a very selective field

Selective or not selective is depend on the ratio, right? Let's say 10 records out of 1 million records.

Correct

Andwian wrote:

kriki wrote:

I would opt for "c. Customer No.,Open,Positive,Calculate Interest,Due Date"
It has Customer no. (I suppose you want to filter only 1 customer per request)
And you also have Open and "Due Date" in it.

Why don't choose (a)?

True. That one escaped me.

Andwian wrote:

kriki wrote:

(BTW: do regular index rebuilds in SQL!).

Is it to keep the best performance when filtering records?

Yes, inserting/modifying/deleting records degrades the index, so you need to fix that doing a regular index rebuild.

Andwian wrote:

If we then use key "c. Customer No.,Open,Positive,Calculate Interest,Due Date", so the best practice is to do the SETRANGE/SETFILTER using the order of this key, i.e:

SETRANGE(Customer No.);
SETRANGE(Open);
SETRANGE(Due Date);

The order of the SETRANGE/SETFILTER is not important. Important is:
-using SETRANGE if possible
-using the best fitting SETCURRENTKEY

Andwian

Thank you, Kriki!