Tag Archives: Performance

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.

Tip #42 | SetCurrentKey on Flowfields

I’ve already posted a few times about the possibilty of doing SETCURRENTKEY on non key (indexed) fields and doing flowfields and CALCSUMS on non SumIndexFields.

Another option that we have is to do a SETCURRENTKEY on a flowfield.

For example:

2015-01-31_21-07-28

This will give the following result:

2015-01-31_21-09-31

And we can check that this is correct:

2015-01-31_21-09-09

Be careful. Mind performance.

Enjoy.

NAV 2013 R2 | Keys, Keygroups and SetCurrentKey

With NAV 2013, the Classic Client and the Native Navision database were discontinued.

One of the possibilities that arised was improving the SQL story in NAV.

This was done first by implementing SETAUTOCALCFIELDS and the possibility to do CALCSUMS and FlowFields without specifying a SumIndexField. This is released in NAV2013.

In NAV2013 R2 the story continues

Keys

In the Native database the structure how Navision maintained Keys was perceived as revolutionairy and a main part in the success of the Application and its architecture.

Within a Key one could specify SumIndexFields. These are decimals that then would be totalled in the background without writing code.

Everything in NAV like G/L, Inventory and Jobs is based on that principle. You will not find any Application code in NAV that totals entries.

To be able to sort something a Key was required in the Native database. A maximum of 40 keys were allowed. This is different in SQL Server.

SQL Server can sort without having an Index (the name for Key in SQL Server). Therefore with the Native database being discontinued the possibility opened to change the Key story in NAV.

Therefore with the introduction of NAVA 2013 R2 it is no longer required to define a Key on a table to sort data.

It is now possible to sort on any column in the UI.

And it is also possible to sort on any field from AL code.

SetCurrentKey no longer requires a Key to be created in the table.

KeyGroups

Another change that has been imlemented is the discontunation of KeyGroups.

After internal debate and investigation Microsoft decided that the active use of the feature was not enough to justify the effort of moving the feature from Classic Client to PowerShell.

Do you agree with this?

NAV 2013 | No more Indexhinting

By Marije Brummel, founder of NAV-Skills

UPDATE: I’ve heard this was fixed in 2016. Dit not test this myself.

Well, that sounds cool right? Like, never index hinting in NAV anymore after all the discussions in the past.

http://mibuso.com/forum/viewtopic.php?f=34&t=13154&hilit=hinting

http://www.mibuso.com/dlinfo.asp?FileID=896

With NAV 2013 it is no longer possible to setup index hints using the $ndo$dbconfig table.

Does it mean it’s no longer nescesairy.

Unfortunately not.

I found out about this during an upgrade of one of my customers from NAV2009 RTC to NAV2013.

We experienced extremely slow performance in an area where NAV2009 was much faster than NAV2013. And here I promised better performance… DAMN. Stick out tongue

Off course with our perfect documentation (not) it took some time to figure out it might be that index hinting was used in NAV2009 to force SQL using an index it would not use normally.

The process is selecting shipments to be planned in a trip for a taxicompany. We use a common method to select a shipment using the user id, just like you would when applying customer and vendor ledger entries. When selected it’s an extremely selective field (only one or two record of a million have this value) but SQL does not pick it up because… because… shoot me, I don’t know why not. DAMN 2. Stick out tongue

So with index hinting no longer possible (confirmed by Microsoft, done on purpose, by redesign) I needed to be creative.

The solution is to save a pointer to a shipment in a second table called “Selected Shipment”. All shipment that need to be planned in a trip are temporarily stored there and picked up and deleted afterwards. Depending on the architecture of your transaction this could even be done in a temporary table and/or a single instance codeunit.

So recap: no more indexhinting, be carefull with upgrades and creative with solutions.

And PS: The general performance of NAV 2013 is AWESOME!!!

NAV 2013 | SETAUTOCALCFIELDS

NAV 2013 | SETAUTOCALCFIELDS

In NAV 2013 we have a new command SETAUTOCALCFIELDS.

What does this command do.

It is used in C/AL like this:

Cust.SETAUTOCALCFIELDS(Balance)

IF Cust.FINDSET THEN REPEAT

Some Code

UNTIL Cust.NEXT = 0;

This leads to this query in SQL Server:

SELECT

ISNULL(“Customer”.”timestamp”,@0) AS ISNULL(“Customer”.”No_”,@1) AS “No_”,…,

ISNULL(“SUB$Balance”.”Balance$Detailed Cust_ Ledg_ Entry$SUM$Amount”,@77) AS “Balance”

FROM “CRONUS Nederland BV$Customer” AS “Customer” WITH(READUNCOMMITTED)

OUTER APPLY (SELECT TOP (1) ISNULL(SUM(“Balance$Detailed Cust_ Ledg_ Entry”.”Amount”),@76) AS “Balance$Detailed Cust_ Ledg_ Entry$SUM$Amount”

FROM “CRONUS Nederland BV$Detailed Cust_ Ledg_ Entry” AS “Balance$Detailed Cust_ Ledg_ Entry” WITH(READUNCOMMITTED)

WHERE (“Balance$Detailed Cust_ Ledg_ Entry”.”Customer No_”=”Customer”.”No_”)) AS “SUB$Balance”

Hence, a single SQL Statement that joins two tables.

This dramatically changes the teaching that has been done about T-SQL generation in NAV from the last few years.

Also, notice that the SIFT view is not used.

BE AWARE

Like a filter,  SETAUTOCALCFIELDS keeps being active on a variable until it is reset.

To reset this command Cust.SETAUTOCALCFIELDS should be used. So without any fields, just like resetting a filter with SETRANGE.

Thanks to Luc van Vugt to bring this to my attention.

Table vs. Query | Performance Battle

Table vs. Query | Performance Battle

NAV2013 introduced the Query object. Frankly I think that this new object type deserves more attention from bloggers. We have been nagging Microsoft for years to deliver this and now it is there and what happens? Quietness.

I have at least two blogs around Queries. This one is the first. It is an important one because I want to blowup a myth even before it get’s the chance of becoming one.

Queries have two advantages when it comes to reading performance that tables don’t have. First you can limit the amount of data you retrieve from the server by defining only the specific columns you need and second you can join multiple tables into one query saving noumerous roundtrips to the server with our good old loopy-loopy code pattern.

But Queries can’t do what Tables can, they cannot write to the database.

And there is more that Queries cannot do. Let me explain with an example.

In my example I’ve created a realy simple query that contains a few fields from the Item Ledger Entry table

I use this Query in a Codeunit along with the Table variable itsself.

This is a very stupid piece of code that reads the same Item Ledger Entry four times. Twice from the table and twice with the query.

And yes, we get four messageboxes with the Document No.

So what is the clue?

Let’s look at what NAV Sends to SQL Server with Profiler…

Please note that NAV only issues three SQL Statements, not four. And I can tell you a Little secret, if you run this again, it will only issue two.

The two statements that are the same are the Query statements. True, they are more lightweight than the Table Query but unless you have a covering index it will read the entire record in SQL anyway.

Caching

Another thing that NAV2013 introduces is Service Tier caching. All the users on the same service tier share the same reading cache. So if I read customer 10000 and another user wants this data it is not read from SQL Server but from cache. NAV Cache.

Unfortunately this only works for Tables, not for Queries.

So remember this when you design your solution. If you read the same data over and over again, a Query might be overkill.

If you want my opinion you should only use queries when joining multiple tables. Never ever use queries in a single table scenario. The only thing you do is adding an extra layer of complexity to your solution and add extra objectcosts for your customer.

Thanks Waldo for the tip!