SQL index field order different from NAV defined field order

davmac1 · 2011-10-27

Is there a utility available that will let me compare the SQL index field order compared to the Navision defined field order?
I have come into a situation where I found the contact list was extremely slow because someone decided to change the field order of several of the indexes in SQL Server (not in the NAV key property).
I would like to be able to run a comparison with all Navision defined keys versus SQL indexes and show all indexes that have a different field order.
If I had to write this, I think I would need to have a program that read all the Navision keys - maybe from text files, and then formatted queries to read the SQL table key properties.
(In case you are wondering, I can't find anyone who knows the history of decisions at this installation.)

David_Singleton · 2011-10-27

davmac1 wrote:

Is there a utility available that will let me compare the SQL index field order compared to the Navision defined field order?
I have come into a situation where I found the contact list was extremely slow because someone decided to change the field order of several of the indexes in SQL Server (not in the NAV key property).
I would like to be able to run a comparison with all Navision defined keys versus SQL indexes and show all indexes that have a different field order.
If I had to write this, I think I would need to have a program that read all the Navision keys - maybe from text files, and then formatted queries to read the SQL table key properties.
(In case you are wondering, I can't find anyone who knows the history of decisions at this installation.)

Was this done directly on SQL or was it done using the SQL Index property in Navision?

pdj · 2011-10-27

davmac1 wrote:

I can't find anyone who knows the history of decisions at this installation.

The changes are most likely made for optimization purposes. In previous versions of NAV it used a different cursor type, which often made it a good idea to change the SQL Index without changing the key. This is not (or atleast very rarely) the case with recent versions, which might be the reason they are giving you a headache now...

DenSter · 2011-10-27

pdj wrote:

In previous versions of NAV it used a different cursor type, which often made it a good idea to change the SQL Index without changing the key.

Still a dumbass move to do that directly in SQL Server though. That's what the SQLIndex property was for.

davmac1 · 2011-10-28

The problem is dumb is done and moved on, and I am left with the results.
What we need is a Navision definition / SQL Server compare tool to show where changes have been made directly to SQL Server and thus do not show up in Navision.
Anyone know of one?

DenSter · 2011-10-28

Google search on "index columns from SQL Server".

The first link looks promising:
http://stackoverflow.com/questions/7658 ... -server-db

This one is real easy:
http://blog.sqlauthority.com/2007/12/18 ... d-columns/
Run this script in the demo database to get a full list of indexes:
sp_helpindex 'dbo.[std2009R2$Item Ledger Entry]'

Do you also need help getting key information out of NAV?

davmac1 · 2011-10-28

Thanks for the links.
I think the virtual NAV table "Key" is the best place to retrieve the NAV keys.
Do you have any other suggestions?

DenSter · 2011-10-28

That's the one

David_Singleton · 2011-10-28

David Singleton wrote:

davmac1 wrote:

I have come into a situation where I found the contact list was extremely slow because someone decided to change the field order of several of the indexes in SQL Server (not in the NAV key property).

Was this done directly on SQL or was it done using the SQL Index property in Navision?

DenSter wrote:

pdj wrote:

In previous versions of NAV it used a different cursor type, which often made it a good idea to change the SQL Index without changing the key.

Still a dumbass move to do that directly in SQL Server though. That's what the SQLIndex property was for.

We still didn't get an answer to my original question. It's quite possible that this was done using SQLIndex. Sometimes becasue the property is hidden by default it is not noticed.

pdj · 2011-10-28

DenSter wrote:

Still a dumbass move to do that directly in SQL Server though. That's what the SQLIndex property was for.

In most cases yes, and I have always done it using NAV as well. However, I can easily imagine a situation where different companies have a very different usage of NAV and therefore different optimal Index'es. This can not be done using the SQLIndex property in NAV.

But as David mention, then davmac1 still haven't answered his question...

DenSter · 2011-10-28

pdj wrote:

In most cases yes, and I have always done it using NAV as well. However, I can easily imagine a situation where different companies have a very different usage of NAV and therefore different optimal Index'es. This can not be done using the SQLIndex property in NAV.

Sorry but I don't agree with that at all. It's never good to modify NAV indexes on SQL Server, and there are no cases in which it is a good thing when the index on SQL is different from what is filtered or sorted through C/AL code. It's alright to ADD custom indexes (IF you know what you're doing) but never to change NAV indexes.

If you need different indexes in different companies, you add all of them. It's better to take the performance hit of the extra index overhead than to have a database with conflicts in the table design.

davmac1 · 2011-10-28

Sorry - I thought I was being clear:

"I have come into a situation where I found the contact list was extremely slow because someone decided to change the field order of several of the indexes in SQL Server (not in the NAV key property)."

It was not done using the SQLindex property.
Disabling, compiling, then re-enabling the keys fixed the problem.
Plus new companies added did not have this problem.

David_Singleton · 2011-10-28

davmac1 wrote:

Sorry - I thought I was being clear:

"I have come into a situation where I found the contact list was extremely slow because someone decided to change the field order of several of the indexes in SQL Server (not in the NAV key property)."

It was not done using the SQLindex property.
Disabling, compiling, then re-enabling the keys fixed the problem.
Plus new companies added did not have this problem.

Yes clear now, when you said NAV Key property, you meant SQLIndex. It makes sense now.

stryk · 2011-10-30

My two cents: I absolutely agree with Daniel that it would be stupid to change NAV "indexes" on SQL without using the "SQLIndex" property. But: with NAV 3.70 this property was not available, thus, if you had to optimize some indexes you had to do it on SQL site (in some cases). So maybe the database originates from those times?

And even though this could be considered an advertisement, well, yes - there is a tool around to compare NAV Perception with SQL Reality when it is about "Keys" and "Indexes" 8)

(And beware: there are still other tools around which screw up your indexes from outside NAV #-o )

davmac1 · 2011-10-30

You are right - it did originate in 3.7.
I am not sure why chenging the field order on keys used for lookups was considered a good idea unless they were tryingto optimize write transactions.

Maybe I can use this as a reason to recommend your performance tools.
I will get more info from you on your site.

BTW - I have your book

DenSter · 2011-10-30

stryk wrote:

But: with NAV 3.70 this property was not available, thus, if you had to optimize some indexes you had to do it on SQL site (in some cases).

Sorry but I still don't agree that changing the SQL Index directly is the only way to go in any case. If I were to want to change the SQL index in a 3.7 database, I would add 'the right one' in the NAV table designer. Not sure if the MaintainSQLIndex property was available then (I don't have 3.7 exe anymore), but you could turn that off for 'the wrong one'.

Howdy, Stranger!

Quick Links

Categories

SQL index field order different from NAV defined field order

Comments