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

Business Central/NAV Developers Forum
1

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

2

Hi Edward,
I did not see any specific reasons why this should be any slower in NAV 2017 compared to NAV 2015.
If you are running it on the same (similar or better) hardware, SQL version etc., then it should also perform the same.
But also consider that SQL sometimes seems a bit slow to get started. If this is a function commonly used in their old setup, then most of the day may already exist in SQL memory.

3

Hi Edward,
It’s a couple of months ago, but did you ever find out what was causing it?

4

Hi Erik,

Apologies for not replying sooner. I did not at the time and as I changed employer shortly after I posted this, I do not know how this was resolved subsequently (If it was).

Many thanks