NAV TechDays 2017: C/AL – Coding for Performance

Abstract:

There are several misunderstandings, confusions or “fake-news” around, when it is about C/AL programming and how it affects performance on SQL Server. Time to clean this up!

In this session we will explain some “DOs & DONTs” of programming NAV; showing best-practices in using certain methods and how to avoid common mistakes.

Further we will give some guidance about how to identify and troubleshoot query and code bottlenecks. Make your C/AL code rock!

Presented by Jasminka Thunes and Jörg Stryk

Presentation Slides & Scripts: NAV TechDays 2017.zip

Download Session Recording from mibuso.com: https://mibuso.com/downloads/nav-techdays-2017-c-al-coding-for-performance

Watch Session on YouTube:

Author: Jörg Stryk

I’m working with MS Dynamics NAV (formerly Navision) since 1997, with SQL Server since 2003. I have a 100% focus on “Performance Optimization & Troubleshooting” (and things in touch with this).
Since 2006 I’m supporting Dynamics customers and partners within their NAV projects as a freelancer; so far I have supported more than 500 projects in 25 countries on 5 continents – and counting!
I’m developing & distributing the optimization utility the “SSI Performance Toolbox” and I am the author of “The NAV/SQL Performance Field Guide” (ISBN 978-3-8370-1442-6).
Microsoft “Most Valuable Professional” (MVP) 2007-2017.

3 thoughts on “NAV TechDays 2017: C/AL – Coding for Performance”

  1. Hi Jorg,

    Is there a recording of this session?

    There are some discussion points in the powerpoint, though without explanation it’s tricky to understand the issue. e.g. GUIDs.

    Regards,
    Jake

  2. Hi Jake,
    all NAV TechDays sessions were recorded and should be available on http://www.mibuso.com for download, soon; you might also want to check out their YouTube channel to watch the recordings.

    As for using GUID, there are mixed opinions around and actually I do not fully agree here with Jasminka (who does not recommend using GUID). IMHO it depends on what you want to optimize …
    GUID in NAV are kind of random-figures, thus not sequentially. If e.g. you use a sequential “Entry No.” for PK and Clustered Index (CI), all Pages could be filled up to completely (= Index FillFactor (FF) 100%) as you always add new records to the end.
    So this kind of clustering keeps the index size somewhat smaller; “Entry No.” is a 4 bytes integer, the CI is used for Bookmark in all other indexes, thus all index objects are smaller, too.
    Problem: by always adding records to the end of a table, you’ll end up in blocks if you have heavy parallel INSERT operations (usually worsened by a stupid numbering algorithm); hence, the end of the table gets stuck.
    With using GUID for PK and CI the INSERT actually happens at “random” positions in the table, thus not always writing to the last Page of the table, but inserting “in between”, hence, always writing to different Pages.
    This could remarkably reduce blocking conflicts in case of heavy parallel INSERT operations!!!
    Theoretical disadvantages: GUID is 16 bytes, thus the index is larger and also all other indexes get bigger. The FF could not be 100% anymore, as now you’ll need some free space on the Pages for quicker INSERT (else you’ll get lots of Page Splits, fragmenting the indexes, potentially degrading Read performance). Well, the FF could be adjusted with an Index Rebuild – if doing this via Maintenance Plan, I suggest to have a “Free Space” of ca. 2% (means FillFactor 98%) to reduce Page-Splits. There are also tools around which could adjust the FF more precisely per index 😉
    Hence: Using GUID for PK/CI could be great to improve/unblock INSERT operations, but could be a (slight?) disadvantage for Read operations. So it depends on the purpose/focus of using the table …

    PS: do not use GUID on standard “Ledger Entry” tables, like “G/L Entry”, “Item Ledger Entry” etc.. Here you’ll always get blocks because of a stupid numbering algorithm; GUID will not help.

Leave a Reply

Your email address will not be published. Required fields are marked *