Setcurrentkey

cnicola · 2007-09-30

In the hotly debated "SP3 Update 6" topic pdj wrote:

"It also kills several of the solutions I have made the last years for customers, when the solution was made under the assumption that it was executed on SQL. I have gotten used to never use SETCURRENTKEY unless I need a special sorting order. So I have a lot of places where I set filter on i.e. Item No. on Item Ledger without setting the sort order. This code will now be index hinted to the primary key, which of course takes ages no matter how small the database is. "

This reminded me of some question I had. (BTW this is all assuming Index Hinting is not on)
After some work with SQL I began to think just like pdj that there is no need for SetCurrentkey since it would send an "Order By" that you do not really need. But a lot of times I noticed that the performance was worse than if I had used SetCurrentkey
Let's take 2 examples:

1. I am filtering on 1 Item No. and 1 Posting date on Item Ledger Entry table. The recordset is only say 20-30 records and therefore hardly any cost to sort the set by Entry No. so SQL uses key "Item No., Posting Date".
2. I am filtering on 1 Sell-to Customer No. and 1 Customer PO No. on Sales Line table. The table has say 500k - 700k records and the set returned can be at the high end 10k - 40k records. The cost of sorting that many records by primary key becomes significant now and SQL decides to do a Clustered Index Scan which is definitely slower than just using the key "Sell-to Customer No.,Customer PO No." (which it does if you do a select statement with only the filters and no sorting)

So after seeing this I decided to stick with using SetCurrentKey since to me in small recordsets I don't lose anything sorting by primary key or any other key.

The question is: do you guys still use setcurrenkey regularly or have you done away with it unless you really need the records sorted in a certain order?

P.S. Of course the situation becomes a lot more complicated when the Navision key is horrible and long and your SQL index is short and streamlined but let's stick with the simple things ](*,)

matttrax · 2007-10-01

I always use SETCURRENTKEY. Don't know if it's because I started out programming strictly for C/SIDE databases or what, but it's a good habit to get into I think.

kine · 2007-10-01

I am using it just when sort order is needed or I am using CALCSUMS. But hard to say if it is good or not... :-k

cnicola · 2007-10-01

kine wrote:

I am using it just when sort order is needed or I am using CALCSUMS. But hard to say if it is good or not... :-k

Why would you use it for Calcsums? As far as I have seen Navision searches all the SIFT tables to choose the best regardless of whether in designer it is defined next to the key you use in Setcurrentkey or not.

kine · 2007-10-01

not when CALCSUMS is used. Still the correct key must be active.

cnicola · 2007-10-02

Hi Kamil,

You are right I forgot :oops: : the system does require you to use the securrentkey with all the fields you are filtering on but the system will still use whatever SIFT is best regardless to which key is attached.

Cristi

NavStudent · 2007-10-02

they should change that property for sql. it is realy not needed.

DenSter · 2007-10-02

For CALCSUMS you need it I think to identify which SIFT table the bucket is read from.

cnicola · 2007-10-02

Hi Daniel,

I am pretty sure I tested it and I am using it: the SIFT table used is independent of the key you set in SetCurrentkey.
I am usually now defining my SQL maintained Navision keys to be as good as possible on SQL with no SIFTS and then create other keys that are not maintained on SQL on which I enable just the SIFT levels. The good thing about that is that for SIFTs a lot of times the order is pretty horrible for a SQL key so this way I don't get a bad Order By statement.
I will test it again and let you know but I recommend trying it yourself.

Regards,
Cristi Nicola

DenSter · 2007-10-02

oh I don't need to test it, you're the one with the initial issue

I don't think it makes a difference which SETCURRENTKEY is used in code, the exe figures out which one to use, and that is a big mystery to me. My point was just that the link to the SIFT table is by way of the key inside NAV. The index number of the SIFT table is in order of the keys in the table designer.

Marije_Brummel · 2007-10-02

It is no mistery. It uses the first available one with all required fields. No smart optimisation done.

kriki · 2007-10-03

matttrax wrote:

I always use SETCURRENTKEY. Don't know if it's because I started out programming strictly for C/SIDE databases or what, but it's a good habit to get into I think.

So do I. It can happen you still have to program on a Navision DB, so you better have the RESET-SETCURRENTKEY-SETRANGE (or SETFILTER) 'in your fingers'.
The same goes for FINDSET,FINDFIRST,FINDLAST. I always use these, even if I am programming on a Navision-DB.

And remember : it is possible you re-use some code on another customer (with another kind of DB)!

pdj · 2007-11-11

cnicola wrote:

In the hotly debated "SP3 Update 6" topic pdj wrote:

HEY! I didn't notice this tread till now! But thanks a lot for bringing up this subject - I got wiser reading yours and the the other replys. However; I don't agree.

I understand your argument with a big resultset - then it might be an advantage to adjust the sort order.

But in most cases I still wont do or recommend it.
By avoiding the SETCURRENKEY you get less dependent of changes in keys and indexes. You can optimize the keys and indexes without (or with less) risk of getting an error about missing key.
If you keep doing it to get your code to work on the native db as well, are you then also defining all the keys needed for native, and do the SQL optimization using MaintainSQLIndex and SQLIndex properties? Well, I dont. [-(

I always develop purely for SQL or for Native or for both, but I guess each partner should have a policy for this.

I'm sorry, but I meant it when I wrote it, but right not I'm in doubt, because I also use the new FINDFIRST/LAST/SET and COUNTAPPROX and ISEMTPY a lot more, even on native only development. So if morale is good... :oops:

DenSter · 2007-11-11

One word of caution with not using SETCURRENTKEY and index hinting: If you don't specify the key, NAV will send an ORDER BY on the primary key and SQL Server will be forced to use the primary key, and you will end up with table scans. This is one of the reasons why the default index hint does not work for everything.

pdj · 2007-11-11

DenSter wrote:

One word of caution with not using SETCURRENTKEY and index hinting:

I know. I actually thought about mentioning that my comments assumed that index hinting was disabled. But then I concluded that no live database actually have this configuration. And I can't start thinking about index hinting without my blood pressure rises, so I'll stop now... ](*,)

Waldo · 2007-11-15

Believe me, many of the new implementations worldwide will go live with index hinting enabled. Not everyone is familiar with the issue regarding index hinting and the fact that you should first test both to see what setting matches the implementation.

DenSter · 2007-11-15

pdj wrote:

...no live database actually have this configuration...

I think that most live implementations that apply update 6 will have index hinting turned on.

kriki · 2007-11-16

DenSter wrote:

pdj wrote:

...no live database actually have this configuration...

I think that most live implementations that apply update 6 will have index hinting turned on.

Well, I've been warning my colleagues to turn it off!
I think SQL does a very good job selecting the best index except in some rare cases in which indexhinting might be the last solution to solve it.

I've had a DB that was quite fast on SQL to post an invoice and put on a local DB it took ages to post an invoice because of a missing SETCURRENTKEY in the Italian localization. And SQL selected the correct index.

Waldo · 2007-11-16

... actually ... I have one customer where update 6 with index hinting really dit a VERY good job ...

Any, like they say in Belgium: "Exceptions confirm the rules" (in Dutch it sounds better though

)

kriki · 2007-11-16

Waldo wrote:

... actually ... I have one customer where update 6 with index hinting really dit a VERY good job ...

Any, like they say in Belgium: "Exceptions confirm the rules" (in Dutch it sounds better though )

Both true!

And I always add: "But the exceptions shouldn't be the rule!" (and again : in Dutch it sounds better).

Waldo · 2007-11-16

Actually, in Flemish it sounds better. Not Dutch.
And in Flemish, everything sounds better ... .

DenSter · 2007-11-16

Waldo wrote:

Any, like they say in Belgium: "Exceptions confirm the rules" (in Dutch it sounds better though )

Yes in Dutch it sounds MUCH better

The English phrase is 'the exception breaks the rule'

Waldo · 2007-11-16

Sounds more logical, doesn't it ...

pdj · 2007-11-25

kriki wrote:

I think SQL does a very good job selecting the best index except in some rare cases in which indexhinting might be the last solution to solve it.

Lige mine ord! Or does "My words exactly!" sound better? 8)

Setcurrentkey

Comments

Categories