Webcast on SQL Optimization for Ms Dynamics NAV

NavStudent · 2007-01-29

Hello anybody attended the webcast?

There was one question I had asked but couldn't understand what he said.

The question was this. Navision has standard keys on tables. There certain fields that are not used for a given company. For example Variant Code. There are keys with this field in Item ledger.

SQL has to maintain the key for this field. In 4.0 sp1 you can change the sql index.
The question is; should you move the Variant code to last field or remove it the sql index?

Also something new I learned is that it's not a good idea to change the clustered index on Ledger tables. The reason the cluster index is added to all the other indexes, causing decrease write performance.

David_Singleton · 2007-01-29

NavStudent wrote:

Hello anybody attended the webcast?

There was one question I had asked but couldn't understand what he said.

The question was this. Navision has standard keys on tables. There certain fields that are not used for a given company. For example Variant Code. There are keys with this field in Item ledger.

SQL has to maintain the key for this field. In 4.0 sp1 you can change the sql index.
The question is; should you move the Variant code to last field or remove it the sql index?

Also something new I learned is that it's not a good idea to change the clustered index on Ledger tables. The reason the cluster index is added to all the other indexes, causing decrease write performance.

Web cast?
What web cast?
Where did you find out about this?

NavStudent · 2007-01-29

here is the link

https://mbs.microsoft.com/partnersource ... QLwebcasts

I saw it from Nav screensaver.

http://www.msdynamicsscreensaver.com/xml/news.xml

The second part of it is on friday. So you can see the rest.

David_Singleton · 2007-01-30

Thanks.

NavStudent · 2007-01-30

you are welcome. Now I guess I have to wait till tomorrow for my answer.

David_Singleton · 2007-01-30

Sorry, I just read the bit about the web cast.

Personally I would remove the variant from the index. Since the field is always blank, it has zero selectability.

Navision's primary key is (in fairly loose terms) a clustered index, since it contains all the data in the record. This is why Navision have always used a sequential integer on primary keys for tables that get very big. And its why you should never be making changes that keep lots of Sales Lines in the system, that will keep re-indexing the primary key.

kine · 2007-01-30

Answer for your question was mentioned during the webcast ("if company is not using e.g. variants, it is good to remove the field from indexes")

DenSter · 2007-01-30

I am going on a long plane trip and want to download that webcast so I can watch it on the plane. Does anybody know a link to the recorded webcast?

By the way... the answer is: leave the NAV key as it is, it might be used in SETCURRENTKEY commands and as default report keys. Create a SQL Index without the Variant Code, or put the Variant Code at the end. If the Variant Code is the last field in the index, it doesn't make much of a difference in how SQL Server uses the index (it's unimportant for selectivity)

kine · 2007-01-30

The recording URL was not sent to us yet.

NavStudent · 2007-01-30

Ofcourse it's unimportant. But on write transaction it effects performance, because sql has to maintain the field in the key. That's why I asked.

DenSter · 2007-01-30

One field in one index does not affect write performance very much. It's when you have a ledger table with 20 compound indexes with 15 key fields and 10 sumindexfields each and all SIFT levels turned on when you can make a big impact.

One field in one index may save you part of a millisecond per write operation, and will only make a difference if you have extraordinary high transaction volumes.

NavStudent · 2007-01-30

He showed what optimal path sql takes for example when he runs

select * from customer Ledger entry
where "Document type" = 1

then = 2
then = 3

and sql took different indexes for each.

then he rante with select top 200.

The select top 200, is allways called always in sp 1-3 so sql will always use the clustered index and do a table scan.

I ran on sql2K5 the following code

customer.setffilter("search name",'A*')
customer.findset;

and sql used Primary key as index.

then I added setcurrentkey, and sql then used "search name" Key.

It looks like setcurrentky does change which index sql is selecting.

kine · 2007-01-30

Yes, because the result for which the Execution plan is calculated is different and the execution plan calculate with the final sorting of the result.

David_Singleton · 2007-01-30

DenSter wrote:

I am going on a long plane trip and want to download that webcast so I can watch it on the plane. Does anybody know a link to the recorded webcast?
...

It would be nice if MVPs got this sort of information, and maybe even notification BEFORE the event.

](*,) ](*,) ](*,) ](*,) ](*,) ](*,) ](*,) ](*,) ](*,)

kine · 2007-01-30

The information was sent to all partners, it is why you do not know about that (and it can be found on PartnerSource :-)), you need to do something about that to be in the queue... ;-)

DenSter · 2007-01-30

David Singleton wrote:

It would be nice if MVPs got this sort of information, and maybe even notification BEFORE the event.

Sorry David, but it is not an MVP event. I was notified through an internal email, and figured it was a company held thing. I'd have mentioned it for sure if I had known you didn't know about it.

Saint-Sage · 2007-01-30

I tried to sign up for the Friday Webcast, at about 4pm yesterday. It tried to sign me up for both sections and unfortunatly told me the webcast registration was closed.

Does anyone know if a permit is possible for the last half? Also, will they release the contents of the first webcast as a download?

Thanks!

Marije_Brummel · 2007-01-30

Hey David,

The webcast was done by our good friend Hynek, I wanted to mention it when we called last sunday, sorry for that. :oops:

He gave a terific presentation, but it was sort of the same as I saw at convergence and directions. You saw it at convergence to.

I work very closely with Hynek as a SQL Perform Consultant and I can reccomend using his tools and methodology.

About the Variant question. I believe that part of implementing NAV on SQL you should make budget for what I call a "Index Plan". It is something I have learned at TechEd by Kimberly Tripp, another great SQL expert.

Every company, especialy large ones uses NAV differently so every company needs their own index plan.

Setting up SQL correctly, maintaining the database and implementing a correct index plan usualy is enough to get Navision working like a charm om SQL. The SQL Perform tools can make this task manageable.

Sorry for possible advertising.

David_Singleton · 2007-01-30

Well my main interest was to see how it compared to Hynek's presentation, so I guess that question is answered.

Hynek_Muhlbacher · 2007-01-31

Hi Guys,
I am glad that you enjoyed the presentation and apologies if you could not hear my answer properly. Mark has kindly asked me to follow up on this. Thanks Mark for the advert and of course David for your kind comments.

In my opinion there is no reason whatsoever to index on "empty" columns, it costs an extra index key on the index resulting in less buffers being able to kept in data caches. It is not massive overhead, but why bother.

And yes David, the original Navision developers knew their stuff, all ledger entries with a narrow primary key, compared to some features added later with composite primary keys. It makes a difference on both server platforms, it is equally bad on 'native' too to use composite PKs.

Hynek.

David_Singleton · 2007-01-31

Will the web cast be down-loadable?

BTW check you signature, people wont find your site if you spell your company name wrong :oops:

Anyway your presentation at Convergence was brilliant. I seriously doubted that I could learn much in 1 hour, but I really did get value out of it.

kine · 2007-01-31

David, we have got the download access and the presentation slides yesterday evening.

And Hynek, thanks for the webcast. There were 4 of us from our company listening and refreshing the knowledge.

Waldo · 2007-01-31

David,
If you want, I will notify you on these events (Did it for Mark as well

).

Hynek,
Thanks for this presentation. I followed the one at Convergence as well, and to remind everybody, it was marked as the second best presentation of all (knowing that the general session of Bill Gates was marked as best one...). Great stuff, and yesterday's webcast was a great F5 (refresh...).

Regards

navisi0ne · 2007-01-31

recorded webcast is on partnersource:

https://mbs.microsoft.com/partnersource ... veMeetings

thaug · 2007-01-31

Could this be added as a download here? =P~ [-o<

NavStudent · 2007-01-31

Hello Hynek Muhlbacher
Thank you for your answer.

I have some more question.

It relates to how sql stores the buckets for dates. My what kind of code would you have to write in order for Navision to use the year and Month buckets to sum the quantity.

For example if you put the following code. Will Navision use the year bucket to calculate the year? or will it simply add all the day records?

ItemLedger.setcurrentkey("Item No.","Entry Type","Variant Code","Drop Shipment","Location Code","Posting Date");
ItemLedger.setrange("Posting Date",010100D,123100D);
ItemLedger.calcsums(Quantity);

So if the year and months are disabled, will navision sum the actual table or will it sum up the bucket for the day?

Marije_Brummel · 2007-01-31

Since the intention of a webcast is to get people thinking and experimenting I will answer this one with a counter question:

Have you tested this? You can turn on the profiler with the parameters Hynek explained and see exactly what SQL statements are created.

You can also use the client monitor for this.

NavStudent · 2007-01-31

I created a new table in 50000 range with line as PK. added a date field and amount field added seond key with date and amount as sum index flowfield. I disabled the year and month.

I looked at client monitor. It looks like it's running the following statment.

"mytable 5K".setcurrentkey(Date);
"mytable 5K".calcsums(Amount);

this was in client monitor.

SELECT SUM("s2") FROM "NavisionSP2$50000$0" WITH (READUNCOMMITTED)  WHERE (bucket=3)

bucket 3 is the date buck. It looks like it goes to the bucket and adds the dates. Fair enough.

I then ran the following code

"mytable 5K".setcurrentkey(Date);
"mytable 5K".setrange(Date,010107D,123107D);
"mytable 5K".calcsums(Amount);

This was in query monitor.

SELECT SUM("sum1") FROM (SELECT  SUM("s2") AS "sum1" FROM "NavisionSP2$50000$0" WITH (READUNCOMMITTED)  WHERE (bucket=? AND ((f3>=? AND f3<?))) UNION ALL SELECT  SUM("s2") AS "sum1" FROM "NavisionSP2"."dbo"."NavisionSP2$50000$0" WITH (READUNCOMMITTED

:shock:

It sucks that I can't see all the stement. But what I can figure out is that it looks like summing bucket ? which i guess is either date or year.

Hynek_Muhlbacher · 2007-01-31

Hi David,
Thanks for the spell check :oops:

Hi All,
Thanks for all the nice comments! Very kind of you.

With the buckets, play a bit more students....What have you missed? Perhaps closing date? Try 010107D..C123107D

... It is not documented very well ;-)

Mark's comment was right, play more, learn more. Come to SQL Perform workshops to play! ;-)

N.B. I think that I have done two posts so far breaking all rules not to advertise business here... Sorry, perhaps somebody ban me from here please?

Waldo · 2007-01-31

Hynek Muhlbacher wrote:

N.B. I think that I have done two posts so far breaking all rules not to advertise business here... Sorry, perhaps somebody ban me from here please?

No no ... not yet ... first tell us when there are workshops planned in Europe (Belgium, Netherlands, ... )?

Marije_Brummel · 2007-01-31

Be patient Eric, we are working on it.

We hope March/April

Howdy, Stranger!

Quick Links

Categories

Webcast on SQL Optimization for Ms Dynamics NAV

Answers