Performance issues in NAV2017 CU11

ACaignie

Hello,

We recently migrated from NAV2009R2 to NAV2017CU11, and we have some long running SQL queries. Almost all of these queries have the same 'signature' in SQL, and start with 'SELECT TOP 50 ISNULL( ...), like this example from the vendor:

SELECT TOP (50) ISNULL("Vendor"."timestamp",@0) AS "timestamp",ISNULL("Vendor"."No_",@1) AS "No_",ISNULL("Vendor"."Name",@2) AS "Name",ISNULL("Vendor"."Search Name",@1) AS "Search Name", ... WHERE (ISNULL("Vendor"."Search Name",@1)=@11 AND ISNULL("Vendor"."No_",@1)<@12 OR ISNULL("Vendor"."Search Name",@1)<@11) ORDER BY "Search Name" DESC,"No_" DESC OPTION(OPTIMIZE FOR UNKNOWN, FAST 50)

Some of these long running queries (on other tables than vendor) take more than 50 seconds to run , on a table that only contains about 2.000.000 records.
After investigation it seems that these queries come from pages that are in the background, and have filters on them. If these pages are in the foreground then there is no performance issue, ony if they are minimized and another process is done by the user.

Is there any way to control this kind of behaviour? The 'RefreshOnActivate' property doesn't make any difference. I already disabled the sorting on the page to avoid sorting on flowfields, but this doesn't help.

Any ideas please?

Kind regards,
Andy

Slawek_Guzek

ACaignie wrote: »

I think that the background refresh does a table scan instead of using one the correct key

It is quite possible.

The trouble with NAV queries is that they always pull all the columns, so the SQL Server always has to do a key lookup operation to the clustered index to pull the data when it is using an index for filtering. The Key Lookup is considered in SQL Server to ba a very 'expensive' operation, so the query optimizer often decides to do a full table scan rather than the index seek + key lookup.

If you say that the filters narrow your subset to about 10k out of 2m this consists about 5% of your table. This is very likely above the tipping point where SQL Server decides to go for the full table scan.

If your query has ISNULL in the WHERE clause, like in your example, it makes things much worse as SQL cannot use seek on the index, the best possible use of an index un such queries is to make a full scan on it - which makes it more likely to use a full table scan.

First, try to limit the number of records user are working with. Add more filtering if you can.

Secondly, you can try to cluster your data along different index, the one which is supposed to be always used to filter the data users are working with. If you don't have an index which is always used you can pick up the one which is used in this particular scenario - but you need to test scenarios which don't use it to see the impact.

In older NAV versions there was an option to force NAV to rewrite the query by adding an index hint to it, forcing the SQL Server to use a particular index. If you created directly in SQL a table with a special name, $ndo$dbconfig, you could store in there rules for hinting specific queries. It was a feature of NAV pre 2013, but some time ago long NAV & mibuso user, @bbrown, found that it works again in NAV 2016

This was the last resort option, hard to configure and use, but sometimes was a life saver in scenarios like yours. You may look at this document about index hinting in NAV, or try to google around how the $ndo$dbconfig has been used, maybe you can make work it for you.

guidorobben

indexes might help. But not always. For some reason, flowfields can be slow. When that happens, I rebuild them to queries.

ACaignie

I have already investigated this, and there is only one flowfield in the query. This flowfield goes to the related table with the primary key (a simple LOOKUP flowfield). So I hope this is not the issue :-(

Slawek_Guzek

This particular query picks up the TOP 50 records orderd by "Search Name". If you don't have an index on the table on that particular column the SQL Server must sort all the records in the table (2m in this case) by the "Search Name" column and return just the top 50.

If you don't have an index starting from "Search Name" column don't be surprised the query takes long time.

If you do have an index starting from the "Search Name" field it still might take a long time as the index migh got heavilly fragmented during the upgrade. This is happening because in NAV 2013 upwards the text fields are stored internally as nvarchar type, which takes 2 times more the space as varchar used in pre 2013 versions, and during conversion NAV rewrites all the tables, ruining the indexes.

The wise thing would be to run some index optimisation routine to bring the internal indexes structure to some order.

ACaignie

Hi Slawek,
Thanks for the answer. An index optimisation is done every night, and was also done after the upgrade.
The issue is not the sorting I think. The recordset on the page is filtered, and these filters return a set of records that is only a fraction of the 2m records. If the user puts additional filters the page remains fast. However if he opens another page and does a modification to the same table, then it takes very long to refresh the page that is in the background.
So I would like to know if this background refreshing can be disabled.

Andy

Slawek_Guzek

I don't think that there are any methods or properties which would let you disable the background refresh. At least I am not aware of any.

By the way - is your lookup field the "Search Name" field in the Vendor table, and are you filtering on that flowfield?

Juhl

Hi Andy
I came across something similar, maybe you can use this. But it seems MS does some crazy calculations in pages something.

https://juhl.blog/2017/09/22/slow-customer-card-in-nav-2017/

ACaignie

Hi Slawek,
I'm sorry for the confusion but the query in my question was just an example of a 'SELECT TOP (50) ISNULL' query that takes a very long time, the actual query is on a customized table (without filters or sorting on flowfields). The user works on a small subset of that table (about 10.000 records out of 2M), the 1.900.000 other are filtered out by a key. I think that the background refresh does a table scan instead of using one the correct key. I now asked the user to close the page instead of minimizing it

ACaignie

Hi Juhl,
I have already removed AgedAccReceivableChart and AgedAccReceivableChart2, and commented out the calculation of BalanceExhausted and DaysPastDueDate. We have customers who has over 10.000 invoices, and it took NAV more than 2 minutes to open the page
I asume everything works fine on their Cronus database, but on larger database ...

Slawek_Guzek

ACaignie wrote: »

I think that the background refresh does a table scan instead of using one the correct key

It is quite possible.

The trouble with NAV queries is that they always pull all the columns, so the SQL Server always has to do a key lookup operation to the clustered index to pull the data when it is using an index for filtering. The Key Lookup is considered in SQL Server to ba a very 'expensive' operation, so the query optimizer often decides to do a full table scan rather than the index seek + key lookup.

If you say that the filters narrow your subset to about 10k out of 2m this consists about 5% of your table. This is very likely above the tipping point where SQL Server decides to go for the full table scan.

If your query has ISNULL in the WHERE clause, like in your example, it makes things much worse as SQL cannot use seek on the index, the best possible use of an index un such queries is to make a full scan on it - which makes it more likely to use a full table scan.

First, try to limit the number of records user are working with. Add more filtering if you can.

Secondly, you can try to cluster your data along different index, the one which is supposed to be always used to filter the data users are working with. If you don't have an index which is always used you can pick up the one which is used in this particular scenario - but you need to test scenarios which don't use it to see the impact.

In older NAV versions there was an option to force NAV to rewrite the query by adding an index hint to it, forcing the SQL Server to use a particular index. If you created directly in SQL a table with a special name, $ndo$dbconfig, you could store in there rules for hinting specific queries. It was a feature of NAV pre 2013, but some time ago long NAV & mibuso user, @bbrown, found that it works again in NAV 2016

This was the last resort option, hard to configure and use, but sometimes was a life saver in scenarios like yours. You may look at this document about index hinting in NAV, or try to google around how the $ndo$dbconfig has been used, maybe you can make work it for you.

ACaignie

Hi Slawek,
Indexhinting is a good idea, however this was kind of the last option I wanted to explore.
Thx for the answer
Andy