Need to include a field in a NAV index and create an ascending index

(0) Share

Report

Posted on by Dan Eckhoff

1,305

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 have the same question (0)

All responses (4)

Answers (0)

Daniele Rebussi 2,480 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Dan Eckhoff 1,305 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Erik Hougaard 177 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Miguel Llorca Gómez 7,019 User Group Leader on at

Like (0)

Report

I completely agree with Erik and Daniele.

Was this reply helpful? Yes No