Query READ speed performance reduced when upgrading from 2015 to 2017.

Edward_Bloomfield

We are currently upgrading a NAV2015 customer to 2017 from 2015.

One of the features we originally added was a page that shows a consolidated view of information relating to a table like the Item table.

As we are pulling this information from four different tables (all bespoke and outside the NAV standard object range) we considered flow fields but were unable to get these to work with the complexity involved so instead we built a NAV Object to generate the data structure and then looped through this to populate a temporary table via code – you cannot build forms on Query objects unfortunately – that would be rebuilt each time someone opened the Page it was based on.

This worked OK. It would take 30 seconds or so for the page to open which wasn’t quick but the client was happy.

Whilst testing this as part of their 2017 upgrade UAT, they reported that this now took 3 minutes and 30 seconds to run, which is far too long.

We got them to bring their upgrade test server’s resources up to the level of the live one and restarted it to eliminate this as a factor but the Page/underlying query would still take 3 minutes and 30 seconds to run.

Taking the routine apart I found that:

• Running the Query directly only took a second or so.
• Remming out the code that populated the temporary table did not speed up the process.

However I found that remming out the query.READ, leaving just the query.OPEN did speed it up.
Opening the query was quick but looping through the READs has slowed right down with NAV2017:

e.g.

IF RrecObjQuery.FINDFIRST THEN
RrecObjQuery.DELETEALL(TRUE);

LqryObject.OPEN;

WHILE LqryObject.READ DO BEGIN

RrecObjQuery.INIT;
RrecObjQuery."User ID" := USERID;
RrecObjQuery."Object No." := LqryObject.No;

Is there a way to speed this up in 2017, or at least restore the performance to 2015 levels?
Many thanks in advance

davmac1

Have you checked this setting?
For a call to any of the FIND functions, 1024 rows are cached. You can set the size of the cache by using the Data Cache Size setting in the Microsoft Dynamics NAV Server configuration file. The default size is 9, which approximates a cache size of 500 MB. If you increase this number by one, then the cache size doubles.
If this is set lower than the default, you could have major speed issues. Higher than 9 will allow for large query sets.