Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.5K Microsoft Dynamics NAV
- 18.5K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 264 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 992 SQL General
- 384 SQL Performance
- 33 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 259 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Options
Setcurrentkey
cnicola
Member Posts: 181
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 ](*,)
"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 ](*,)
Apathy is on the rise but nobody seems to care.
0
Comments
My Blog - nav.education
MVP - Dynamics NAV
My BLOG
NAVERTICA a.s.
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.
MVP - Dynamics NAV
My BLOG
NAVERTICA a.s.
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
RIS Plus, LLC
MVP - Business Apps
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
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.
RIS Plus, LLC
MVP - Business Apps
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)!
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
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:
Peter
RIS Plus, LLC
MVP - Business Apps
Peter
Eric Wauters
MVP - Microsoft Dynamics NAV
My blog
RIS Plus, LLC
MVP - Business Apps
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.
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
Any, like they say in Belgium: "Exceptions confirm the rules" (in Dutch it sounds better though
Eric Wauters
MVP - Microsoft Dynamics NAV
My blog
And I always add: "But the exceptions shouldn't be the rule!" (and again : in Dutch it sounds better).
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
And in Flemish, everything sounds better ... .
Eric Wauters
MVP - Microsoft Dynamics NAV
My blog
The English phrase is 'the exception breaks the rule'
RIS Plus, LLC
MVP - Business Apps
Eric Wauters
MVP - Microsoft Dynamics NAV
My blog
Peter