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.
my 2 cents
0
Answers
Web cast?
What web cast?
Where did you find out about this?
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.
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.
MVP - Dynamics NAV
My BLOG
NAVERTICA a.s.
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)
RIS Plus, LLC
MVP - Business Apps
MVP - Dynamics NAV
My BLOG
NAVERTICA a.s.
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.
RIS Plus, LLC
MVP - Business Apps
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.
MVP - Dynamics NAV
My BLOG
NAVERTICA a.s.
It would be nice if MVPs got this sort of information, and maybe even notification BEFORE the event.
](*,) ](*,) ](*,) ](*,) ](*,) ](*,) ](*,) ](*,) ](*,)
MVP - Dynamics NAV
My BLOG
NAVERTICA a.s.
RIS Plus, LLC
MVP - Business Apps
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!
No one loves you like the one who created you...
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.
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.
SQL Perform Ltd
NAV on SQL Optimization - Tools, Education, Services
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.
And Hynek, thanks for the webcast. There were 4 of us from our company listening and refreshing the knowledge.
MVP - Dynamics NAV
My BLOG
NAVERTICA a.s.
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
Eric Wauters
MVP - Microsoft Dynamics NAV
My blog
https://mbs.microsoft.com/partnersource ... veMeetings
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?
So if the year and months are disabled, will navision sum the actual table or will it sum up the bucket for the day?
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.
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.
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.
: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.
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?
SQL Perform Ltd
NAV on SQL Optimization - Tools, Education, Services
No no ... not yet ... first tell us when there are workshops planned in Europe (Belgium, Netherlands, ... )?
Eric Wauters
MVP - Microsoft Dynamics NAV
My blog
We hope March/April