Bad primary key selectivity

DeSp

In the Sales Line table we have a primary key ("Document Type","Document No.","Line No.") which is unique and clustered. It seems to me that the "Document Type" field in this key has a bad data selectivity. I want to enhance the performance using another secondary key like "Document No.","Document Type","Line No.". Will it be better to use this key instead of primary key?

Thanks in advance.

Slawek_Guzek

I don't think it is very good idea. First I don't think that changing field order will help - the key consists of three fields and changing order will not improve selectivity.

Second - even if you do this you'll have to go through all code in tabels, codeunits, reports. etc, find all record variables based on table 37, find all gets(...) which relate to them, and change field order in get call parameters.

If you even do this, using for example Developer Toolkit, which helps you in finding all variable records based on table 37, you will not be able perform any upgrade, apply any updates, because you'll have to do this job on every new object added to database.

I wouldn't do that.

Regards,
Slawek

DeSp

I don't want to replace or redesign the existing primary key - I just want to use another secondary key via SETCURRENTKEY() statement directly in code.

Slawek_Guzek

Get always uses primary key. If you want to use non-primary key you'll have to use find. So, instead of use salesLine.get(DocType, DocNo,LineNo) you'll have to write:

SalesLine.SETCURRENTKEY("Document No.","Document Type","Line no.");
SalesLine.setfilter("Document Type",DocType);
SalesLine.setfilter("Document No.", DocNo);
SalesLine.Setrange("Line no.", lineNo);
SalesLine.find('-');

I'm not sure if it will be fasetr than simple SalesLine.get(..)

In my opinion your new key would be usable only if you want to browse through sales lines sorting them by document no., regardless of document type. I can't see what it could be good for, but of course you may have such a need.

Regards,
Slawek

DeSp

The main question is: what will be faster - the clustered primary key which has the first field with a bad selectivity or the secondary nonclustered key with a good selectivity in the first field?

kine

1] Which version you are using? 4.00, 4.00SP1, 4.00SP2?
2] For each version is different answer... :-)

bbrown

Which database?

David_Cox

The "Document No." Key would be faster to filter on a given Document Number, If you did not have all the Information, Lets say you did not have the "Line No.".

Navision appends the primary key fields after any extended keys, so I would Not use the primary key fields in this Instance, as the new key will bring the fastest result.

So if your New key was just "Document No." you would still be able to use the primary fields in your selection construction.
SalesLine.SETCURRENTKEY("Document No.");
SalesLine.SETRANGE("Document No.", DocNo);
SalesLine.Setrange("Document type", DocType);
IF SalesLine.find('-')THEN;

Navision will use the Fields from the Extended Key first, then the primary key fields, in Order.
So the First Line of the selection will be found.

So if your New key was just "Type","No." you would still be able to use the primary fields in your selection construction.
SalesLine.SETCURRENTKEY(Type,"No.");
SalesLine.Setrange(type,SalesLine.Type::Item);
SalesLine.Setrange("No.",Item."No.");
SalesLine.SETRANGE("Document type",SalesLine."Document Type"::Order)
IF SalesLine.find('-')THEN;

Navision will use the Fields from the Extended Key first, then the primary key fields, in Order.
So the First "Order" Sales Line with the selected "Item" will be found.

But if you have the 3 primary values, by far the quickest is:
IF SalesLine.GET(DocType,DocNo,LineNo)THEN;

DeSp

Thanks for your answers.

We use Navision SQL 3.70A. What is the answer in our case? :-)

DeSp

Thanks for your detailed answer, David.

But it looks like a Query Optimizer always picks the clustered primary key in this case:

SalesLine.SETCURRENTKEY("Document No."); 
SalesLine.SETRANGE("Document No.", DocNo); 
SalesLine.Setrange("Document type", DocType); 
IF SalesLine.find('-')THEN;

It doesn't pick the suggested secondary key ("Document No.") - so the primary key is supposed to be the fastest by the Query Optimizer. I saw this in a SQL Profiler trace.
This secondary key is picked by the Query Optimizer only in this case:

SalesLine.SETCURRENTKEY("Document No."); 
SalesLine.SETRANGE("Document No.", DocNo); 
IF SalesLine.find('-')THEN;

So where is the truth?

bbrown

Selectivity is the percentage of rows returned by a query using a given index. The higher percentage means a lower selectivity. A query is considered to have high selectivity if it returns less than 5% of the rows in the table. The query optimizer will most likely use this index. When selectivity is between 5% and 10% the query optimizer may or ay not use the index. When the selectivity is above 10% the query will rarely use the index. If there is not a better index available a table scan will be done. A table scan is the process of SQL reading every record in a table to locate the desired data. A table scan on a large table can have a serious impact on system performance

Example:

Navision uses a key (“Document Type”, “Customer No”). An index (“Document Type”, “Customer No”) will have a lower selectivity than (“Customer No.”, “Document Type”). Create a new key ((“Customer No.”, “Document Type”) and turn off the maintenance of the first index in SQL. In 4.0 you can use the SQLIndex property.

DeSp

Yes, but I can't turn off the maintenance of the primary key. The Query Optimizer every time picks it instead of the key with a better selectivity (see code example).

P.S. We use Navision 3.70A.

DenSter

SETCURRENTKEY translates into ORDER BY. It has little to do with which index SQL Server ends up using. The query optimizer will determine which index to use based on the selectivity of the fields used to filter the query.

It is important to understand the difference between Navision KEYS and SQL Server INDEXES. You NEED Navision keys to ake the application order stuff by certain fields. There is only one way that Navision will allow you to order and that is to create a key for your required sort order. If that key just happens to have a non-selective field, that simply means that SQL Server will never use the index for data retrieval, and you should turn the SQLIndex property off. If SQL Server won't use the index, you should not have to update it.

It works a little differently with primary, clusetered indexes. How exacly I don't know, but suffice to say that you need the uniqueness of the primary key constraint to identify a record. SQL Server actually uses something else to make a record unique, but we're dealing with Navision here . The onyl thing you can do is to keep the primary key in a different order, but I think that is only available since 4.0 SP1. So for the sales line table you could put the document type as the second or even the third field in the SQL Server index. In Navision you just keep the key as it is and all the code should still work. I really doubt that in that case it would make much of a difference though.

DeSp

Let's remember the "Tuning Navision for better performance" and the "Performance troubleshooting guide" documents. They say that we always must use SETCURRENTKEY() before set filters in order to retrieve filtered data efficiently and quickly. If SETCURRENTKEY() is only for sorting then these recommendations are useless, aren't they?

bbrown

The filters and sort order that you set will influence the indexes that SQL uses to retrieve the data.

DenSter

it may influence which index is used, but by no means does SETCURRENTKEY mean that SQL Server will actually use the corresponding index that belongs to the key.

Again, SETCURRENTKEY translates into ORDER BY. If SQL Server takes the sort order into account when selecting the index then yes it does have an effect, but it is not a one-to-one relationship.

The way it was explained to me is that the filters that you set have a bigger impact on which index is used than the key you select (i.e. the sort order). If you set a key with 12 fields, and you only set filters on two of them, SQL Server will select the index that has those two fields (IF it exists of course), and it will sort the resulting record set on the fly.

There's a way to see how SQL Server decides which key it uses in the Query Analyzer, but I can't remember how that works.

Marije_Brummel

Please correct me if I am wrong but when you activate the Client Monitor you can turn on some options on the 2nd tab

This shows you the exact SQL statements that Navision uses.

You can copy this statement and experiment with it using the enterprise manager to see what it does.

We used this to experiment with the FINDFIRST and FINDLAST functions.

DenSter

Correct, you can turn on the client monitor and check the results. Filter on 'SQL Statement' to see the SQL queries. You'll find that SETCURRENTKEY translates into ORDER BY. You can also turn on the SQL Profiler and see what happens there. Find out what your connection id is though and set a filter on it in your trace, or you'll get loads of other stuff in there. I wish I could remember how to visualize the index selection...

DeSp

Thanks, I just wanted to be sure that I understand it correctly.

I've turned on everything everywhere :-). I see some Client Monitor records with a column "SQL Index Conflict" filled in. What does it mean?

Marije_Brummel

I remember another discussion in this thread

http://www.mibuso.com/forum/viewtopic.p ... x+conflict

Does reading this anwer the question or do we need to further investigate?

DenSter

remember though that the client monitor sucks up resources itself too, so use it selectively. Don't just have it on for all the open Navision client sessions.

bbrown

Using a SETCURRENTKEY statement will not insure that SQL uses that index. No more than using an ORDER BY stament in Quesry Analyzer will. You can experiment with this behavior by pasting the SQL statements (from client monitor) into Query Analyzer and looking at the query plans. Make changes to see how they effect the plan.

The number of records can also effect the query plan. With a small number of records, SQL will tend to use the clustered index even if there is an index that matches your filters.

You can see this by comparing the behavior of a ledger table in a small database (say CRONUS) to a large production DB with a large number of records.

DeSp

I remember another discussion in this thread

http://www.mibuso.com/forum/viewtopic.p ... x+conflict

Does reading this anwer the question or do we need to further investigate?

Unfortunately it doesn't. That thread is addressed to another headache issue - we can't solve these deadlocks not automatically resolved which lead to a client hang.

Any hints about "SQL Index Conflict" will be appreciated.

Marije_Brummel

Have you tried to google on this keyword?

If you do this all kind of links are provided about Index Conflicts.

Maybe also MSDN has some information about this.

DeSp

I hope to find the answer here, on Mibuso