1,303
NAV 2013 R2 using the current Cumulative update. I have a very slow custom process I'm running off the Sales Line table and SQL Profiler is recommending that I 'include' a field on one index and create an ascending index on another table.
Is there a way to do this with NAV or do I need to manage within SQL?
*This post is locked for comments
I completely agree with Erik and Daniele.
All keys are both ascending and descending, it is just a matter of deciding the reading order of if. NAV does not support mixing ascending and descending within the same key (index).
If the suggestion from the SQL Profiler is to add a field at the end of the index, you can just do that. And add the new key on the other table.
Daniele is correct, that in some cases you have to use SETCURRENTKEY to force NAV to give the correct index hint to SQL otherwise the profiler will just keep suggestion to do what you have already done.
Daniele,
I appreciate the response, however, I don't think it's what I'm looking for. I realize that you can use a NAV index in ascending order, but SQL Profiler was looking for an actual index in ascending order in order to optimize. I do not see a way to accomplish this in NAV.
Also, I do not mean to include a field in the index key, rather it needs to be 'included' in the dataset results of the index. It's an SQL convention which in this case improved performance by 57%. Again, I do not see a method to 'include' fields not in the index key.
Best Regads,
Dan
You need to create a new key on the table.
In the NAV dev. environment open the object explorer -> tables -> view -> keys -> insert a new line with the necessary fields.
The in your code add a SETCURRENTKEY statement to select the key created.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,269
Super User 2024 Season 2
#2
Martin Dráb
230,198
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (