SQL index changes in 5.0

NavStudent

Hello I was looking at the beta 5.0 and Navision has changed the sql keys.
For example in Item Ledger. They have changed the Navision key

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

To sql index

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

Since Navision calls sort by
Source Type,Source No.,Item No.,Variant Code,Posting Date

in sql statement when you use the key, does this cause SQL to do more processing to sort it by that order?

My question is basically does SQL use indexes for sorting?
And how much processing is involved in sorting.

Thank you.

Marije_Brummel

If you only need sorting for a report, and already have a selective index, you can safely create a key and leave the SQL Index of.

Please make sure your users understand that on SQL reports can be significantly slower than on C/Side and that having a Reporting database can become a fine solution.

David_Singleton

Mark Brummel wrote:

... and that having a Reporting database can become a fine solution.

I remember when one of the great selling points of Navision, was "And you don't need to have a separate reporting database with buckets".

Miklos_Hollender

I think this is related to what the SQL gurus call "selectivity". A field like source type has the same value hundreds of thousands of entries while a field source no. usually has only a few dozens or hundreds of entries.

Now I don't why is it important that the first field of the key should have high "selectivity" instead of the key itself, but it seems to be...

ara3n

To get an understanding why first field has to have high selectivity. Think about this. You have a Phone book that is sorted by Last name, then First name. So in order to find by first name, you have to find the last name , and then search through the pages for first name. If the last name was selective (unique enough) you could very fast find the page that starts with last name, and then find first name. But what if the 90 percent of the people were smith?

You would have to look through 90 percent of the book. In computer terms it means means you have to put that into cache and then look through it 90 of the record.

Miklos_Hollender

Thanks, that's interesting. So it means searching by indexes is something like a sequential lookup like we do in a phonebook and not something like the searching and sorting algorythms we learned in school? You know like the one if I remember correctly, quickfind or quicksort, to find an element in an array - if it's n elements long, look at the one at the n/2 position, then if it's too small then at the one at (n/2 +n)/2 etc. always halving the array. Such algorythms don't really care much how many fields are there in the key - I think they always take log 2 n time. Funny. Probably the main difference might be that all these algorithms worked on data in memory and the database is on a disk, with a sequential access. Hmmm.... So, I=in a sense, every database is sequential because the head reading the disk doesn't really undestand the nice tuple operations of SQL?

ara3n

In B-Tree the search time is O(log n). This is just for the first field. After finding the first field, then you need to look for second field within the leaf.
So if all the fields in the first field are the same, then you are scanning through a lot of leafs and looking at each one.

Nobody

I have done extensive testing on the selectivity and density of standard indexes in NAV on SQL. In SQL 2000 rearranging the clustered indexes for better selectivity did help, but in SQL 2005 it did not seem to make much difference at all. SQL 2005 does not seem to lean so heavily on selectivity as 2000 did. In 2005 the default order works almost just as well as the rearranged order.

The real issue now with 2005 is having dynamic fields in the clustered index. Once a record is inserted into a table with a clustered index the fields in the clustered index should not be updated. This causes serious overhead because if the data in the clustered index changes all non-clustered indexes must be updated to reflect the change. If you look at some of the manf. table clustered index you will find "Status" as the first field, this is not good as the status will change over time and each time it changes the entire clustered index must be updated and all non-clustered indexes.

"getting on my soap box"

I am not sure where the whole "limit the amount of non-clustered indexes" on a table for better performance came from but it is my opinion that this is not accurate. The loss of performance form missing one necessary non-clustered index can have serious performance impact FAR greater than the few milliseconds it take to update and maintain non-clustered indexes. The extra over head from 10 extraneous non-clustered indexes tiny compared to the overhead and performance hit you will take from missing one necessary non-clustered index.

This should be slowly becoming a non-issue as SQL 2005 has a DMV that tracks index usage stats and can tell you exactly what indexes are being used and how often.

"Done Preaching"

Marije_Brummel

You are right in the fact that SQL2005 behaves different than 2000.

The Status field in manufacturing is a real nightmare for performance tuning, been there, done that. :?

In the navision world we definately have a lot to learn on how to make our solution work for SQL

The story about selectivity is strange, because when I was at teched in barcelona last year there was a session specialy about selective indexing on SQL 2005.

DenSter

Mark Brummel wrote:

The story about selectivity is strange, because when I was at teched in barcelona last year there was a session specialy about selective indexing on SQL 2005.

Really? Did your trip to Teched Barcelona affect selectivity? You're right that IS strange..... I have to get out more

Marije_Brummel

TechEd is THE best way to learn a LOT about other MS technology than Navision.

Although Boston (not Barcelona) had a high quality NAV team I learned the most from the other sessions.

I did however got a change to learn other MS experts about NAV which was pretty cool.

David_Singleton

Mark Brummel wrote:

TechEd is THE best way to learn a LOT about other MS technology than Navision.

Although Boston (not Barcelona) had a high quality NAV team I learned the most from the other sessions.

I did however got a change to learn other MS experts about NAV which was pretty cool.

Yeah I have to agree, Tech Ed (IT Forums) was very useful, I would recommend it to those Navision experts that want to know what else is going on out there.

And Mark, this is the topic you and I discussed after one of the SQL sessions.

bbrown

In SQL, the clustered index does not need to be the primary key. It should be the most commonly used index. Also, as stated above, it is best if it uses fields that are not modified.

kriki

[Topic moved from Upcoming version NAV 5.1 forum to SQL Performance forum]

kriki

[Topic moved from Upcoming version NAV 5.1 forum to SQL General forum]

kriki

[Topic moved from Upcoming version NAV 5.1 forum to SQL Performance forum]

ara3n

kriki
You can't make your mind huh?

DenSter

He just wants to make 3000 as soon as possible

DenSter

I want to stay ahead

kriki

:oops: I got a little lost at a certain moment. Too many tabs open to move topics from-to.. :oops: