Monthly Archives: July 2015

NAV2015 | Performance Tuning

This post also applies to NAV2013, any version since we moved to the new SQL client and dropped classic client.

I am currently in progress of upgrading two of my oldest customers. One with 10 and one with 15 years of data. Large databases, highly customised and tuned for performance to perfection what can be done in those versions.

One of the things you promise these customers is better performance, since NAV2013 and beyond are the fastest versions of Navision ever.

However, after the initial data upgrade and making all the pages more or less usable this is not just the case. Actually the performance is completely terrible. Rubbish.

Ai ai.

The reasoning behind this is relatively simple. Microsoft has made a couple of changes to the way it talks and works with SQL Server that make old performance tuning tips work less and new tips to be required.

iu

First of all we have the “OUTER APPLY”. This is an actual JOIN between flowfields and tables that happens when running pages. Instead of fetching flowfields row by row Navision now creates one query that joins all the flowfields. This can cause the system to slow down if SQL is not optimised for joins.

Learning to optmise SQL for joins is basic knowlegde for DBA’s. Something I learned from sessions organised by SQL Skills. Its all about creating indexes that have the fields from the where clause. Covering indexes also make huge differences.

Second we have the statistics. It used to be best practice to turn off auto create statistics beacuse Navision used cursors. Well, it does no longer do that. We can now safely turn on the default values and not worry about updating them asynchronously.

Lastly there is paralelism on the SQL Box. This used to be set to 1 for NAV databases. Don’t do that anymore. Because of the “OUTER APPLY” we actually bennefit from multiple CPU treads.

fast

Lessons learned? Well this is not my first NAV2013 implementation/upgrade. I learned all this the hard way while emailing back and forth with my former SQL Perform friends. I actually think I had one of the first 100GB+ NAV2013 databases after an early upgrade.

NAV2013 and newer are the fastest versions ever, but performance tuning is an art. It always was and always will be.

iu7GAQ1O6O

Can you screw it up? Yes off course. As a user could Navigate to an Item Ledger Entry in classic client, remove the filter and place a new one and blow up the system, in NAV2013 they can accidentaly sort on an unindexed column. In the last scenario SQL Server will generate and use statistics, but statistics cannot replace indexes.

Enjoy this summer read.

Dynamics NAV 2016 “Corfu” Beta available for partners

Ok, this is my first mobile blog post.

As a response to a question on my previous post I wanted to share the link where the public beta can be downloaded.

http://t.co/2brBIcViBZ

Let me know if it works. Enjoy the great release! Please have a close look at the code editor and play around with the code unit types.

Dynamics NAV “Corfu” will be named: NAV 2016

CJ0hTZdUYAAZUGtAt WPC 2015 in Orlando Florida, Microsoft revealed the official product name of the next version of Microsoft Dynamics NAV, currently codenamed Corfu.

The name will be Dynamics NAV 2016.

Personally I am very enthousiastic about the release since it shows the NAV teams has their ducks in a row. We get a lot of new application features, development enhancements and a phone client.

If you look at the agenda for NAV Directions USA and EMEA you can get a glance of what is comming.

11701082_10207440171061436_1988002162048490151_n

Enjoy your time at WPC! (Thank Per Mogensen from Mergetool for the Picture).Reg-2015-landing-page-marquee1

Tip #50 – Implementing Addresses

Many, many years ago, in 2009 I’ve written a blog about how to implement No. Series in Microsoft Dynamics NAV. This is one of the oldest features in the product, first introduced in Navision Financials 1.1. When I acquired the product as an end user this was presented as a new feature.

Implementing features like this in your add-on products will make your software recognisable as NAV software and make it easier to use the product for customers.

Another example of a typical way of doing things in NAV is using addresses and formatting them to be printed. This is maybe older than Number Series.

So, since we are doing tip number fifty since I started the first one in 2009 lets focus on that feature and how to implement an address in NAV in such a way that people who are familiar with NAV will recognise it as NAV.

This does not mean that how we work with addresses in NAV cannot be improved, but this is the typical way to implement it in the product.

Description

This creates a standard address format, listing all necessary fields, attributes and methods to generate a basic version of the data entity.

It is being used for master data of the category Business and Document entities.
Examples of usages are:

Business Master Data:

  • Table 18: Customer
  • Table 23: Vendor
  • Table 270: Bank Account
  • Table 5050: Contact

Documents:

  • Table 36: Sales Header
  • Table 38: Purchase Header
  • Table 5900: Service Header
  • Table 5740: Transfer Header

When implemented, addresses have a standard set of fields
Address – Text 50
Address 2 – Text 50
City – Text 50 (Tablerelation: Post Code)
Contact – Text 50
Country/Region Code – Code 10 (Tablerelation: Country/Region)
Post Code – Code 20 (Tablerelation: Post Code)
County – Text 30

The Post Code field and City Code field have the following C/AL Code.

Post Code
PostCode.ValidatePostCode(City,"Post Code",County,"Country/Region Code",(CurrFieldNo <> 0) AND GUIALLOWED);
City
PostCode.ValidateCity(City,"Post Code",County,"Country/Region Code",(CurrFieldNo <> 0) AND GUIALLOWED);

PostCode is a global variable of type record, table Post Code.

Naming & Conventions

Master Data :

  • Standard Field Names

Documents :

  • If multiple addresses exist then : How Used + Field Names
    Example: Bill-to Address
  • If only one address exist then : Standard Field Names

Inheritance

When implemented on documents, data is inherited when the reference to the master data is populated.

"Bill-to Address" := Cust.Address;
"Bill-to Address 2" := Cust."Address 2";
"Bill-to City" := Cust.City;
"Bill-to Post Code" := Cust."Post Code";
"Bill-to County" := Cust.County;
"Bill-to Country/Region Code" := Cust."Country/Region Code";

Users can change the values after they are inherited. When the master data is changed, the documents are not updated.

Printing

The standard way of printing addresses on documents is done using an API, Codeunit 365. For each reference of the address this API has a function that takes the record as a parameter and returns an array of type Text, length 80 with 8 dimensions.
The formatting is done based on a set of business rules in the application that is out of the scope of the blog post.

Example

A document table has a reference to the Customer Master Data and inherits the Address Fields

The table contains a reference to the customer table using a field like

Customer No. Code 20

OnValidate
"Address" := Cust.Address;
"Address 2" := Cust."Address 2";
"City" := Cust.City;
"Post Code" := Cust."Post Code";
"County" := Cust.County;
"Country/Region Code" := Cust."Country/Region Code";

When populated the address fields get copied over from the master data. The address fields reference to the customer table.

Address – Text 50
Address 2 – Text 50
City – Text 50
Contact – Text 50
Country/Region Code – Code 10
Post Code – Code 20
County – Text 30

To format the address fields, a function in the API (Codeunit 365) exists

ExampleDocument(VAR AddrArray : ARRAY [8] OF Text[50];VAR ExampleDocument : Record "Example Document Header")
WITH ExampleDocument DO
  FormatAddr(
    AddrArray,"Ex-Name","Ex-Name 2",'',"Ex-Address","Ex-Address 2",
    "Ex-City","Ex-Post Code","Ex-County","Ex-Country/Region Code");

Specific Example

Addresses are used in Sales Documents like shown in this screenshot
Address Format 1Address Format 2The Values are inherited using C/AL Code
Address Format 3The code for post code and city is validated
Address Format 4We can print the address using functions in the API
Address Format 5On a report this code populates the array to be printed on the report.
Address Format 6

Usages

You can see this used in the following tables & pages:

Master Data: Company Information, Location, Customer, Vendor, Bank Account, Resource, Contact, Employee, Union, Responsibility Center, Work Center, Machine Center.

Other Tables: Customer Bank Account, Vendor Bank Account, Ship-to Address, Order Address, Contact Alt. Address, Alternative Address, Service Item
Processes Job

Documents: Sales Header, Purchase Header, Sales Shipment Header, Sales Invoice Header, Sales Cr.Memo Header, Purch. Rcpt. Header, Purch. Inv. Header, Purch. Cr. Memo Hdr., IC Outbox Sales Header, IC Outbox Purchase Header, Handled IC Outbox Sales Header, Handled IC Outbox Purch. Hdr, IC Inbox Sales Header, IC Inbox Purchase Header, Handled IC Inbox Sales Header, Handled IC Inbox Purch. Header, Reminder Header, Issued Reminder Header, Finance Charge Memo Header, Issued Fin. Charge Memo Header, Service Header, Service Shipment Header, Service Invoice Header, Service Cr.Memo Header, Sales Header Archive, Purchase Header Archive, Transfer Header, Transfer Shipment Header, Transfer Receipt Header, Service Contract Header, Filed Service Contract Header, Return Shipment Header, Return Receipt Header

Consequences

Adding an address reference adds 500 characters (Double Byte) to the table. Each table can only have 8000 characters.

Other applications when using interfaces might use different formatting. Using three address fields is very popular.

This might lead to conflicts in Dynamics NAV

On Master Data, when addresses are implemented often the following fields also exist, although they are not part of the address

Phone No. Text 30
Fax No. Text 30
E-Mail Text 80
Home Page Text 90

References

Elements that are typically used in connection with the Address elements could be the “No. Series”, “Master Data” and/or the “Documents”. Master Data are central to almost everything we do, so most elements connect in one way or another to the Master Data.