Is SETCURRENTKEY required/recommended when doing a FINDFIRST or ISEMPTY

rsaritzky

Hi,

I have some code that checks for a duplicate value in a table:

MyTable.SETRANGE("Parent Id",ParentTankStructure.Id);
MyTable.SETRANGE("Tier Code",NewTierCode);
MyTable.SETRANGE(Code,NewCode);
IF NOT MyTable.ISEMPTY THEN
  ERROR(DuplicateTierCodeErr,NewTierCode,NewCode);

The table has a key defined for "Parent ID","Tier Code","Code". Do I have to, or from a performance standpoint is it recommended to add a line to the above code at the beginning?:

MyTable.SETCURRENTKEY(“Parent Id”,”Tier Code”,Code);

I seem to recall that if NAV finds an appropriate key for a query like this, it will use it automatically without my having to specify it.

We're on NAV2016.

Thanks,

Ron

lynge

If you don't need records in a specific order and you off cause don't in your example, they i don't think you should set it by default.

It is actually not NAV that selects the appropriate "key" (to my knowledge) but the Query optimizer of the SQL server. It will try to determine the most efficient execution plan for the query based on quite advanced parameters like active "keys", statistics of the actual data in the database etc.

IF you ever have a query that runs slower than expected, you could go to the SSMS and look at the long running queries. That can give you inspiration for creating new keys in NAV (but please DO NOT just create everything it suggests as every key also have a cost when inserting/updating/renaming records. So this requires some knowledge/training/experience).
Also in these cases you can experiment with using the SETCURRENTKEY as i think NAV might send it to the SQL Server as an index hint (something the sql server query optimizer also takes into consideration when calculating the best execution plan).

Warning: If you add a SETCURRENTKEY and the sql server query optimizer chooses to use a execution plan using another key for some reason, you will then force you SQL server to "waste" time in sorting the result. If you don't need the result in a particular order, that will just slow you queries down.

So my recommendation would be to avoid using SETCURRENTKEY unless you know what you are doing and have a reason for it (like if you expect a FINDSET to return the rows in the specified order because your code depends on it).

DenSter

The word 'key' means different things in different situations. In the context of SETCURRENTKEY, it means 'sort order'. SQL Server uses something called 'index'. The 'key' in the NAV object corresponds to the index that is created on SQL Server (according to the index properties in the table object of course).

SETCURRENTKEY sets the 'sort order'. SQL Server will use the sort order as one of the parameters when it decides which 'index' to use for retrieving the records. It will select the 'index' regardless of what you specify in SETCURRENTKEY as the 'sort order'. It is totally possible to specify one 'key' for sorting, and then SQL Server uses another 'index' for its execution plan.

If you don't specify the key, the returned record set will be sorted using the primary key. Now, in your example, because you are specifying filters for those fields, SQL Server might use that index to get the records, but it will sort them by PK anyway.

In other words: if the sort order matters, you have to use SETCURRENTKEY.

More important for performance is the isolation level of the query. You need to specify in the FINDSET command whether you intend to update records or update key. If you don't then SQL Server may return the records with read-only rights, and when you issue a MODIFY it will have to then get the records again with proper update rights to make that possible.