SQL tuning

illug

Hi ...

I want to start experimenting with using the SQLIndex property. But first I would like to ask if anyone knew what the results would be if for a key in Navision MaintainSQLIndex would be False but MaintainSIFTIndex would be true?

David_Singleton

Using copy paste, I could pre-post for you all the replies that you are about to get for this topic \:D/

And all though I don't know the exact answer, I am curious as to how this would be possible? I guess SQL would create the SIFT table, but would not index it?

Marije_Brummel

Why don't you test this?

I use this sometimes to eliminate dirty fields like variant code from the sift levels without chaning the NAV Key.

David,

The SQL Index and SIFT Table have nothing to do with each other.

The SQL index is just an index on the existing table, while the SIFT Table is a new table with it's own (2) indexes.

So the answer is YES, you can have a SIFT table without turning on the SQL Index.

DenSter

illug wrote:

if anyone knew what the results would be if for a key in Navision MaintainSQLIndex would be False but MaintainSIFTIndex would be true?

The Navision key would not be created as an index on the SQL Server table, and there would be a SIFT table to keep track of bucket values for the sumindexfields that are associated with the key in Navision. This is of course assuming that there are sumindexfields, and at least one SIFT level is actuvated.

David_Singleton

Mark Brummel wrote:

...
The SQL index is just an index on the existing table, while the SIFT Table is a new table with it's own (2) indexes.

So the answer is YES, you can have a SIFT table without turning on the SQL Index.

Yes that seems to make sense, thanks.

illug

Thank you for your replies.

I´ve just migrated from Native to SQL 2005 and probably have a lot of tuning to do. What I am thinking about (and the reason for my original question) doing has to do with this key in the Item Ledger Entry:

Source Type,Source No.,Item No.,Variant Code,Posting Date

Some users are using this to see item sales for specific customers. I thought it might make sense not to maintain it in SQL, and create a new key Source No.,Item No. that would be used in stead.

kriki

illug wrote:

Thank you for your replies.

I´ve just migrated from Native to SQL 2005 and probably have a lot of tuning to do. What I am thinking about (and the reason for my original question) doing has to do with this key in the Item Ledger Entry:
Source Type,Source No.,Item No.,Variant Code,Posting Date

Some users are using this to see item sales for specific customers. I thought it might make sense not to maintain it in SQL, and create a new key Source No.,Item No. that would be used in stead.

If you use 4.00SP3, you can keep the key like it is, but in the key-property SQLIndex, you can change it.
This way you don't need to create a new key in Navision.

kriki

[Topic moved from Navision forum to SQL Performance forum]