Performance compare tests – Part 2

another test scenario:

same preconditions like in last post.
1. db: orig. nav 2017 demo db
2. db: to nav 2017 converted nav 2013 demo db

Now developed a recordload test with g/l entries. wanted to know the record load time per entry.

orig. nav 2017 demo db:
(total no. of entries) | (total load time) | (load time per entry)
run 1: 3186 | 282 | 0,088512
run 2: 3186 | 265 | 0,083176
run 3: 3186 | 250 | 0,078468

converted nav 2013 demo db:
(total no. of entries) | (total load time) | (load time per entry)
run 1: 2842 | 171 | 0,060169
run 2: 2842 | 156 | 0,054891
run 3: 2842 | 141 | 0,049613

the test code:
// dt / DateTime
// timePerLoad / Decimal
// noOfEntries / Integer
// totalTime / Decimal
noOfEntries := IterateGLEntries;
totalTime := CURRENTDATETIME – dt;
timePerLoad := totalTime / noOfEntries;
MESSAGE(FORMAT(noOfEntries) + ‘ | ‘ + FORMAT(totalTime) + ‘ | ‘ + FORMAT(ROUND(timePerLoad,0.000001)));

IterateGLEntries() : Integer
glEntry2.GET(glEntry.”Entry No.”);
UNTIL glEntry.NEXT = 0;

also interesting …



Performance compare tests between Nav 2013 and Nav 2017

There were some discussions about the newer Nav versions performance. As for my own experience, i can say Nav 2017/2018 releases are quite slow.

To get a better feeling about that i did the following:

I converted Nav 2013 Cronus db (build 46056) to Nav 2017 (build 13682) by simply run the convert job in the dev. env. nothing more. then created a nav service for the converted nav2013-db.

test scenario: i wanted to test the load time for the item list page. in many huge database there can be 1000s and 1000s of items. it’s a very important page, very often loaded per day by many people. so high peformance would be a good thing. in both databases are the same no. of items: 145. in both pages are the same columns/fields in the same order shown. i have the same nav system, same binaries, but different db. so the situation is quite ok for a valid comparison.

next step: i wrote a simple test cu for loading the item-list page.

[Test] TestItemList()
// ItemList / TestPage / item list
// dt / DateTime

i started the test cu with the simple test function and measured the time:
1. run: 270ms
2. run: 230ms
3. run: 170ms

now copied that test cu to the nav2017 db, ran the same test, measured the time:
1. run: 380ms
2. run: 320ms
3. run: 300ms

WOW!!!!!! simple test, the numbers tell the story!
What’s about other main list pages (customers, vendors, ledger entries and so on)? What about performance of posting processes? What would i measure, if i continue that kind of tests? i think better not measure …

Is that really a progress??? What is the advantage of new features, if the performance lowers and lowers from release to release ???

i think here is a todo for the nav microsoft team.




Tables Changes from Nav 2013 to Nav 2017 – first shot

For those who would like to have release notes when upgrading from nav 2013 to nav 2017 i have developed following table comparison.

New in Nav 2017:

60 Document Sending Profile
61 Electronic Document Format
62 Record Export Buffer
130 Incoming Document
131 Incoming Documents Setup
132 Incoming Document Approver
133 Incoming Document Attachment
134 Posted Docs. With No Inc. Buf.
135 Acc. Sched. KPI Web Srv. Setup
136 Acc. Sched. KPI Web Srv. Line
137 Inc. Doc. Attachment Overview
197 Acc. Sched. KPI Buffer
249 VAT Registration Log
326 Tax Setup
477 Report Inbox
487 Business Chart User Setup
570 G/L Account Category
710 Activity Log
777 Online Bank Acc. Link
806 Geolocation
851 Cortana Intelligence
870 Social Listening Setup
871 Social Listening Search Topic
980 Payment Registration Setup
981 Payment Registration Buffer
983 Document Search Result
1034 Job Planning Line – Calendar
1060 Payment Service Setup
1062 Payment Reporting Argument
1150 Report Totals Buffer
1200 Bank Export/Import Setup
1205 Credit Transfer Register
1206 Credit Transfer Entry
1209 Credit Trans Re-export History
1213 Data Exchange Type
1214 Intermediate Data Import
1220 Data Exch.
1221 Data Exch. Field
1222 Data Exch. Def
1223 Data Exch. Column Def
1224 Data Exch. Mapping
1225 Data Exch. Field Mapping
1227 Data Exch. Line Def
1231 Positive Pay Entry
1232 Positive Pay Entry Detail
1235 XML Buffer
1237 Transformation Rule
1240 Positive Pay Header
1241 Positive Pay Detail
1242 Positive Pay Footer
1248 Ledger Entry Matching Buffer
1249 Bank Stmt Multiple Match Line
1250 Bank Statement Matching Buffer
1251 Text-to-Account Mapping
1252 Bank Pmt. Appl. Rule
1259 Bank Data Conv. Bank
1260 Bank Data Conv. Service Setup
1261 Service Password
1265 Data Exch. Field Mapping Buf.
1270 OCR Service Setup
1271 OCR Service Document Template
1275 Doc. Exch. Service Setup
1280 Bank Clearing Standard
1281 Bank Data Conversion Pmt. Type
1284 Outstanding Bank Transaction
1293 Payment Application Proposal
1294 Applied Payment Entry
1295 Posted Payment Recon. Hdr
1296 Posted Payment Recon. Line
1299 Payment Matching Details
1300 Mini Customer Template
1301 Item Template
1302 Dimensions Template
1303 Mini Vendor Template
1304 Sales Price and Line Disc Buff
1306 User Preference
1307 O365 Device Setup Instructions
1308 O365 Getting Started Page Data
1309 O365 Getting Started
1310 Chart Definition
1311 Last Used Chart
1312 Trial Balance Setup
1313 Activities Cue
1319 Sales by Cust. Grp.Chart Setup
1400 Service Connection
1430 Role Center Notifications
1500 Workflow Buffer
1501 Workflow
1502 Workflow Step
1504 Workflow Step Instance
1505 Workflow – Table Relation
1506 Workflow Table Relation Value
1507 Workflow Step Buffer
1508 Workflow Category
1509 WF Event/Response Combination
1511 Notification Entry
1512 Notification Setup
1513 Notification Schedule
1514 Sent Notification Entry
1515 Dynamic Request Page Entity
1516 Dynamic Request Page Field
1518 My Notifications
1520 Workflow Event
1521 Workflow Response
1522 Workflow Event Queue
1523 Workflow Step Argument
1524 Workflow Rule
1525 Workflow – Record Change
1526 Workflow Record Change Archive
1530 Workflow Step Instance Archive
1540 Workflow User Group
1541 Workflow User Group Member
1550 Restricted Record
1600 Office Add-in Context
1601 Office Add-in Setup
1602 Exchange Object
1606 Office Invoice
1610 Office Add-in
1612 Office Admin. Credentials
1615 Office Job Journal
1620 Office Document Selection
1625 Office Contact Associations
1638 Invoiced Booking Item
1650 Curr. Exch. Rate Update Setup
1660 Payroll Setup
1661 Import G/L Transaction
1662 Payroll Import Buffer
1700 Deferral Template
1701 Deferral Header
1702 Deferral Line
1703 Deferral Post. Buffer
1704 Posted Deferral Header
1705 Posted Deferral Line
1800 Data Migrator Registration
1801 Data Migration Entity
1802 Assisted Company Setup Status
1803 Assisted Setup
1804 Approval Workflow Wizard
1805 Encrypted Key/Value
1806 Data Migration Setup
1807 Assisted Setup Log
1810 Assisted Setup Icons
1900 Cancelled Document
2000 Time Series Buffer
2001 Time Series Forecast
2002 Azure Machine Learning Usage
2100 Sales Document Icon
2103 O365 Sales Document
2105 O365 Payment History Buffer
2110 O365 Sales Initial Setup
2118 O365 Email Setup
5127 Deferral Header Archive
5128 Deferral Line Archive
5324 Exchange Service Setup
5329 CRM Redirect
5330 CRM Connection Setup
5331 CRM Integration Record
5332 Coupling Record Buffer
5333 Coupling Field Buffer
5334 CRM Option Mapping
5335 Integration Table Mapping
5336 Integration Field Mapping
5337 Temp Integration Field Mapping
5338 Integration Synch. Job
5339 Integration Synch. Job Errors
5340 CRM Systemuser
5341 CRM Account
5342 CRM Contact
5343 CRM Opportunity
5344 CRM Post
5345 CRM Transactioncurrency
5346 CRM Pricelevel
5347 CRM Productpricelevel
5348 CRM Product
5349 CRM Incident
5350 CRM Incidentresolution
5351 CRM Quote
5352 CRM Quotedetail
5353 CRM Salesorder
5354 CRM Salesorderdetail
5355 CRM Invoice
5356 CRM Invoicedetail
5357 CRM Contract
5359 CRM Team
5360 CRM Customeraddress
5361 CRM Uom
5362 CRM Uomschedule
5363 CRM Organization
5364 CRM Businessunit
5365 CRM Discount
5366 CRM Discounttype
5367 CRM Account Statistics
5368 CRM NAV Connection
5370 CRM Synch. Job Status Cue
6300 Azure AD App Setup
6301 Power BI Report Configuration
6302 Power BI Report Buffer
6303 Azure AD Mgt. Setup
6700 Exchange Sync
6701 Exchange Contact
6702 Booking Sync
6703 Booking Service
6704 Booking Mailbox
6705 Booking Staff
6706 Booking Service Mapping
6707 Booking Item
6710 Tenant Web Service OData
6711 Tenant Web Service Columns
6712 Tenant Web Service Filter
6721 Booking Mgr. Setup
7500 Item Attribute
7501 Item Attribute Value
7502 Item Attribute Translation
7503 Item Attr. Value Translation
7504 Item Attribute Value Selection
7505 Item Attribute Value Mapping
7506 Filter Item Attributes Buffer
8400 Record Set Definition
8401 Record Set Tree
8402 Record Set Buffer
8628 Config. Field Mapping
8630 Config. Media Buffer
8631 Config. Table Processing Rule
8632 Config. Record For Processing
8650 DataExch-RapidStart Buffer
9000 User Group
9001 User Group Member
9002 User Group Access Control
9003 User Group Permission Set
9004 Plan
9005 User Plan
9006 Plan Permission Set
9007 User Group Plan
9008 User Login
9042 Team Member Cue
9062 User Security Status
9063 Relationship Mgmt. Cue
9069 O365 Sales Cue
9070 Accounting Services Cue
9153 My Account
9154 My Job
9155 My Time Sheets
9170 Profile Resource Import/Export
9178 Application Area Setup
9179 Application Area Buffer
9190 Terms And Conditions
9191 Terms And Conditions State
9192 Pending Company Rename
9400 Media Repository
9500 Email Item
9600 XML Schema
9610 XML Schema Element
9611 XML Schema Restriction
9612 Referenced XML Schema
9650 Custom Report Layout
9651 Report Layout Selection
9656 Report Layout Update Log
9657 Custom Report Selection
9701 Cue Setup
9800 Table Permission Buffer
9900 Web Service Aggregate
130400 CAL Test Suite
130401 CAL Test Line
130402 CAL Test Codeunit
130403 CAL Test Enabled Codeunit
130404 CAL Test Method
130405 CAL Test Result
130406 CAL Test Coverage Map
2000000114 Document Service
2000000150 NAV App Object Metadata
2000000151 NAV App Tenant App
2000000152 NAV App Data Archive
2000000153 NAV App Installed App
2000000160 NAV App
2000000161 NAV App Dependencies
2000000162 NAV App Capabilities
2000000163 NAV App Object Prerequisites
2000000165 Tenant Permission Set
2000000166 Tenant Permission
2000000168 Tenant Web Service
2000000169 NAV App Tenant Add-In
2000000170 Configuration Package File
2000000175 Scheduled Task
2000000180 Media Set
2000000181 Media
2000000183 Tenant Media Set
2000000184 Tenant Media
2000000185 Tenant Media Thumbnails
2000000189 Tenant License State
2000000190 Entitlement Set
2000000191 Entitlement
2000000194 Webhook Notification
2000000195 Membership Entitlement
2000000196 Object Options
2000000197 Token Cache
2000000198 Page Documentation
2000000199 Webhook Subscription


Removed from table list:

452 Approval Setup
453 Approval Code
464 Approval Templates
465 Additional Approvers
470 Job Queue
824 DO Payment Connection Details
825 DO Payment Connection Setup
826 DO Payment Setup
827 DO Payment Credit Card
828 DO Payment Credit Card Number
829 DO Payment Trans. Log Entry
830 DO Payment Card Type
5150 Integration Page
2000000203 Database Key Groups



How to: Get field values from dynamically loaded tables

If you need field values from different tables at runtime within the same function, then there is a nice solution:


// variables
tableType, Option : CompInfo,PayTerm
result : ARRAY [5] OF Variant 

LoadDynamicallyFieldValues(tableType::CompInfo, result);
MESSAGE(FORMAT(result[1])+', '+FORMAT(result[2])+', '+FORMAT(result[3]));
LoadDynamicallyFieldValues(tableType::PayTerm, result);
MESSAGE(FORMAT(result[1])+', '+FORMAT(result[2])+', '+FORMAT(result[3]));

LoadDynamicallyFieldValues(tableType : 'CompInfo,PayTerm';VAR result : ARRAY [5] OF Variant)
CASE tableType of
  recID := compInfo.RECORDID;
  recID := payTerm.RECORDID;
  GetFieldValue(recID,'Discount %',result[3]);

GetFieldValue(recID : RecordID;fieldName : Text;VAR fieldValue : Variant)
 fieldRef := recRef.FIELD(field."No.");
 fieldValue := fieldRef.VALUE;



Get next object version number

Every developer has sometimes the issue: What is my next version number for the new object ? If there is a couple of developers working in the some database, you’ll need a kind of a version control.

I’ve developed a page, which calculates the next version number for a defined version prefix. The version sytax is: <PREFIX><Main No.>.<2-digit Sub No.>.

Let’s start with a list of pages with different version list, version ARCH1.00 to ARCH1.06. It does not matter, if the version list of an object contains more than one value.


After running the page set the version prefix, here ARCH, and push action “Get next version”. We get then the new version: ARCH1.07.


You can download the page here.


Export Nav Objects by Code

Exporting locked nav objects can be a problem when importung in target database. it’s not that easy to unlock them in the target database. So for that you can export nav objects by code and check Lock status before exporting.

Create a new report, add dataitem Object. set report to processingonly.


select dataitem Object, set property ReqFilterFields to Type,ID.
set request page: add field Path (Text).


add following code to trigger OnOpenPage:

Path := 'c:\temp';

add following code to report trigger OnPreReport():

finsql := 'C:\Program Files (x86)\Microsoft Dynamics NAV\100\RoleTailored Client\finsql.exe';
  ERROR('finsql not found');

additional add that code to trigger Object – OnAfterGetRecord():

IF Object.Locked THEN BEGIN
  Message(FORMAT(Object.Type)+'-'+FORMAT(Object.ID)+' is locked.');
  // alternatively unlock object, then try again.
  // Object.Locked := FALSE;
  // Object.MODIFY;
end ELSE begin
  arguments := 'command=exportobjects,file=%1,servername=%2,database=%3,filter="Type=%4;ID=%5",ntauthentication=1';
  arguments := STRSUBSTNO(arguments,Path+'\'+FORMAT(Object.Type)+'-'+FORMAT(Object.ID)+
  result := result + FORMAT(Object.Type)+'-'+FORMAT(Object.ID)+'\';

to trigger Object – OnPostDataItem():


Global Variables:

Process DotNet System.Diagnostics.Process.'System, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089'
arguments Text
finsql Text
Path Text
result Text


How to: Get record count for all tables

In newer Nav versions the tables overview in development environment/database information has gone. So how to get that list back ?

Let’s got to SSMS (Sql Server Management Studio).
There select the database, for which you want that information.
Right click on database -> Reports -> Standard Reports -> Disk usage by Top Tables

enter image description here

There we are ! 😉