Category Archives: Performance, Keys, Indexes, SQL Server

Everything I post about performance and Dynamics NAV nicely in one Category

NAV 2013 | No more Indexhinting

Editors Note : This blog post was brought forward from my previous blog.

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

http://dynamicsuser.net/blogs/mark_brummel/archive/2007/08/21/is-navision-to-stupid-or-is-sql-to-smart.aspx

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!!!

Advertisements

NAV2013R2 | Maintain SIFT Index

As I already explained in my last blog post the way performance tuning is done in NAV2013R2 has changed from previous versions.

Rather than issueing a SQL Statement for each flowfield in each row on a listpage NAV now uses OUTER APPLY.

In a large upgrade project I am doing with a 100+GB database the Posted Invoices did not perform both from purchase and sales.

It is more work to analyse a query. Each flowfield adds an OUTER APPLY to the query and sometimes this can lead to queries being a page long. It helps to paste the query in SQL server management studio and search for OUTER APPLY and add a line break for each.

In this scenario the Amount and Amount Incl. VAT flowfields were added to the list hence two OUTER APPLY statements were added.

In this case the SIFT on the primairy key was disabled which was best practive in older versions of NAV.

Sift

However by activating the MaintainSIFTIndex property the performance of the page was back to being ultra fast like it should be in NAV2013R2.

 

 

NAV2013R2 | SQL Integer Issues

It’s been a while since I had time to blog, and still I do not really have the luxury of time but there are at least two things important enough to share.

At the moment I am involved in a large upgrade project from NAV 5.0 to 2013R2 with a Multi-country NAV database of 100+ GB.

This entry will be about the issues NAV2013R2 has with using the SQL Integer option for NAV Code fields and the next entry will be about SIFT on primairy key fields.

Both will lead to performance issues if treated with the knowledge of previous versions.

SQL Server performance tuning for Dynamics NAV changes with each release of the product. This is why it is so hard to make easy rules to remember for developers. Most of the times the changes are good and the product performs faster if used correctly, as is the case with this release.

In Dynamics NAV it is possible to change the SQL DataType for a Code field from the default value VarChar to Integer.

This was done a lot by customers moving from the Classic Database to SQL Server for sorting purposes and it was never an issue.

Until this release.

For those of you who attended one of my sessions at Directions last year you might remember that I told that rather than reading flowfields row-by-row in a listpage NAV is now performing an OUTER APPLY returning a complete dataset using SQL Server power.

The issue in this scenario is when you change for example the properties of the No. field in the Customer table to Integer and you do not change it on all referenced tables SQL cannot handle the conversion.

This is a SQL Server issue more than a NAV issue and it will not be solved by Microsoft.

Here is a link to the issue on Mibuso.

http://mibuso.com/forum/viewtopic.php?f=32&t=58700&start=0&hilit=NAV2013+and+performance

The solutions are to either change all the fields to Integer which is close to impossible since you end up in tables like Default Dimensions or to just reverse the change back to the default value.

I did the latter and implemented a SQL script to add filler characters to solve the sorting issues.

Contact me if you want more information about the solution.

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 Mark 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

http://dynamicsuser.net/blogs/mark_brummel/archive/2007/08/21/is-navision-to-stupid-or-is-sql-to-smart.aspx

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!