Webcast on SQL Optimization for Ms Dynamics NAV

NavStudentNavStudent Member Posts: 399
edited 2007-04-06 in SQL Performance
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
«1

Answers

  • David_SingletonDavid_Singleton Member Posts: 5,479
    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?
    David Singleton
  • NavStudentNavStudent Member Posts: 399
    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.
    my 2 cents
  • David_SingletonDavid_Singleton Member Posts: 5,479
    Thanks.
    David Singleton
  • NavStudentNavStudent Member Posts: 399
    you are welcome. Now I guess I have to wait till tomorrow for my answer.
    my 2 cents
  • David_SingletonDavid_Singleton Member Posts: 5,479
    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.
    David Singleton
  • kinekine Member Posts: 12,562
    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")
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • DenSterDenSter Member Posts: 8,304
    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)
  • kinekine Member Posts: 12,562
    The recording URL was not sent to us yet.
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • NavStudentNavStudent Member Posts: 399
    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.
    my 2 cents
  • DenSterDenSter Member Posts: 8,304
    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.
  • NavStudentNavStudent Member Posts: 399
    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.
    my 2 cents
  • kinekine Member Posts: 12,562
    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.
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • David_SingletonDavid_Singleton Member Posts: 5,479
    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.

    ](*,) ](*,) ](*,) ](*,) ](*,) ](*,) ](*,) ](*,) ](*,)
    David Singleton
  • kinekine Member Posts: 12,562
    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... ;-)
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • DenSterDenSter Member Posts: 8,304
    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-SageSaint-Sage Member Posts: 92
    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!

    No one loves you like the one who created you...
  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    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. :mrgreen:
  • David_SingletonDavid_Singleton Member Posts: 5,479
    Well my main interest was to see how it compared to Hynek's presentation, so I guess that question is answered.
    David Singleton
  • Hynek_MuhlbacherHynek_Muhlbacher Member Posts: 2
    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.
    Hynek Muhlbacher
    SQL Perform Ltd

    NAV on SQL Optimization - Tools, Education, Services
  • David_SingletonDavid_Singleton Member Posts: 5,479
    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.
    David Singleton
  • kinekine Member Posts: 12,562
    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.
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • WaldoWaldo Member Posts: 3,412
    David,
    If you want, I will notify you on these events (Did it for Mark as well :wink: ).

    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
  • navisi0nenavisi0ne Member Posts: 36
    recorded webcast is on partnersource:

    https://mbs.microsoft.com/partnersource ... veMeetings
  • thaugthaug Member Posts: 106
    Could this be added as a download here? =P~ [-o<
    There is no data, only bool!
  • NavStudentNavStudent Member Posts: 399
    Hello Hynek Muhlbacher
    Thank you for your answer.

    I have some more question. :D

    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?
    my 2 cents
  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    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.
  • NavStudentNavStudent Member Posts: 399
    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.
    my 2 cents
  • Hynek_MuhlbacherHynek_Muhlbacher Member Posts: 2
    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?
    Hynek Muhlbacher
    SQL Perform Ltd

    NAV on SQL Optimization - Tools, Education, Services
  • WaldoWaldo Member Posts: 3,412
    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, ... )?

    :wink:

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    Be patient Eric, we are working on it. :mrgreen:

    We hope March/April
Sign In or Register to comment.