1

I have the following questions:

  1. What does SETCURRENTKEY actually do?
  2. What is the benefit of SETCURRENTKEY?
  3. Why would I use SETCURRENTKEY?
  4. When would I use SETCURRENTKEY?
  5. What is the advantage of using an index and how do I tie this analogously to the example of an old sorting system of a library?
  6. What type of database querying efficiency problems does this function solve?

I have been searching all over the internet and the 'IT Pro Developer Help' internal Navision documentation for this poorly documented function and I cannot find a right answer to my questions.

The only thing I know is that SETCURRENTKEY sets the current key for a record variable and it sorts the recordset based on it. When SETCURRENTKEY is used with only a few keys, it can improve query performance. I have no idea what is actually happening when a database uses an index versus not using an index.

Someone told me this is how SETCURRENTKEY works:

It is like the old sorting card system in a library: without SETCURRENTKEY you would have to go through each shelf and manually filter out for the book you want. You would find a mix of random books and you would have to say: "No, not this one. Yes, this one". With SETCURRENTKEY you can have an index analogous to the old system where you would just go to a book or music CD based on its 'Author' or 'Artist' etc.

That's all fine, but I still can't properly answer my questions.

underscore_d
  • 6,309
  • 3
  • 38
  • 64
MrStack
  • 455
  • 2
  • 7
  • 22

2 Answers2

6
  1. With SETCURRENTKEY you declare the key (table index, which can consist of many fields) to be used when querying database with FINDSET/FINDFIRST/FINDLAST statements, and the order of records you will receive while iterating the recordset with NEXT statement.
  2. Performance. The database server uses the selected key (table index) to retrieve the record set. You are always better off explicitly stating SETCURRENTKEY in your code, as it makes you think along about you database structure and indices required.
  3. Performance, and so that you know ahead the order of records you will receive when iterating through a recordset.
  4. When to use:

The typical use is this:

RecordVar.SETCURRENTKEY(...)
RecordVar.SETRANGE(Field, ...)
RecordVar.SETFILTER(Field, ...)
RecordVar.SETRANGE(Field, ...)
...
IF RecordVar.FINDSET THEN REPEAT
  // do something with records
UNTIL RecordVar.NEXT = 0;

SETCURRENTKEY is declarative, and comes into effect only when FINDSET is executed. At the moment FINDSET is executed, the database will be queried on the table represented by RecordVar, using the filters declared by SETRANGE/SETFILTER, and the key/index declared by SETCURRENTKEY.

For 5. and 6. and generally, I would truly reccomend you to familiarize yourself with basic database index theory. This is what it is, pretty well explained by yourself using the library/book analogy.

Ivka
  • 321
  • 2
  • 4
  • 2
    As per the first statement above, SETCURRENTKEY(...) isn't required with a GET(...) statement. – ian_scho Sep 24 '14 at 10:30
  • 1
    True. GET() will always use the primary key, and will disregard any filters applied. – Ivka Oct 22 '14 at 08:14
  • What about if I have to edit a key-field in the loop? I get some weird results if I try this – Fabian Bigler Aug 10 '15 at 11:22
  • 5
    If using SQL Server as the database, the SETCURRENTKEY does NOT specify which index SQL Server will use, it only adds an ORDER BY to the SQL statement. The database engine may still decide to use another index. You can use the profiler to check what difference the SETCURRENTKEY has on the SQL statement. – Roeland Jan 08 '16 at 12:56
  • 1
    Also true. Even more so - recent versions of Dynamics NAV (SQL-only) will not even complain if you use SETCURRENTKEY for a key/index that does not exist. Which is convenient for one-off tasks/procedures, but having such code in production and run daily by users still asks for having those keys/indices present, for performance reasons. But be mindful here and do not create keys for each and every combination you use for sorting, as they can grow your database unnecessarily. – Ivka Jan 10 '16 at 10:51
  • Fabian, you have to be careful here. I'm trying to avoid such constructs, as I'm too lazy to risk fixing some weird behavior afterwards. Use MODIFYALL if you can get away with that, or another approach is populating a temporary table with your modified records while iterating, and afterwards 'pump' the temporary records over the actual records. With NAV, this will all happen in a single transaction by default, hence safe thing to do. You might also use MARK(TRUE) in the first loop to mark what you need to modify, then revert to primary key, filter using MARKEDONLY(TRUE) and do modification. – Ivka Jan 10 '16 at 10:59
  • @Roeland _"If using SQL Server as the database, [`SETCURRENTKEY` does nothing to benefit performance]"_ - well, given that NAV **exclusively** uses SQL Server now, the performance argument is totally obsolete and misleading. – underscore_d May 31 '18 at 19:37
0

If modifying key fields (or filtered fields, even if not in the key) in a loop, the standard way to do this in NAV is to declare a second record variable, do a GET on it using the primary key fields from the record variable you are looping through, then change and MODIFY the second record variable.

ChristopherM
  • 1