SQL Query optimizer

lyot

Hi,

I had a question about the query optimizer used when NAV works with SQL server.
As I understand it isn't required that one uses the SETCURRENTKEY command in NAV anymore, since the query optimizer will deceide which is the most effeient key. One could think that this picking the most efficient key by the query optimizer takes some time (how short it may be).
So if I deceide to use the SETCURRENTKEY command in NAV, will the query optimizer still search for the most efficient key? Or will that part of query optimization be skipped? :-k

Thanx

azerty74

As I understand, if you use the SETCURRENTKEY then you force Sql Server to use that key, because in the query sent to Sql an index hint will be added.

Sometimes this produces better results, sometimes not. It depends on how uptodate your statistics are, if your indexes are fragmented, if you have wrong or too few or too many indexes,...

But, the time Sql Server needs to decide which index it should use is negligible. If it is not then your Sql Server requires maintenance or you should check your table design.

garak

The NAV command "setcurrentkey" is on sql only for "order by" clause.
If the setcurrentkey command is not set, the sql server use the PK for "order by". The query optimizer use his statistic informations for finding the result set with (in his meaning) the "best" index.

But if you have enabled in the table "$ndo$dbconfig" for example the config
"IndexHint=Yes;Company="YourCompanyName";Table="Customer";Key=Search Name;Search Method=;Index=1" the sql server use the index 1 ("Search Name" if u use a setcurrentkey command in NAV (dependent on "Search Method"). <- But for this, u must knwo what u do

(http://www.navisionguider.dk/downloads/ ... TGuide.pdf)

Regards

lyot

Thank you